0

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?

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
asyncwait
  • 4,425
  • 4
  • 37
  • 52

2 Answers2

4

Use the "Missing indexes" dmv stuff to spot what could be added?

  • Example (this is weighted so you have an idea of cost too)
  • MSDN

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

gbn
  • 408,740
  • 77
  • 567
  • 659
0

You should first identify your expensive queries: How Can I Log and Find the Most Expensive Queries?

Community
  • 1
  • 1
Mitch Wheat
  • 288,400
  • 42
  • 452
  • 532