Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 1539 | Views in the last 30 days: 7
 
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

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones