SQL Tips by Namwar Rizvi

May 20, 2007

Decide new indexes easily by SQL Server 2005 DMVs

Filed under: DMV,New Features,SQL Server 2005,tips,TSQL,Tuning — namwar @ 8:26 PM

Deciding a proper index to optimize a poorly running query is more art than science. It requires statistics of object usages, their impacts on already running queries etc. SQL Server dynamic management views have a set of DMV which provide excellent and detailed information for object usage. Following query gives you the clear picture of what will happen if you apply the suggested index.

— Potentially Useful Indexes
select d.*
, s.avg_total_user_cost , s.avg_user_impact, s.last_user_seek
,s.unique_compiles

from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d

where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle
order by s.avg_user_impact desc
go

The above query lists the column where an index can be beneficial and its impact on the user. Please note that these statistics will be reset once SQL Server will be restarted. Indexes suggested by the above query will greatly reduce your efforts to decide the best indexes.
But, again as I said above, these are just suggestions actual performance improvements can only be decided by you because no one else knows your requirements better than you:)

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: