SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Ultimate Missing Index Finder


The Ultimate Missing Index Finder

Author
Message
YeshuaAgapao
YeshuaAgapao
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1901 Visits: 211
Comments posted to this topic are about the item The Ultimate Missing Index Finder



arbarnhart-780010
arbarnhart-780010
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 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.
Ronnie.Doggart
Ronnie.Doggart
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
YeshuaAgapao
YeshuaAgapao
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1901 Visits: 211
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
arbarnhart-780010
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 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?
YeshuaAgapao
YeshuaAgapao
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1901 Visits: 211
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
YeshuaAgapao
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1901 Visits: 211
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
arbarnhart-780010
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 60
Still nothing.
arbarnhart-780010
arbarnhart-780010
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 60
I am running my own server instance on my machine; I have total control.
YeshuaAgapao
YeshuaAgapao
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1901 Visits: 211
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search