Gath Adams

Tuning SQL Server can be a real pain - everyone knows the basics; return the minimum rows, use indexes etc - but sometimes you need to get some hard stats.

Before we begin, I am going to assume you are running in Query Analyzer, or some tool that allows you to change the connection parameters.

Execute:


SET STATISTICS IO ON
SET STATISTICS TIME   ON

This gives you useful info on how many pages were read in your query (both from the cache and from disk)   and how long it takes.

Eg, after running a query, here are the stats:

Table 'DG_UserProperties'. Scan count 0, logical reads 2154, physical reads 10, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘DG_Users’. Scan count 1, logical reads 1083, physical reads 27, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SQL Server Execution Times:
CPU time = 16 ms,   elapsed time = 213 ms.


It is important to reduce the ‘reads’ and the CPU time. Elapsed time can vary depending on server load, so isn’t as important when comparing queries.

After each set of statistics, clear the cache otherwise the next query will use it & give an incorrect comparison.


DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE


This post brought to you by WeGotDomain.com - Over 10,000 Aged domains for sale!

Related posts:

1. Convert MySQL to MS Sql Server