By: Rob Fisch | 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
- 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.
(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.
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:
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
