Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tip for determining I/O Heavy Queries

By Ryan Cooper, (first published: 2007/11/06)

When it comes to performance tuning, locating the problem index (or lack thereof) is one of the first steps and being able to very quickly narrow the focus of your efforts makes for a much more efficent DBA. Using this technique, you can very easily get quantifiable I/O data for your queries and quickly get an idea of which queries and index need your attention.

This is a combination of 2 techniques posted on SQL Server Central, so I want to give these guys their due.

Last year Michael Morin posted a great technique on determining which indexes are
responsible for most of your I/O. The premise is to store the output from DBCC Showcontig with TableResults into a table and correlate that information with a Profiler Trace monitoring the Scan:Started event. (Number of times accessed * avg. row size) equals the 'Data Flow' for that index.

So, lets say you use Michael's method and you determine that the index PK_Trains is generating your most I/O, but you have several queries
that access that table. How do you know which query is responsible for all this I/O?

Last month Solomon Rutzky post an ingenious idea of creating an After Insert trigger on your Trace Table that
runs DBCC InputBuffer to populate the TextData field for events that don't normally populate TextData (ie. Scan: Started).

So by combining these two techniques you can take Michael's idea a step further and calculate a 'Data Flow' value
for each of your queries.

An example would be to combine the Trace Table created with Michael's technique with your Table of Indexes to determine which
queries are scanning PK_Trains.

SELECT CAST(TextData AS VARCHAR(4000)) AS SQLString, COUNT(*)
FROM TraceTable TT
JOIN MyDatabase..CompileIndexesShowContig CI ON TT.objectid = CI.objectid AND TT.indexid = CI.indexid
WHERE TextData IS NOT NULL
AND DatabaseID = 10
AND EventClass = 51
AND CI.indexname = 'PK_Trains'
GROUP BY CAST(TextData AS VARCHAR(4000))
ORDER BY COUNT(*) DESC

Perhaps PK_Trains is being scanned so heavily because a certain query is missing a more selective index. I have found this extremely helpful in troubleshooting high I/O queries. Enjoy.

Mike's Column: http://www.sqlservercentral.com/columnists/mmorin/2573.asp

Solomon's Column: http://www.sqlservercentral.com/columnists/srutzky/3201.asp

Total article views: 26159 | Views in the last 30 days: 14
 
Related Articles
ARTICLE

A Technique for Determining the I/O Hog in your Database

Performance Tuning can be as much an art as a science when working with SQL Server, but there are ma...

FORUM

Disabling index by using select query

Disabling index by using select query

SCRIPT

Queries, Plans, and Indexes

Find indexes associated with queries and vice versa. Helps figure out what procedures are using ind...

FORUM

defining query to use an existing index

defining query to use an existing index

FORUM

Query about Partition the table & Index

Query about Partition the table & Index

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones