Grumpy Old DBA - Beware the darkside !

I sometimes discover that the built in reports for SQL Server within SSMS are an unknown, sometimes this is because not all the right components were installed during the server build, other times is because generally there’s never been great reporting for the DBA from the SQL Team so no-one expects to find anything useful for free!

We did get the performance dashboard with SQL 2005 and this can be made to work with SQL 2008 with just a few code changes.

We also have the MDW database/management reports, but although I have this running on a couple of servers I’ve never really fallen in love with it.

( Once upon a time you could download the rdls for these reports but as far as I can tell the links are no longer available, I don’t know if this is deliberate. )

I use a few of these reports now and again, I have my own dashboards, reports and queries which are customised to particular environments, but if I’m faced with an “unknown” server then these reports will often be my first port of call.

Like anything using the dmvs remember that under pressure dmvs may shed or stop collecting data. Data is usually cumulative since last service restart, is cleared on a service restart and certain data may be cleared under specific circumstances.

The only way to collect dmv data for analysis is to take regular snapshots of the data into permanent tables of your own and work from there. At best some dmv data can lead you into believing you have problems where you don’t.

The relationships between dmvs can sometimes be confusing; obtaining information about partitioned tables is a good example of where it’s not as easy as you might wish.

Anyway Reports are available by context when you right click in SSMS object explorer; here I’m clicking on the actual instance/server.

( Note that to protect the innocent names and data have been changed/blanked where appropriate )

1. Reports by Server/Instance

To start off open SSMS, register your Server and right click on the instance/server

Choose Reports – Standard Reports - to view available reports for that context.

Here’s the list of Standard Reports for the instance, 23 reports in all and appearing to cover a number of useful areas.

Click on the individual report you wish to run to bring it to screen.

• I’m not going to screenshot every report but I will include a few examples.
• The main problem is that some of what might appear useful take too long to drill down and your actual chance of finding what you’re looking for is very slim, unless you only have a few connections.
• Some reports I’ve never been able to entice into producing an output.
• I’ll summarize this briefly below.

Starting with Server Dashboard which sounds better than it is:

• There are 24 databases on this server and as you can see the two charts do not display correctly, in fact I am totally unable to make them display correctly and neither the export or print option will produce a correctly formatted report.
• Right click to export to pdf or excel, pdf never formats correctly
• I personally would have like web archive as an option here.
• There’s no drill down and to be honest not a great deal of much use.

Non Default Configuration Options expand to show server options – I’m not sure I quite agree with “ Non Default” this sort of infers you’re not running a standard box, anyway this is from a production box showing what you might see. ( these are my “non default” settings! )

You might like to compare to the front screen of the Performance Dashboard which drills down on just about everything and also auto refreshes.

( Note that this report has been visually formatted to give a look and feel which matches other reports I use )

Back to SSMS Reports - Here’s the memory report, there’s drill down by object type too, not shown here.

Here’s one of my own reports: ( sorry can’t make it smaller and readable ! )

The Processes Report – generally far too unwieldy. ( drill down  just too many clicks  shown after )

Top Queries ( statements ) by cpu:

• · Can’t click on the chart to select a statement
• · Statements are abridged
• · Drill down does give object ID so you can track back to the procedure.

    2. Reports by Database – right click on a user database for these

The basic disk usage report is useful, especially as it shows autogrow activity – which should never be like this for a production database – but I don’t fully administer this system. ( as usual names changed to protect the innocent! )

Backup information.

Successful operations list each backup with file details etc. If your logs back up every 5 mins then this is a lengthy report!

  3. Reports for SQL Agent

Right clicking on the SQLAgent will show the following two reports.

Top Jobs report is interesting, I’m not sure about the Job Steps !

   4. Reports for Management

There are also two reports available from Management, but I am unable to see what value they have.

To summarise; If you have no reporting and you didn’t know these reports were available then you will probably find these better than nothing.

The main advantage these have over a query is that most are graphical and that well known phrase “ A picture paints a hundred words ” still holds true.