Monday, May 9, 2011

Part 3: Analyze SQL Profile traces with a SSRS dashboard. The reports itself


As described in my previous blogpost Part 1 and Part 2, I started a series in which I will explain how you can make a dashboard in SQL Reporting Services to analyze your SQL Server Profiler traces. In this last blogpost I will supply all reports for dashboard.

This dashboard contains 13 reports and can be downloaded from here:

  1. TracePerformanceOverview. This is the dashboard itself. It is the starting point of your analyze. It contains links to all other 12 reports.
  2. ApplicationTOPXCPU: Overview of CPU usage grouped by application.
  3. ApplicationTOPXReads: Overview of Reads grouped by application.
  4. ApplicationTOPXTimeSQL: Overview of SQLDuration grouped by application.
  5. ApplicationTOPXWrites: Overview of Writes grouped by application.
  6. RepeatingTOPXCPU: Overview of recurring queries summarized by CPU.
  7. Repeating TOPXReads: Overview of recurring queries summarized by Reads.
  8. Repeating TOPXTimeSQL: Overview of recurring queries summarized by SQL Duration.
  9. Repeating TOPXWrites: Overview of recurring queries summarized by Writes.
  10. TOPXCPU: Overview of queries ordered by CPU usage.
  11. TOPXReads: Overview of queries ordered by Reads.
  12. TOPXTimeSQL: Overview of queries ordered by SQL Duration.
  13. TOPXWrites: Overview of queries ordered by Writes.
  • Deploy all these reports to your reporting server in one folder. The reports are made on a SQL 2008 R2 Reporting server. Therefor they can only be deployed on a SQL 2008 R2 Reporting server. If you deploy it on a SQL 2008 Reporting server you can get next error:  [rsInvalidReportDefinition] The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded.
  • Create a Data source on your reporting server named: ExactPerformanceAnalyze

 Enjoy using the dashboard. Please leave a comment if you have questions or suggestions.

8 comments:

Laurie said...

I get errors when deploying to report server Error 1 [rsInvalidReportDefinition] The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded.

Could you add some additional instructions?
Thanks

André van de Graaf said...

Laurie, The reports are made on SQL 2008 R2 Reporting Server. So you need to use a SQL 2008 R2 reporting server. I think you uses a SQL 2008 Reporting server. The database can still stay on SQL 2008. I will update the blogpost with the requirement to have a SQL 2008 R2 reporting server. Thanks for your useful comment.

Laurie said...

Any chance on getting the non R2 version?

André van de Graaf said...

Lauri, no sorry, I made the reports on SQL 2008 R2, i do not have à SQL 2008 reporting server

Ashrafur Rahaman said...

Very helpful, Thanks very much.

Al said...

Thank you for creating these reports, I will try to put them to good use.
I do have a question, more towards the trace it self, I have a trace table for an application that crated an API Cursor, the summary of the data is as follows.
EventClass E_Count CPU Duration
11 1 NULL NULL
45 1 0 0
70 1 NULL NULL
10 1 16 111
11 1 NULL NULL
45 1 12688 13630
10 1 12688 13630
11 1 NULL NULL
Grouped Events:
EventClass E_Count CPU Duration
10 288011 26647 28667
11 288011 NULL NULL
45 288011 15359 23404

I want to accurately report on the data, but not sure if I should exclude certain events, are the null values considered time not spent in a SQL Task?
any suggestions would be greatly appreciated.

André van de Graaf said...

AL,

The time not spend is the difference of the end time of trace line X and the start time of trace line X+1. So this will not work if you do group by. It will also not working if you have used a filter on your trace file. If you are using a filter the Time no spend is bigger as it really is.
I hope this will help you, if not please let me know.

Gr. André

Unknown said...

Hi André,
I can't download the ExactPerformanceAnalyze rdll package.
Is it possible to end it to me.

Thank you
Mathias FATENE