Quick SQL Server Tuning Tip November 1
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:
« « How to access HttpServerUtility.MapPath in a Thread
See how your website looks in over 60 browsers - in minutes » »
If you liked this, then subscribe to my RSS feed
Matt Nov 2
Ya, good tip. Tuning SQL can be a real pain, and this definitely helps.
Roswall Nov 2
I have SQL Server 2005 Express edition - which doesn’t come with Query Analyzer. How can I use this method?
Gath Nov 2
Roswall - You can still use this. Just use Management Studio Express and press the ‘New Query’ button. It will open up a query window that allows you to enter the T-SQL commands.
Spillane Nov 12
Do you have an example of how this helps improve your SQL speed?
Gath Nov 12
I had a slow-running query in WeGotDomain.com. By running the SQL query, checking the stats, slightly altering the query, checking the stats again etc I was able to pinpoint which bit of the query was taking all the time.
The actual problem was that I was using a function in the WHERE part of the query, so I was not taking advantage of any indexes. I replaced the function with SQL that could use the index and now the code is nice’n'zippy.