Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Queries, Plans, and Indexes

By ericzbeard,

The reason I wrote this script was to help locate the queries using indexes. Sql Server 2005 allows you to see exactly how your indexes are being used, and sometimes you spot one that has only been used a few times, or maybe one that only gets scanned, with no seeks. You want to find where the indexes are being used so you can fix the queries somehow.

Note that the query ignores indexes in the sys schema, and it also ignores any index references it finds directly under "Update" nodes, so you can focus on where the indexes are actually being used.

This can take a long time to run. On my system ( a few hundred tables, 30Gb data size), it took almost 30 minutes and returned over 100,000 rows. You should select the contents into a temp table and go from there with your analysis.

Total article views: 1647 | Views in the last 30 days: 2
 
Related Articles
FORUM

Disabling index by using select query

Disabling index by using select query

FORUM

defining query to use an existing index

defining query to use an existing index

FORUM

Query about Partition the table & Index

Query about Partition the table & Index

FORUM

Index

index

SCRIPT

Query to get tables with no clustered indexes

Query to get tables with no clustered indexes(2K5,2K8,2K8 R2)

Tags
indexing    
query plans    
 
Contribute