3

I have tried to look at sys.dm_db_missing_index_details to check for missing indexes on my SQL Server 2005 database. It is returning no rows.

It is possible that it should be empty but highly unlikely as I have not added ANY indices on any table (except the ones you get by creating primary keys). I am also running unit tests as well as adhoc development tests (using Linq to SQL) against it so there is some activity against it.

Do I need to turn on the capturing of this data?

Is this only supported on certain editions of SQL Server 2005?

Thanks in advance for any efforts to help.

Mark
  • 1,516
  • 2
  • 14
  • 24

2 Answers2

6

It appears that it's on by default - although check any shortcut you are using to launch and make sure it's not launching with a -x

From http://msdn.microsoft.com/en-us/library/ms345524(v=SQL.90).aspx

This feature can only be disabled if an instance of SQL Server is started by using the -x argument with the sqlservr command-prompt utility.

Also you'll want to know that the table is populated as queries are run if SQL Server uses the query optimizer - this table is cleared when you restart SQL Server.

From http://msdn.microsoft.com/en-us/library/ms345434(v=SQL.90).aspx

Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. Missing index information is kept only until SQL Server is restarted. Database administrators should periodically make backup copies of the missing index information if they want to keep it after server recycling.

Lastly there is an article that goes into the limitations here that you may or may not know about, but I'll post in case someone else happens across this post and needs: http://msdn.microsoft.com/en-us/library/ms345485(v=SQL.90).aspx

I didn't see anything about the feature being missing in some versions but you WILL need certain permissions:

Users must be granted the VIEW SERVER STATE permission or any permission that implies the VIEW SERVER STATE permission to query this dynamic management view.

DKnight
  • 733
  • 3
  • 15
  • Hi DKnight. Awesome answer - VERY thorough. Unfortunately it doesn't look like my SQL server is being started with -x. Also I have tried looking at the query as 'sa' and still see nothing so I suspect it is not a permission issue in this case. Any suggestions of what else I can check? Happy to post the result of the system queries if more information is needed. – Mark Apr 08 '11 at 08:55
  • The only other thing that I saw suggested was to make sure that you are running a query that actually needs to be optimized. The way to check is to run a 'Display Estimated Execution Plan' and verify that you have "Missing Index (Impact...." in green. Not sure if that is good enough to generate the table entry, but running that query should put something in that table – DKnight Apr 08 '11 at 12:59
  • Also if you are connected to multiple DB servers verify that you are checking the correct one - i could see myself making that error – DKnight Apr 08 '11 at 13:00
  • Hi DKnight. I will try the executation plan suggestion. Thanks for the suggestion about the correct db as well - the simplest suggestions are sometimes the most useful. – Mark Apr 14 '11 at 03:04
  • _The way to check is to run a 'Display Estimated Execution Plan' and verify that you have "Missing Index (Impact...." in green._ Hmm, seeing some of the boxes in green although no notification about Missing Index. Maybe this is a clue but unfortunately not the answer. Thanks for your help though @DKnight – Mark Apr 14 '11 at 03:57
  • Try to run the query with Actual Query Plan. When the results are returned in SSMS, there should be a tab for Execution Plan. Activate that tab and you should see a grab for the Execution Plan. At the top may be a bar indicating there is a missing index. If not, SQL couldn't automatically determine how to better optimize it with a new index. The only other thing you could do is to force it into a intentionally bad situation by crafting a query which you know will produce a table scan. – Taylor Gerring Apr 14 '11 at 11:02
1

Another option is to query the plan cache directly -- this also has the benefit that it can get you the query that wants the index. There's a related question here on SO -- the answer from BankZ has a complete SQL script that does the job.

It may run slowly though - the plans are in XML format, so with this query, we're asking SQL Server to do lots of XML work rather than table work. But it does work :-)

And as with the main missing index table, the plan cache is cleared down if SQL Server is restarted.

Community
  • 1
  • 1
Chris J
  • 30,688
  • 6
  • 69
  • 111
  • Thanks for taking the time to respond Chris. I ran the query and it returned 0 rows indicating that there is something more fundamental here. Thanks for the note about loosing the plan cache on restart - not the case but good to record in this thread. – Mark Apr 14 '11 at 04:32
  • The "something fundamental" that you're missing may simply be that SQL really doesn't think it needs more indexes :-) Have you looked at the query plans for the queries your executing? Also, how many rows are there in the tables -- it may be that there's so few rows in them that SQL Server thinks that extra indexes aren't going to do anything significant. – Chris J Apr 14 '11 at 08:01
  • Hi Chris, you may be right but that doesn't seem likely at this point. That data tables I am using range from 100 - 2,000 rows - not major in the grand scheme of things. I only have primary and foreign keys defined so I would have expected some indexing improvements. Thanks again for your time answering this - much appreciated. – Mark Apr 15 '11 at 05:08