Get Index recommendations from SQL Server Database


SELECT

sys.objects.name,

(

avg_total_user_cost * avg_user_impact)*(user_seeks + user_scans)as Impact,

cast

(‘CREATE NONCLUSTERED INDEX ~NewNameHere~ ON ‘+sys.objects.name +‘ ( ‘+ mid.equality_columns +

CASE

WHEN mid.inequality_columns ISNULLTHENELSECASEWHEN mid.equality_columns ISNULLTHENELSE‘,’END+

mid

.inequality_columns END+‘ ) ‘+CASEWHEN mid.included_columns ISNULLTHENELSE‘INCLUDE (‘+ mid.included_columns +‘)’END+

‘;’

asxml)AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns, mid.included_columns FROM

sys

.dm_db_missing_index_group_statsAS migs INNERJOINsys.dm_db_missing_index_groupsAS mig ON migs.group_handle = mig.index_group_handle

INNER

JOINsys.dm_db_missing_index_detailsAS mid ON mig.index_handle = mid.index_handle INNERJOINsys.objectsWITH (nolock)

ON

mid.object_id=sys.objects.object_idWHERE (migs.group_handle IN(SELECTTOP (500) group_handle FROM

sys

.dm_db_missing_index_group_statsWITH (nolock)ORDERBY (avg_total_user_cost * avg_user_impact)*(user_seeks + user_scans)DESC

))

 

ORDER

BY 2 DESCSELECTsys.objects.name,(avg_total_user_cost * avg_user_impact)*(user_seeks + user_scans)as Impact,

cast

(‘CREATE NONCLUSTERED INDEX ~NewNameHere~ ON ‘+sys.objects.name +‘ ( ‘+ mid.equality_columns +CASE

WHEN

mid.inequality_columns ISNULLTHENELSECASEWHEN mid.equality_columns ISNULLTHENELSE‘,’END+ mid.inequality_columns END+‘ ) ‘

+

CASEWHEN mid.included_columns ISNULLTHENELSE‘INCLUDE (‘+ mid.included_columns +‘)’END+‘;’asxml)AS CreateIndexStatement,

mid

.equality_columns, mid.inequality_columns, mid.included_columns FROMsys.dm_db_missing_index_group_statsAS migs INNER

JOIN

sys.dm_db_missing_index_groupsAS mig ON migs.group_handle = mig.index_group_handle INNERJOINsys.dm_db_missing_index_detailsAS mid ON

mig

.index_handle = mid.index_handle INNERJOINsys.objectsWITH (nolock)ON mid.object_id=sys.objects.object_idWHERE

(migs.group_handle IN(SELECTTOP (500) group_handle FROMsys.dm_db_missing_index_group_statsWITH (nolock)ORDERBY

(avg_total_user_cost * avg_user_impact)*(user_seeks + user_scans)DESC))ORDERBY 2

DESC

 

Advertisements

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

%d bloggers like this: