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

Recommended Indexes Expand / Collapse
Author
Message
Posted Wednesday, January 08, 2014 12:15 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 962, Visits: 942
Hi all,

Hopefully someone can shed some light on this issue I'm seeing.

I'm using Glenn Berry's scripts to get recommended indexes from a particular instance/database, and I have implemented the index (so i think). However, the query still returns it as a "missing" index.

Code to find missing indexes:
SELECT	user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,
migs.last_user_seek,
mid.statement AS [Database.Schema.Table],
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.unique_compiles,
migs.user_seeks,
migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;

The index that is recommended:
index_advantage	Database.Schema.Table
147830591.8 [DB1].[dbo].[table1]

equality_columns
[state]

inequality_columns
[completedate]

included_columns
[messageid], [type], [haschildren]

unique_compiles
1

user_seeks
742688



The index that is present has this definition, and it appears to be used (albeit not as much as expected based on the index_advantage) based on the index usage stats, but I can't figure out why it keeps coming up in the results when looking for recommended indexes.

CREATE NONCLUSTERED INDEX [idx_nc_sql_portal_state_completedate] ON [dbo].[table1]
(
[state] ASC,
[completedate] ASC
)
INCLUDE ( [messageid],
[type],
[haschildren]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO


Index usage stats:

Index Name
idx_nc_sql_portal_state_completedate

Total Writes
3887142

Total Reads
40948



Thanks in advance for any help!

Steve
Post #1529041
Posted Friday, January 17, 2014 1:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 235, Visits: 1,179
Here you go.

http://www.sqlskills.com/blogs/paul/missing-index-dmvs-bug-that-could-cost-your-sanity/


Just as with the performance tuning wizard you should take the recommended indexes as a suggestion, not as a rule. You still need to verify that the index is sane, not duplicating something that's already covered, and you should test to verify it will actually help performance and not hurt more.
Post #1532272
Posted Saturday, January 18, 2014 10:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 962, Visits: 942
benjamin.reyes (1/17/2014)
Here you go.

http://www.sqlskills.com/blogs/paul/missing-index-dmvs-bug-that-could-cost-your-sanity/


Just as with the performance tuning wizard you should take the recommended indexes as a suggestion, not as a rule. You still need to verify that the index is sane, not duplicating something that's already covered, and you should test to verify it will actually help performance and not hurt more.


Great link, thanks for sharing! And I agree with Paul's title, it has been costing my sanity in this instance.
Post #1532390
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse