Creating Custom Reports for the DBA

1.       You donít need† SQL Server Reporting Services to do this

2.       You donít need IIS installed

3.       You do need SQL Server 2005 Service Pack 2 on your client tools e.g. management studio

4.       You donít need† Service Pack 2 on your Servers.

5.       User Databases must be in 9.0 mode for database reports.

6.       You can put the rdl files on your workstation or the servers.

7.       You donít need BIDS.

8.       Reports can also be deployed with SSRS

9.       Doesnít work with SQL 2000


         Iím not totally sure that everyone is familiar with the addition of custom reports to Management Studio with service pack 2 for sql server 2005.

o    Iíll just make a quick mention that you should always apply service packs and patches to client tools, all versions, even if you donít have the database engine installed.

         Iíve been running my own custom reports against SQL Server since 6.5 publishing them to html pages, Reporting Services gave me an alternative method and now Service Pack 2 expands upon this further.

o    Hereís a couple of screen shots of what was possible prior to reporting services

o    These are static html pages generated from the web assistant wizard.







         Hereís a hyperlink to a custom report which shows details of the procedure cache† -†† Procedure Cache Report

o    Iíve saved these as mhtml files rather than screen shots.


         As it stands all this works locally, you either have the rdl files on your workstation and run them against every server, or you install them on each server. I have not attempted to create any enterprise solution, that would have to be done with SSRS.

         To make the reports easy to use I have where possible placed the code within the reports, this is intentional in an attempt to make them totally portable.

        Some of the reports do require code on your server and it is assumed that you have a database exclusively for DBA use on each server, failing that you would need to deploy to master or msdb, I donít normally like adding this type of code to application databases, or system databases.


Finding Custom Reports on the menu



















The following Reports do not need any code on the Server, typically run these against master or msdb


         Database Backup and Transaction Log Backup Status

o    This will colour according to how long since the last backup

o    it assumes all databases in full recovery should have transaction log backups

o    You may wish to alter the setting according to your log backup frequency

         Workers , Signal Waits and cpu counters

o    This is essentially ums stats

o    Use this to check on numbers of workers and and worker waits

         Top 20 indexes† in cache ( this is database specific so run against the database youíre interested in )

o    Itís sometimes handy to know which tables and indexes are actually in cache

         Procedure Cache Status

o    This was very useful when I was tracking memory problems

         Active Running Processes on the Server

o    This captures what is actually running when you open this report

o    It also captures the entire text of the running commands

o    Itís excellent for capturing that query from hell

o    May produce a big result set

         Plans in Cache

o    OK this is messy and produces a lot of output

o    You may want to restrict output further

o    Hopefully the report explains it self.

         Blocked Users

o    This was for specific use to show who was blocking who when blocking occurred

o    Useful if you want to deploy a SSRS report to show basic blocking chains for non DBA support staff or you want to restrict users running their own queries against a production server

         Buffer Cache Usage

o    Check out how your data cache is being used

o    With many servers now deploying lots of memory it can be interesting to see how itís actually used

o    You may be surprised too!

         User Sessions Ė similar to sp_who2

o    This was developed as a SSRS report to keep support staff off the production systems

o    ( stops endless sp_who2 commands )

o    Youíll probably want to customise the colour scheme


         So right click and save the zip files to your PC, extract  the rdl files below to a folder on your workstation and open them in SSMS Ė itís that easy!


o    BackupStatus

o    blocked users

o    Buffer Cache

o    Cache Plans

o    Procedure Cache Status

o    Running Processes

o    short sysprocesses

o    Top 20 Indexes in cache

o    Workers


         You donít need to load them into BIDs, just browse to them from Custom reports and they will run.


         Here are examples of each report, except† Plans in Cache (CachePlans1.rdl ) which was too difficult to manipulate to produce a suitable example.


o    Database Backup and Transaction Log Backup Status

o    Workers , Signal Waits and cpu counters

o    Top 20 indexes† in cache

o    Procedure Cache Status

o    Active Running Processes on the Server

o    Blocked Users

o    Buffer Cache Usage

o    User Sessions


You will need BIDS if you wish to modify the reports ,deploy them to SSRS or change the logo


So what is this all about then?

         To support or tune a database server you must have the ability to collect† statistical performance data, perfmon counters for example.†

         You must be able to establish a baseline to which performance can be compared, otherwise how do you know if your server is busier than normal or quieter than normal.

         You must have regular reports of performance and reports that assist in diagnosing problems of any sort.

         You should also build Application Dashboards that track your particular applications, I will include a fictitious example.

         These reports are intended for the DBA therefore they assume you have sysadmin/dbo rights to the databases.

         You should have a DBA admin database on each server, this will hold your custom code.



I have a number of other reports which I will publish, some of which link to other statistical gathering processes





©† February 2008