The Ultimate Missing Index Finder

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jesse Roberge » The Ultimate Missing Index Finder

28 posts, Page 1 of 3

1 2 3 »»»

The Ultimate Missing Index Finder

Rate Topic

Display Mode

Topic Options

Author

Message

YeshuaAgapao

Posted Saturday, August 02, 2008 8:33 AM

Say Hey Kid

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 5:17 PM
Points: 701, Visits: 207

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

Post #545640

arbarnhart-780010

Posted Monday, October 06, 2008 6:06 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, June 23, 2010 7:04 AM
Points: 18, Visits: 60

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.

Post #581027

Ronnie.Doggart

Posted Monday, October 06, 2008 8:57 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, September 27, 2010 1:29 AM
Points: 20, Visits: 182

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

Post #581133

YeshuaAgapao

Posted Monday, October 06, 2008 10:33 AM

Say Hey Kid

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 5:17 PM
Points: 701, Visits: 207

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.

Post #581240

arbarnhart-780010

Posted Monday, October 06, 2008 10:40 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, June 23, 2010 7:04 AM
Points: 18, Visits: 60

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?

Post #581247

YeshuaAgapao

Posted Monday, October 06, 2008 10:48 AM

Say Hey Kid

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 5:17 PM
Points: 701, Visits: 207

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.

Post #581252

YeshuaAgapao

Posted Monday, October 06, 2008 10:56 AM

Say Hey Kid

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 5:17 PM
Points: 701, Visits: 207

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.

Post #581255

arbarnhart-780010

Posted Monday, October 06, 2008 10:58 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, June 23, 2010 7:04 AM
Points: 18, Visits: 60

Still nothing.

Post #581256

arbarnhart-780010

Posted Monday, October 06, 2008 11:00 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, June 23, 2010 7:04 AM
Points: 18, Visits: 60

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

Post #581258

YeshuaAgapao

Posted Monday, October 06, 2008 11:06 AM

Say Hey Kid

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 5:17 PM
Points: 701, Visits: 207

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

Post #581262

« Prev Topic | Next Topic »

28 posts, Page 1 of 3

1 2 3 »»»

Permissions