The Ultimate Missing Index Finder

Question

The Ultimate Missing Index Finder

YeshuaAgapao

Hall of Fame

Points: 3495
More actions
August 2, 2008 at 8:33 am

#382115

Comments posted to this topic are about the item The Ultimate Missing Index Finder

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

arbarnhart-780010 Old Hand Points: 318 More actions · Answer 1

What steps do you need to take to prepare a database for this to return anything? I run it on our database, which has over 400 tables and over 1000 queries many of which have never had any sort of optimization applied and it returens no records. I expected to get inforrmation overload back.

Ronnie.Doggart SSC Enthusiast Points: 188 More actions · Answer 2

Had to modify it to get it to work on my system:

JOIN sys.dm_db_missing_index_details ON sys.objects.object_id=dm_db_missing_index_details.object_id

modified to be

JOIN sys.dm_db_missing_index_details ON sys.objects.object_id=sys.dm_db_missing_index_details.object_id

YeshuaAgapao Hall of Fame Points: 3495 More actions · Answer 3

I'll put up a fix. It is working on my system with the missing 'sys.' ,which is probably why I didn't catch it. It is inconsistent with the rest of the code so it is a bug. I'm logged in a non-'sa' accoutn with the sysadmin server role. I can see if it breaks on a non sysadmin account.

arbarnhart-780010 Old Hand Points: 318 More actions · Answer 4

That fix does nothing for me; still zero records from a datbase that is very suspect as far as optimization goes. I am not a DBA and this database is the product of a group of developers running rampant. Are there some settings I need to adjust so it captures this information?

YeshuaAgapao Hall of Fame Points: 3495 More actions · Answer 5

I've recreated the problem. It returns no results if you do not have the 'VIEW SERVER STATE' permission. Looked it up in the sys.dm_db_missing_index_* entries in BOL. server-level sysadmin has that right bundled in. Not sure about which of the others. dbo (database owner) does not (it does give 'VIEW DATABASE STATE' though, which is insufficient for sys.dm_db_missing_index_*). I'll update the documentation.

YeshuaAgapao Hall of Fame Points: 3495 More actions · Answer 6

If you are on a shared hosting box, you can ask one of the admins to put this proc into your database for you with an 'EXECUTE AS' clause that impersonates to an account with the 'VIEW SERVER STATE' option. They may want to review the proc (it uses DB_ID() to restrict to the current DB) first because they probably won't want you to be able to peek into other customers' databases.

arbarnhart-780010 Old Hand Points: 318 More actions · Answer 7

arbarnhart-780010

Old Hand

Points: 318

October 6, 2008 at 10:58 am

#881235

Still nothing.

arbarnhart-780010 Old Hand Points: 318 More actions · Answer 8

I am running my own server instance on my machine; I have total control.

YeshuaAgapao Hall of Fame Points: 3495 More actions · Answer 9

Are you using -x option for the startup options for the service (or when starting manually from the command line)? It disables some DMV data collection (not sure if it affects sys.dm_db_missing_index_* dmvs but I think it would). It also breaks SET STATISTICS TIM ON and SET STATISTICS IO ON.

-x disables all these (from BOL) to squeeze a few percentage points of performance:

SQL Server performance monitor counters

Keeping CPU time and cache-hit ratio statistics

Collecting information for the DBCC SQLPERF command

Collecting information for some dynamic management views

Many extended-events event points

arbarnhart-780010 Old Hand Points: 318 More actions · Answer 10

I figured it out - a bonehead move on my part. We keep our database in source control by scripting it with DB Ghost. To get changes, I script anything I am working on and check it in, them get all the scripts and run them. So I had a "new" database. I have to run our application and beat on it a while. I did it a little and started getting results.

YeshuaAgapao Hall of Fame Points: 3495 More actions · Answer 11

Ideally you would want to run this on a database with production load. If you can't, QA load is a distant 2nd-place alternative. DEV traffic won't get much insight on global worst offenders.

Scott Herbert-214404 Old Hand Points: 387 More actions · Answer 12

This is a great proc; I've been using something similar for a while, but this adds in a few more useful columns. One thing that's baffling me however is that some of the highest results from this proc I've subsequently added indexes for, and they still turn up (unlike most instances where as soon as the index is added, they disappear from the proc results).

For example, from the proc results:

schema name = Analysis

Table name = Notice

Equality = NULL

Inequality = [NoticeStageID]

Include = [ID], [AnalysisID], [FileID]

Score = 25953.496496

I've then added the index

CREATE INDEX [ixAnalysisHolding_AnalysisID_CustodianBeneficialOwnerID]

ON [Analysis].[AnalysisHolding]

([AnalysisID], [CustodianBeneficialOwnerID])

INCLUDE([ID], [RegisteredHolderBeneficialOwnerID], [BeneficialOwnerInvestorID], [Shares])

WITH ( FILLFACTOR = 90 );

and no difference to the proc results. What's going on? Am I missing the significance of the "Inequality" column, or is this a factor of the way the DMVs work?

YeshuaAgapao Hall of Fame Points: 3495 More actions · Answer 13

We have the issue with provider worklist at Carle Clinic. I think it is a SQL server bug that involves recording data in the DMVs. Might be a data type issue but I doubt it because other non-uses of indexes due to data-type casting don't show up.

I know that SQL server can only use one nonequality column in a seek. Inequality is any seek that is not for '=' -- '>', '<', NOT IN(), LIKE etc..

Scott Herbert-214404 Old Hand Points: 387 More actions · Answer 14

thanks, that's (the non-equality bit) an interesting piece of trivia.