October 22, 2008 at 3:55 pm
Hi: I’m trying to find a way to know what tables and what columns of those tables the deferent sp's of a DB are using so I can determine what are the best Index options for all of the sp’s.
Any buddy has an Idea?
October 22, 2008 at 7:52 pm
Try using The DMV's to tell you what indexes need to be built (or dropped)
The output of this query can be interpreted according to Microsoft best practices:
Index Advantage above 5k means one should consider and index
Index Advantage above 10k means an index needs to be built
I swiped some of the code from somewhere, but most of it is mine including the 'index advantage' calculation which I swiped from Microsoft's MCITP books when I was taking the exams.
SELECT
user_seeks * avg_total_user_cost * (avg_user_impact *0.01) as 'index advantage'
,mig.index_group_handle
,mid.index_handle ,
CONVERT ( decimal ( 28 , 1 ), migs.avg_total_user_cost * migs.avg_user_impact *
( migs.user_seeks + migs.user_scans )) AS improvement_measure
, 'CREATE INDEX missing_index_' + CONVERT ( varchar , mig.index_group_handle ) + '_'
+ CONVERT ( varchar , mid.index_handle ) + ' ON ' + mid.statement
+ ' (' + ISNULL ( mid.equality_columns , '' )
+ CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL
THEN ','
ELSE ''
END
+ ISNULL ( mid.inequality_columns , '' ) + ')'
+ ISNULL ( ' INCLUDE (' + mid.included_columns + ')' , '' ) AS create_index_statement
, migs.*
, mid.database_id
, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT ( decimal ( 28 , 1 ), migs.avg_total_user_cost * migs.avg_user_impact * ( migs . user_seeks + migs.user_scans )) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans ) DESC
If the code doesn't work, it's a copy/paste error from my website. a bunch of the formatting was goofy so it might have some spaces where it shouldn't.
Craig Outcalt
October 23, 2008 at 5:43 pm
Hi! Thanks for the help!
For what I see this query doest makes the indexes it only suggest it right?
October 24, 2008 at 4:12 am
Yup, and you should test any recommendations out on a dev system to be sure that they really do help. Also check the recommendations to see if there's an existing index that can be modified (by adding columns) to produce what's recommended.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2008 at 4:17 am
fjmorales (10/22/2008)
Hi: I’m trying to find a way to know what tables and what columns of those tables the deferent sp's of a DB are using so I can determine what are the best Index options for all of the sp’s.
In general, the best way to go about this is to run each of the procs in management studio and used the execution plan to see what the query's doing, then add indexes and see if the queries use them. I would suggest that to start with, you pick the 5 or so worst performing procs that you have (use profiler to determine that) and optimise those, then pick another 5, etc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2008 at 4:20 am
gail always provides perfect solution
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 24, 2008 at 7:34 am
Cool, I'll try it... If I find the time!!! I'll let you know when I do it.
Thanks a lot for the help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply