Set STATISTICS IO
SET STATISTICS IO displays the amount of disk activity generated by a query. To enable it, run the following statement:
SET STATISTICS TIME ON
and now can run a query and check the message tab :
SELECT DISTINCT(CustomerID) FROM Sales.SalesOrderHeader
Clean the Buffer Cache
We can remove all the cached pages from the buffer by running :
When testing, its recommended to clean the buffer and run the query. This will give you a true picture of the performance of the query.
Then run the following query:
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 870
It will show an output similar to the following:
Here are the definitions of these items, which all use 8K pages:
Logical reads Number of pages read from the buffer pool.
Physical reads Number of pages read from disk.
Read-ahead reads Read-ahead is a performance optimization mechanism that anticipates the needed data pages and reads them from disk. It can read up to 64 contiguous pages from one data file.
Lob logical reads Number of large object (LOB) pages read from the buffer pool.
Lob physical reads Number of large object (LOB) pages read from disk.
Lob read-ahead reads Number of large object (LOB) pages read from disk using the read-ahead mechanism, as explained earlier.
Now, if you run the same query again, you will no longer get physical and read-ahead reads.
Scan countis defined as the number of seeks or scans started after reaching the leaf level (that is, the bottom level of an index). The only case when scan count will return 0 is when you’re seeking for only one value on a unique index, like in the following example:
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = 51119
If you try the following query, in which SalesOrderID is defined in a nonunique index and can return more than one record, you can see that scan count now returns 1:
SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = 51119
Finally, in the following example, scan count is 4 because SQL Server has to perform four seeks:
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID IN (51119, 43664, 63371, 75119)
Removing Plans from the Plan Cache
You can use a few different commands to remove plans from the plan cache :
DBCC FREEPROCCACHEstatement can be used to remove all the entries from the plan cache. It can also accept a plan handle or a SQL handle to remove only specific plans.
DBCC FREESYSTEMCACHEstatement can be used to remove all the elements from the plan cache or only the elements associated with a Resource Governor pool name.
DBCC FLUSHPROCINDBcan be used to remove all the cached plans for a particular database
DBCC DROPCLEANBUFFERSstatement can be used to remove all the buffers from the buffer pool. You can use this statement in cases where you want to simulate a query starting with a cold cache