SQL Server - 检测非索引列但在 WHERE 子句中使用
如何检测 WHERE 子句中包含但用于索引的列?
小背景:
直到表只有很少数量的记录时,一切都会好起来,一旦开始有数百万条记录,则应该为存储过程中的 WHERE 子句中使用的列创建索引,内联查询等,
由于我们有数百个存储过程和查询,这些存储过程和查询经常被开发人员更改,所以我希望有一种自动方式来识别 WHERE 子句中使用但未创建索引的那些列。如何在 SQL Server 2008 中做到这一点?
How to detect a column included in WHERE clause but used in indexed?
Little Background:
Until the time the table has few number of records things will be okay, once it started having millions of records then index should be created for a column which is used in WHERE clauses in stored procs, inline queries etc.,
Since we have hundreds of stored procs and queries that often gets changed by the devs I wanted to have a automated way of identifying those columns that are used in WHERE clauses but not an index is created. How to do that in SQL Server 2008?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
使用“缺少索引”dmv 的内容来发现可以添加什么?
编辑:我将限定...这允许您根据成本/使用/收益考虑索引。
索引只能在周日 04:00 使用,因此从成本角度来看可能不值得
Use the "Missing indexes" dmv stuff to spot what could be added?
Edit: I'll qualify... this allows you to consider indexes based on cost/use/benefit.
An index may only be used at 04:00 on a Sunday, so from a cost perspective it's probably not worth it
您应该首先确定昂贵的查询:如何记录并查找最昂贵的查询?
You should first identify your expensive queries: How Can I Log and Find the Most Expensive Queries?