Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Missing index details Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 12:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 30, 2014 1:19 PM
Points: 208, Visits: 1,027
SELECT

migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
mid.statement as fullyqualifiednames,

'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'

+ ' 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


ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC





this query gives me around 490 indexes, i think i cant create blindly all the indexes, please advice some parameter on what basis i can choose most required indexes only.

and there must be some indexes already created, i dont want to create duplicate one. any idea how can filter already created indexes in this script.

Post #1441406
Posted Thursday, April 11, 2013 12:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:04 PM
Points: 23,302, Visits: 32,057
Biggest piece of advice, don't do this. You really need to look at each index and decide if it needs to be created or not. The missing index DMV can also provide you with multiple indexes that are virtually the same, but with some investigation can be combined or possibly eliminated by a slight modification to an existing index.

Do not blindly add indexes just because the DMV or DTA say you need them. Really check them out and test each possible change.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441410
Posted Thursday, April 11, 2013 12:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 30, 2014 1:19 PM
Points: 208, Visits: 1,027
but we can not test as its production environment, there's a long process to create or drop any index on server. so by having an idea only i have to suggest about which index need to create in the server. so please just give me slight idea about an important parameter in this DMV.
Post #1441415
Posted Thursday, April 11, 2013 12:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:04 PM
Points: 23,302, Visits: 32,057
I would not automate this. Each index suggested needs to looked at with a critical eye, not just at the individual index but at al the other indexes suggested on the same table and already on the table.

I have gone through this process myself, and yes it is time consuming but in the end you will have a better set of recommendations to provide regarding what indexes should exist on each table.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441416
Posted Thursday, April 11, 2013 12:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 30, 2014 1:19 PM
Points: 208, Visits: 1,027
ok then please give me an idea regarding what to see when keeping an critical eye on each index, what is the best part to look into it.
Post #1441421
Posted Thursday, April 11, 2013 12:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:04 PM
Points: 23,302, Visits: 32,057
I would start by putting the indexes into a spread sheet grouped by tables. Start by comparing the order of the equality columns (your WHERE or ON clauses would be using Column = value for these), then look at the inequality columns (column >= value or such in a WHERE clause), then look at the included columns.

I have had the missing index DMV and DTA suggest adding an index with 40+ included columns from a table with around 50 columns.

This is just a quick jump start. Even after you narrow down the indexes, you really need to test them in a test or development environment before putting them in production.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1441428
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse