Index Usage Report in SQL Server

By: Rob Fisch   |   Updated: 2007-12-04   |   Comments (1)   |   Related: > Reporting Services Report Builder

Problem

In a previous tip we looked at how to determine which indexes were being used for a SQL Server 2005 database.

"Databases have two primary storage needs; data pages and index pages.  Understanding and viewing the actual data in your tables is pretty straightforward by running some sample queries to get an idea of what columns are being used as well as what type of data is actually being stored.  On the flip side of this, it is often difficult to know exactly what indexes are being used and how they are being used.  So how can you get a better understanding of how your indexes are being used and what operations are occurring (inserts, updates, deletes, selects)?"

To take this original tip a step forward, the following Reporting Services reports have been created to display the data in a more formalized way.

Solution

This companion article provides downloadable Reporting Services reports that contain the solution in a usable set of reports. One report uses Windows Authentication and the other uses SQL Server authentication, which prompts you for those credentials.

Both reports prompt for servername (in the event you want to run the report against multiple servers).

(Note: These reports were created with the SQL Server 2005 client tools at SP2. If you have a pre-SP2 system and these files do not work, contact me and I'll try to help.)

If you have the SQL Server 2005 SP2 client tools loaded on your desktop you do not even have to deploy them. You can download the complete Report Designer solution and run it locally.

Download the solution here

Unzip the solution to a folder and open the IndexUsage.sln file from Visio Studio. From there just run the reports as desired.

• Download the solution here
• Unzip the solution and use the .rdl files in the solution directly on your Reporting Services server.
  
• Configuring the Reporting Server

Native Mode

• Follow these instructions for Native Mode . When you expand the Data Sources object you will see two datasources (pictured below). Configure them both as instructed with the one in the link.
  
  
  
  

Sharepoint Integration Mode

• Follow these instructions for Sharepoint Integration Mode
• The data source section will contain two datasources. Configure them both as instructed with the one in the link.
  
  
  

Running the reports

• The Windows Authentication report - Unless you have Kerberos delegation configured, you will run into the the 'double hop' issue when running the reports remotely. You should be logged into the Report Server (via RDP or VNC or some other remote method) and use the Report Server's web browser. However, this situation is not appropriate for everyone, especially if you are sharing the reports with other staff that might not have access to the system.
• The PromptSQLAccount report - Use this directly from any workstation, however you will need to know the credentials of a SQL user account with access to the Master database.
  

Next Steps

• Look for more MSSQLTips.com conversions coming soon.
• Take a look at these other Reporting Services tips
• Take a look at these other converted tips:
  • Reporting Services Report - Determining Free Space Per Database

About the author

Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips