Systems Engineering and RDBMS

Archive for July 3rd, 2007

Performance Dashboard Reports for SQL Server 2005 – Part II

Posted by decipherinfosys on July 3, 2007

In one of our previous blog post, we had covered how to install the performance dashboard reports in SQL Server 2005. In this post, we will go over some of those reports. Once you have loaded the performance_dashboard_main report (see the previous post on how to do that), you will then have the System CPU utilization graph and below it you will have the current activity and the historical information and Misc. information pertaining to that instance. That is shown in the image below.

From here, you can then drill down into the specifics like looking at the user sessions, the user requests, the wait events occuring in the system etc.. All of those are clickable links on this report which present a drill-down version of the data when those are clicked. Most of those are pretty self explanatory. The one very important piece is the historical information – this information comes from some of the DMVs in SQL Server 2005 which capture this information (if the instance is re-started this information will be re-computed). The “Waits” give the wait states (wait event tuning methodology) and the “IO Statistics” give you information on the databases that are producing the most IO and then you can drill down further into it and see the specific objects that are causing those IO’s. The expensive queries reports are particularly useful for helping troubleshoot query performance issues – these are presented based on the duration, CPU, logical and physical reads, logical writes and by CLR time. I do not want to just clutter the blog with posting one image for each one of these since all of these are pretty much self explanatory and you need to play with it in your environment to see the benefit.

I will talk a bit about the last section of the performance dashboard – the Miscellaneous section. This one has Active Traces as the first report and this identifies the current traces that might be running in your system – now, even if you are not running any active traces, this report will always show you one and that is because SQL Server automatically traces some of the events. This report is just for information sakes. The next one in this section is the databases report which can be used to get a quick overview of some of the configuration options. The next one is the missing indexes report – be aware that these are just suggestions by the optimizer based on your data set, your query load and your hardware configuration due to which it might make a suggestion to include or change indexes. You should carefully evaluate each recommendation since you know your system and should bee building those rules.

One question that our client had asked us was to evaluate the performance impact of having these reports deployed on the production server – we measured the impact on one of the test servers simulating the production load and some of these reports do hit a lot of the DMVs and DMFs but the overall resource utilization was very less. With the wealth of information that they provide, these should be used for diagnosing issues further and are a welcome addition to the database developer/DBA arsenal.

Posted in SQL Server | Leave a Comment »