Part 2: Analyze SQL Profile traces with SSRS dashboard. How does it work?

As described in my previous blogpost 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 trace files. In this blogpost I will explain what you need to install and configure to use this dashboard.

What do you need to install:

• SQL Server 2008 R2 database engine. This database server is used to import all trace files into a database.
• SQL Server Reporting Services. (2008 R2). This reporting server is used to host the dashboard.
• SQL Server Business Intelligence Development Studio. With BIDS you can modify the dashboard and deploy the reports to the reporting server.

What else do you need:

• Use the default trace file template of SQL Profiler to trace your application. Be aware to use the correct filter for the trace, so you got only these queries you want to analyze. For instance you can filter on hostname, spid or applicationname.
• Script to create database and table in which all trace files are imported.
• Import script to import the SQL Profiler trace files (.TRC)
• The SSRS reports of the dashboard to analyze the trace files.

First we will start to create a new PerformanceAnalyze database and import table: TraceFileImport

-- BEGIN Performance analyze script created by André van de Graaf
-- Blog site http://www.keepitsimpleandfast.com/

USE MASTER
GO

-- Create PerformanceAnalyze database
CREATE DATABASE [PerformanceAnalyze] ON PRIMARY
(NAME = N'PerformanceAnalyze',
FILENAME = N'D:\Data\PerformanceAnalyze.mdf',
SIZE = 102400KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'PerformanceAnalyze_log',
FILENAME = N'D:\Data\PerformanceAnalyze_log.ldf',
SIZE = 10240KB , FILEGROWTH = 10%)

GO

USE [PerformanceAnalyze]
GO

-- Create table in which all trace files will be uploaded.
CREATE TABLE [dbo].[TraceFileImport](
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[TextData] [ntext] NULL,
[ApplicationName] [nvarchar](128) NULL,
[NTUserName] [nvarchar](128) NULL,
[LoginName] [nvarchar](128) NULL,
[CPU] [int] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[Duration] [bigint] NULL,
[ClientProcessID] [int] NULL,
[SPID] [int] NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[BinaryData] [image] NULL,
[ImportID] [nvarchar](50) NULL,

PRIMARY KEY CLUSTERED
([RowNumber] ASC)
WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-- END Performance analyze script created by André van de Graaf


Now we have the database and import table, so we are ready to import the first trace files. The trace files will be imported by the stored procedure: PA_ImportTraceFile.

-- Create stored procedure to import the trace files
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PA_ImportTraceFile]
(@ImportID nvarchar(50), @TraceFile NVARCHAR(2000)) AS
INSERT INTO TraceFileImport
SELECT eventclass,TextData,ApplicationName,NTUserName,
LoginName,CPU,Reads,Writes, Duration, ClientProcessID,
SPID,StartTime,EndTime,BinaryData,@ImportID AS importid
FROM :: fn_trace_gettable(@TraceFile,default )
GO

This stored procedure uses 2 parameters: Importid,Tracefilename_and_location
The ImportID is used in the SSRS reports to select your trace file.

Example:
EXEC PA_ImportTraceFile 'CustomerX_1','D:\Traces\Cust_Trace1.trc'

In my next blogpost I will make the SSRS reports available and will explain what you need to configure at the reporting server to use these reports. So stay tuned for the SSRS reports.

Previous posts in this series:
Part 1 Analyze SQL Profile traces with SSRS dashboard.