Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

A Technique for Determining the I/O Hog in your Database Expand / Collapse
Author
Message
Posted Wednesday, August 30, 2006 8:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 08, 2008 4:03 PM
Points: 36, Visits: 65

select objectid, indexid, sum(*) as scans
from IndexCapture
group by objectid, indexid

In the above query change sum(*) to count(*).

This is a very nice article. But I agree with the other post that table size should be included into the calculation instead of just averagerecordsize.

AL

Post #305223
Posted Thursday, August 31, 2006 6:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 10,910, Visits: 12,548
I thought it was an excellent article and another tool to troubleshoot issues.  Certainly table size would come into play, but this is just a way to look at space it takes to put a row into cache.  I would hope that on larger tables you would only need ot be caching parts, not whole tables. 



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #305307
Posted Thursday, August 31, 2006 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 07, 2014 6:26 AM
Points: 2, Visits: 204

I think the sum(*) statement in the SQL to create IndexCaptureSummary should probably be count(*). Also, in the report sql statement, the table name should be CompileIndexesShowContig according to the sql used to create the table earlier on...

I thought this was an interesting concept and so I did the study on my database and I have to say that it does appear that the results as stated in the article can be misleading. One of our most heavily used indexes is on our products table which has large records (varchar() text descriptions,etc), but only 250 rows and the index used is clustered.

I'm not sure that the products table is our IO hog, but it showed up as 57% of flow on 17% of scans using this technique. Most of our queries don't return * from products but only select columns and thus (scancount * AverageRecordSize) may not be a good indication of the IO demand created by those scans. It's also frequently used and small enough overall that I'm 99% sure it's held in memory and hurting disk IO.

Since the profiler event Scan:Start just triggers when an index is used (correct?), our products primary key is probably also scanned when using the products table in joins where records aren't even being retreived (necessarily). Not too sure on this point...

Thus, I think the data collection techniques are good, but I wonder if there is a better formulation of the summary report that would factor in the number of rows in the table and/or other data from profiler that would give a more accurate picture of IO demands. Otherwise, great work!

Post #305427
Posted Thursday, August 31, 2006 9:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 21, 2008 11:14 AM
Points: 3, Visits: 25

This article and all the replies were very interesting and serve a purpose. I too have a few questions about some of the concepts used. I actually think this solution is overthinking a common problem.

If I think I/O is a problem, I will run a SQL Profiler Trace and parse it with my favorite parsing tool and sort by Reads or Writes (typically in my systems if I/O is a problem it is reads... there is probably a table scan or index scan someplace most of the time that can be better implemented through better code, better indexing or a better table/normalization structure) and find the worst queries.

As colin has said earlier (and in numerous posts ) if you have a lot of reads you are bringing in a lot of data and possibly flushing out your cache and definitely using (over?) your disks and CPU..

Take your reads multiply by 8 and divide by 1024 twice. This is number of GB you are reading. I have seen a horribly written query perform 23,000,000 reads on a database whose total size is only 5GB!!

So I like looking at the "simple" things first. That normally solves the biggest headaches.

Post #305563
Posted Sunday, September 03, 2006 7:27 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, March 02, 2014 4:34 PM
Points: 724, Visits: 1,001

I just created this and the

 

select cisc.objectname, cisc.indexname, ic.scans, floor(scans*cisc.averagerecordsize) as flow
from IndexCaptureSummary ic
inner join CompileIndexesShowContig cisc on cisc.objectid = ic.objectid and cisc.indexid = ic.indexid
order by flow desc

works but if you have a table with no pk or no index and just say rowid it does not pick that up...thats so on the

two tables i added default of
'' for both...
one would come in as Null and the other as blank..
SET THE INDEXID default '' on CompileIndexesShowContig and INDEXCAPTURE

Also the sum(*) i changed to count(*)
insert into IndexCaptureSummary (ObjectId, IndexID, Scans)
select objectid, indexid, count(*) as scans
from IndexCapture
group by objectid, indexid

Lets say its taking the AverageScans...what if you only select 5 records from a table and another day you select all records...(then would this be a bit misleading) as the i/o is reduced with smaller selects...(Anyone know how to change it so that rows could be incorporated into this )

I like it alot............

Post #305871
Posted Sunday, September 03, 2006 7:30 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, March 02, 2014 4:34 PM
Points: 724, Visits: 1,001

The article certainly gives a slant on object usage, however I'd suggest a sql statement vs reads, writes, cpu, duration is of much greater value

How do you measure this part...aswell..that be useful to

Post #305872
Posted Tuesday, October 17, 2006 2:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 15, 2011 11:21 AM
Points: 1, Visits: 115
My approach was to use the Scans: Stopped event class to capture the scans. It includes the number of pages read in each scan. I use that number multiplied by the number of scans to calculate the data flow.
Post #316049
Posted Thursday, August 30, 2007 7:51 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:57 PM
Points: 461, Visits: 1,265
Awesome article - just have to see if I can truly put it to practical use.
Post #395122
Posted Tuesday, September 04, 2007 5:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 8:14 AM
Points: 1, Visits: 21
Really pedagogical - easy to follow flow of article/method, and very nice with a proposed solution!
Post #396026
Posted Monday, March 30, 2009 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 24, 2009 12:18 PM
Points: 1, Visits: 18
Can someone please tell me how the raw data from the (SQL Profiler) gets into the table IndexCapture?

Thank you,
Anil.
Post #686269
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse