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.
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.