devxlogo

Watching Index Use

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.

See also  How HealthStream Learning Center Supports Healthcare Education and Compliance
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist