Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Index Usage Report in SQL Server


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


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


Last Updated: 2007-12-04


get scripts

next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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


Learn more about SQL Server tools