Index Usage Report in SQL Server

By:   |   Comments (1)   |   Related: > Reporting Services Report Builder

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.

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.)

Running the reports locally from the Desktop with SQL Server 2005 SP2 client tools loaded:

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.

Configuring and running the reports from a Reporting Services server:
  • 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

(Note: These reports do NOT use a shared data source. The data source is custom and dynamic and is embedded in the dataset.)

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.
Once run, these reports can easily be exported to spreadsheets or PDFs, in the event you need to share information with others easily.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rob Fisch 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

Comments For This Article

Wednesday, November 14, 2012 - 9:15:23 PM - Dang Kinh Van Back To Top (20347)

This article is very god for many people, thanks alot of MSSQLTips !

get free sql tips
agree to terms