March 7, 2009 at 7:35 pm
I'm looking to gather some table-level stats on reads. I have been tuning indexes and I would like a metric to show management that the indices are having the desired effect of reducing IO. In DB2, I can quickly gather information about how many reads are hitting each table, but I can't seem to find that information in SQL Server.
I may be missing something.
Using the dynamic management views, I can find out how many reads a query is executing, and I can find out how many seeks and scans an index is being hit with, but I can't find any way to see how many pages are being read either by table, or by index. Either would be useful.
Please tell me I'm missing something obvious in the dynamic management views. I thought about adding seeks + scans from both the index and missing index views and grouping that by table, but that is not the number of pages read: a clustered index scan of 20 pages would count the same as a clustered index scan of 100,000 pages, wouldn't it?
March 9, 2009 at 6:31 am
Interesting question... I'm not sure it'll help, but you can get the actual logical and physical reads by query for the queries that are in cache using sys.dm_exec_query_stats. Generally I haven't measured performance by I/O on tables or indexes, but by I/O (and time & cpu) on the queries in question. If that I/O goes down, then naturally the other does.
However, that's clearly not directly answering your question. I'm not aware of a mechanism to get this per table. Per database through the Performance Monitor is possible and through trace events or querying the DMV mentioned above will work for queries. But I don't know a method for a table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2009 at 7:09 am
Well, I feel a bit better. I spent a lot of time digging and couldn't find anything. I'll post the same question on usenet and see if any of the brains there come up with anything.
I agree, query reads are more useful, but sometimes reads at the table level are a useful high-level metric, too, and I'd like to know if it is possible.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply