December 21, 2010 at 5:13 am
Is there any way to find out the number of objects(procs) used an index of a table.
Sya I have table t1 and index IX1. Let us assume t1 been using in many procedures, how do we find the procs used the index IX1.
Is there any way to get these info from any dmvs from cached plans or sql tests?
December 21, 2010 at 8:15 am
here is a good article that will help:
http://dbalink.wordpress.com/2008/11/09/how-to-finding-index-usage-stats-with-dmv-in-tsql/
December 21, 2010 at 8:18 am
this will get in the right direction, just change the Database Id and table
use this to get your DB_ID --SELECT DB_ID()
SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,
sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = 6 AND sis.OBJECT_ID = OBJECT_ID('dbo.ORDER_STATUS');
December 21, 2010 at 9:53 am
Here we are getting the seeks, scans and lookups that been occured for the index. However, I am looking for something different like to list the procedure names using the indexes. Please help me out on the same.
December 21, 2010 at 9:57 am
This should get you going in the right direction. Make sure to read the linked articles in this link.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2010 at 10:23 am
I am not so sure there is something out there that will do exactly what you are looking for
This will give the the highest usage SP but then you will need to trace down the plans that are used for each proc....or check your execution plan for a sample of one of them
SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
,OBJECT_NAME(objectid,st.dbid) StoredProcedure
,max(cp.usecounts) execution_count
,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO
,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / (max(cp.usecounts)) avg_total_IO
,sum(qs.total_physical_reads) total_physical_reads
,sum(qs.total_physical_reads) / (max(cp.usecounts) * 1.0) avg_physical_read
,sum(qs.total_logical_reads) total_logical_reads
,sum(qs.total_logical_reads) / (max(cp.usecounts) * 1.0) avg_logical_read
,sum(qs.total_logical_writes) total_logical_writes
,sum(qs.total_logical_writes) / (max(cp.usecounts) * 1.0) avg_logical_writes
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)
order by sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) desc
December 21, 2010 at 10:42 am
456789psw (12/21/2010)
I am not so sure there is something out there that will do exactly what you are looking for
If you read the link I provided and follow the links to a blog post by Michelle Ufford - there is a stored procedure that she created. Check it out - definitely worth the read.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2010 at 9:48 pm
Jason, am not really getting the link by Michelle Ufford.
Could you please post the link related the topic.
December 22, 2010 at 2:34 am
Hi, found something very useful in the below link. Excellent job !!!
http://feodorgeorgiev.com/blog/2010/07/how-to-query-the-cached-plans-xml/
December 22, 2010 at 7:35 am
I was not able to find link as well..
December 22, 2010 at 8:28 am
sqlchanakya (12/21/2010)
Jason, am not really getting the link by Michelle Ufford.Could you please post the link related the topic.
I did not post the link directly to Michelle because there was material in the posts leading up to her post that is pertinent to this question.
First link was By Jason Strate. He referenced a blog by Jonathan Kehayias. Jonathan in turn referenced Michelle Ufford. As I said - please read the trail of links.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 22, 2010 at 8:29 am
456789psw (12/22/2010)
I was not able to find link as well..
I did not post the link directly to Michelle because there was material in the posts leading up to her post that is pertinent to this question.
First link was By Jason Strate. He referenced a blog by Jonathan Kehayias. Jonathan in turn referenced Michelle Ufford. As I said - please read the trail of links.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply