sys.dm_db_missing_index_details 不返回任何行
我尝试查看 sys.dm_db_missing_index_details 以检查 SQL Server 2005 数据库上是否缺少索引。它不返回任何行。
它有可能应该是空的，但可能性很小，因为我没有在任何表上添加任何索引（除了通过创建主键获得的索引）。我还针对它运行单元测试以及临时开发测试（使用 Linq to SQL），因此存在一些针对它的活动。
我需要打开此数据的捕获吗？
这是否仅在 SQL Server 2005 的某些版本上受支持？
预先感谢您提供的任何帮助。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
另一种选择是直接查询计划缓存——这还有一个好处，就是它可以为您提供需要索引的查询。有一个相关的问题这里 -- BankZ 的答案有一个完整的 SQL 脚本来完成这项工作。
但它可能运行缓慢 - 计划采用 XML 格式，因此通过此查询，我们要求 SQL Server 执行大量 XML 工作而不是表工作。但它确实有效:-)
与主缺失索引表一样，如果 SQL Server 重新启动，计划缓存将被清除。
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.
似乎默认情况下它是打开的 - 尽管检查您用于启动的任何快捷方式并确保它不是使用
来自 http://msdn.microsoft.com/en-us/library/ms345524(v=SQL.90).aspx< /a>
此外，您还想知道，如果 SQL Server 使用查询优化器，则在运行查询时会填充该表 - 该表在您重新启动 SQL Server 时会被清除。
来自 http://msdn.microsoft.com/en -us/library/ms345434(v=SQL.90).aspx
最后，有一篇文章讨论了您可能知道或不知道的限制，但我会发布以防其他人遇到这篇文章并需要：http://msdn.microsoft.com/en-us/library/ms345485(v=SQL.90).aspx
我没有看到某些版本中缺少该功能的任何信息，但您将需要某些权限：
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
-xFrom http://msdn.microsoft.com/en-us/library/ms345524(v=SQL.90).aspx
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
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: