sql server - T-SQL for finding Redundant Indexes -

Is anyone aware of T-SQL script that can detect unnecessary indexes in the entire database? An example of an unnecessary index in the table would be:

  Index 1: 'column', 'column b', 'column c' index 2: 'column a', 'column b'  
 
  
  
  
   
  "Post-text" itemprop = "text"> 
 There are situations where there is no redundancy, for example,  column C  was a hose field, but you would sometimes get it quickly You do not need a main lookup for your  index 1  Choose ColumnC from your table where Columns = 12  

On the other hand, index 2 is very small, so it is very easy to use them For queries, memory can be read in memory, which requires an index scan:

  select from YourTable * where ColumnnA '% hello%'  

That's why they are not really unnecessary.

If you do not agree with the argument above, you can search for "unnecessary" indexes like:

 ; (Such as a.object_id, a.index_id, cast (as col_list.list varchar (max)) select from the list (select different object_id, index_id from sys.index_column) apply a cross (cast (column_id varchar) (16) as in [text ()] sys.index_columns b, where a.object_id = b.object_id and a.index_id = b.index_id f or xml path (''), type ) Select col_list (list)) object_name (a.object_id), as tableName, add as bind to bind.exe, as ASI.name, as sys.sysindexes asi asi.id = a .object_id And asi.indid = a.index_id a .object_id = b.object_id and A.object_id = b.object_id and lane (a.list) & gt; Lane (B.list) and left (A.List, LN (B.list)) = BIL Include in sys.sysindexes BSI on BSI.ID = B.Object_ID and BSI.IIDID = B & Ax_ID  

Bring the cake to your users, because the display is "unexpectedly" Decreases: -)