Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Feb 1, 1999

Watching Index Use

Question:
The goal is to watch queries for their use, nonuse, or abuse of indices (using SQL Server 6.5). One of the problems is to be sure that a given index is no longer in use. If an index is dropped, is there any way to watch for a table scan--either on this particular table or in general.

Answer:
The best way I know of would be to use the trace utility provided with SQL Server to save a log of activity before the index drop and then compare it to a trace log generated after the index is dropped. The trace log shows the SQL from each statement and the response time. You would look for queries that use that table whose response times have changed significantly. I would suggest loading the trace log into a SQL table to help facilitate the comparison. You can find documentation on the table structure in the online books.

You could then run those same queries in isqlw using both showplan and show statistics to test whether a table scan is occurring.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap