SQL Server – simple query performance analysis


I’m still reading for my next exam stuff for 70-461. During the chapter “Using Tools to Analyze Query Performance” I found a quite simple way of measuring the query performance by using the SET STATISTICS command.

Example:

DBCC DROPCLEANBUFFERS; 
SET STATISTICS TIME ON; 
SET STATISTICS IO ON; 
SELECT * FROM Sales.Customers; 
SELECT * FROM Sales.Orders;

Note that DBCC DROPCLEANBUFFERS clears data from the cache. SQL Server caches data besides query and procedure plans. The TIME switch will display how many miliseconds the query took to execute, while the IO switch will tell how many logical and physical page reads that was involved.

A page in SQL Server is a physical unit on a disk. The size of a page is fixed to 8,192 bytes. A page belongs to a single database object, such as table, index or indexed view. Pages are further grouped into logical groups of eight pages called extents. An extent can be mixed, if pages on this extent belong to multiple objects.

 Output from “SELECT * FROM Sales.Customers” command:

(91 row(s) affected)
Table 'Customers'. Scan count 1, logical reads 5, physical reads 1,   
read-ahead reads 3,   
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:    CPU time = 15 ms,  elapsed time = 125 ms.

Output from “SELECT * FROM Sales.Orders” command:

(830 row(s) affected)
Table 'Orders'. Scan count 1, logical reads 21, physical reads 1,   
read-ahead reads 19,   
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 330 ms.

The meaning of the information returned is as follows (from Traning Kit for 70-461):

  • Scan count The number of index or table scans performed.
  • Logical reads The number of pages read from the data cache. When you read a whole table as in the queries from the example, this number gives you an estimate about table size.
  • Physical reads The number of pages read from the disk. This number is lower than the actual number of pages because many pages are cached.
  • Read-ahead reads The number of pages SQL Server reads ahead.
  • Lob logical reads The number of large object (LOB) pages read from the data cache. LOBs are columns of types VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), TEXT, NTEXT, IMAGE, XML, or large CLR data types,  including the system CLR spatial types GEOMETRY and GEOGRAPHY.
  • Lob physical reads The number of large object-type pages read from disk.
  • Lob read-ahead reads The number of large object pages SQL Server reads ahead.
Advertisements

4 thoughts on “SQL Server – simple query performance analysis

  1. SET STATISTICS TIME ON;
    SET STATISTICS IO ON;

    print ‘customers’;
    DBCC DROPCLEANBUFFERS;
    SELECT * FROM Sales.Customers;

    print ‘orders’;
    DBCC DROPCLEANBUFFERS;
    SELECT * FROM Sales.Orders;

    RESULT:
    customers

    (91 row(s) affected)
    Table ‘Customers’. Scan count 1, logical reads 5, physical reads 1, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 66 ms.

    orders

    (830 row(s) affected)
    Table ‘Orders’. Scan count 1, logical reads 21, physical reads 1, read-ahead reads 19, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 279 ms.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s