The Ultimate Missing Index Finder

  • YeshuaAgapao

    Hall of Fame

    Points: 3495

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

  • arbarnhart-780010

    Old Hand

    Points: 308

    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

    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

    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: 308

    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

    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

    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: 308

    Still nothing.

  • arbarnhart-780010

    Old Hand

    Points: 308

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

  • YeshuaAgapao

    Hall of Fame

    Points: 3495

    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: 308

    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

    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

    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

    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

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

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

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