How can I track how frequently my applications use indexes on a particular table and find out which applications are using the indexes?
Tracking index usage helps you determine which indexes you need to keep, but you can't get this information without a detrimental effect on your server performance. To get the information, run an audit-object trace on the object IDs that represent the indexes. You can also get the information by running a Showplan trace using SQL Server Profiler.
If you're designing your own applications, a good practice is to wrap the table access in a stored procedure or a set of stored procedures. When you do this, you can tune the stored procedure and test it with Profiler to discover which indexes it's using.
You can use Profiler to trace the following kinds of information:
- Events such as Statement Starting, Execution Plan, and Object Open. These events give you the SQL code that SQL Server is executing, the execution plan showing the data-access methods and the indexes your applications used, and the object SQL Server is opening.
- Data columns such as Event Class, Database ID, Object ID, TextData, Server Process ID (SPID), and StartTime. You can use the database ID and object ID to ensure that you're testing the correct base object access. Then, you can use those IDs to identify the correct database and object in the output of the trace events. You can use the event class to get a subset of queries and extract all the query execution plans SQL Server is using so that you can analyze them. You use the TextData column to view SQL statement and execution-plan text. The SPID lets you order by columns in a specific session. And you can use the StartTime column to sort all of the data columns.
- Filters, which you can use to minimize performance degradation. Filters let you sort data based on a specific base object ID, database ID, or SPID. Because you're retrieving less data, the effect on your system's performance is also less. The code in Listing 1, shows you how to get the ID numbers for filtering. The code in Listing 2 shows you how to generate test data from the Northwind database. The code in Listing 3 gives you a sample script that you can use to start the trace.
You can save the results of your Profiler trace to a file or table so that you can use other tools to analyze the results later. For example, after loading the trace output to a database table, you might use the following query to look for usage of the LastName index on the Employees table:
SELECT StartTime FROM DBO.MyTraceTable WHERE TextData LIKE '%OBJECT:(\[Northwind\].\[dbo\]. \[Employees\].\[LastName\])%'
Then, use StartTime to identify the Statement Starting line in the Profiler trace file so that you can determine which SQL statement SQL Server is executing.
