Catherine Wilhelmsen (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.
This month’s invitation is about monitoring.
This topic is a tough one. I didn’t want to pick just one topic. Especially since I just installed SQL Sentry’s Performance Advisor in production and the Redgate’s DLM Dashboard in Dev. Both products are helping me see my environments clearer.
But what about the need to monitor other items that don’t have a cool tool you can buy off the shelf? Or products like SSRS that have built in metrics collection?
That is when you need to roll your own code to capture metrics to help monitor your own, unique environment. Take Excel reports as an example, or really any application that is used for reporting. It would be wonderful if you could capture metrics and monitor usage of ALL reports across your environment in a consistent manner. Let’s take a look at a way that can be accomplished.
In the beginning
The first thing you need to do is some planning. You need to know what kind of data you want to capture, and how you are going to update the existing reports to capture that data.
I happen to really like the way that SSRS collects usage metrics. So, for my Excel reports, I wanted to collect similar data. This will allow me to have one report to show how all my reports are performing. Maybe my top 10 slowest reports are from 3 different reporting platforms.
I created a couple of tables to hold the non-SSRS report metrics and meta data. I then created a stored procedure to insert the data into the tables. Finally, I added the stored procedure to the bottom of the stored procedures used by the Excel reports I wanted to keep track of.
Step 1
I created a table that held the information I wanted to collect.
CREATE TABLE dbo.ReportType
(ReportTypeID INT IDENTITY PRIMARY KEY
,ReportTypeName AS varchar(20) NOT NULL
)
INSERT INTO dbo.ReportType(ReportTypeName) VALUES ('Excel');
INSERT INTO dbo.ReportType(ReportTypeName) VALUES ('PowerPoint');
CREATE TABLE dbo.ReportLog
(ReportLogID INT IDENTITY PRIMARY KEY
,ReportTypeID INT NOT NULL
,ReportPath VARCHAR(255) NOT NULL
,ReportName VARCHAR(255) NOT NULL
,UserName NVARCHAR(255) NOT NULL
,SprocName VARCHAR(255) NOT NULL
,ReportParameters VARCHAR(500) NULL
,TimeStart DATETIME NOT NULL
,TimeEnd DATETIME NOT NULL
,Row_Count INT NOT NULL
)
Step 2
I created a stored procedure that I could have at the bottom of the stored procedures the reports are using.
CREATE PROCEDURE dbo.InsertReportUsage ( @ReportTypeID INT ,@ReportPath VARCHAR(255) ,@ReportName VARCHAR(255) ,@UserName NVARCHAR(255) ,@SprocName VARCHAR(255) ,@ReportParameters VARCHAR(500) ,@TimeStart DATETIME ,@TimeEnd DATETIME ,@Row_Count int ) AS BEGIN TRY SET NOCOUNT ON INSERT INTO ReportLog ( ReportTypeID ,ReportPath ,ReportName ,UserName ,SprocName ,ReportParameters ,TimeStart ,TimeEnd ,Row_Count ) VALUES ( @ReportTypeID ,@ReportPath ,@ReportName ,@UserName ,@SprocName ,@ReportParameters ,@TimeStart ,@TimeEnd ,@Row_Count ) END TRY BEGIN CATCH DECLARE @ErrorMessage AS nvarchar(3000) ,@ErrorSeverity AS int SET @ErrorMessage = ISNULL(DB_NAME(DB_ID()) + N'.' + SCHEMA_NAME(SCHEMA_ID()) + N'.' + OBJECT_NAME(@@PROCID, DB_ID()), N'SQL Object Name Not Available') + N': Error: ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + N' Line: ' + CONVERT(nvarchar(5), ERROR_LINE()) + N' - ' + ERROR_MESSAGE() SET @ErrorSeverity = ERROR_SEVERITY() RAISERROR(@ErrorMessage, @ErrorSeverity, 1) END CATCH SET NOCOUNT OFF GO
Step 3
I added some common fields at the top of the stored procedure for my report. Then, at the bottom I called the stored procedure that will record the report usage metrics.
Since I didn’t want to hard code the report path or the report name in the stored procedure, I passed those values from Excel.
I was also able to dynamically capture the stored procedure name, and the system user’s credentials.
Here is an example of a report that lists the names of employees.
CREATE PROCEDURE dbo.PersonReport ( @LastName varchar(100) ,@ReportPath VARCHAR(255) ,@ReportName VARCHAR(255) ) AS BEGIN TRY --SET NOCOUNT ON (Don't set) --------------Excel Log Block-------------- DECLARE @SprocName VARCHAR(255) = DB_NAME(DB_ID()) + '.' + SCHEMA_NAME(SCHEMA_ID()) + '.' + OBJECT_NAME(@@PROCID, DB_ID()) DECLARE @ReportParameters VARCHAR(8000) = 'LastName=' + @LastName DECLARE @TimeStart DATETIME = GETDATE() --------------Excel Log Block-------------- SELECT p.BusinessEntityID ,p.Title ,p.FirstName ,p.MiddleName ,p.LastName FROM Person.Person AS p WHERE p.LastName LIKE @LastName + '%'; --------------Excel Log Block-------------- EXEC ReportManagement.dbo.InsertReportUsage 1, @ReportPath, @ReportName, SUSER_NAME(), @SprocName ,@ReportParameters, @TimeStart, Get_date(), @@RowCount --------------Excel Log Block-------------- END TRY BEGIN CATCH DECLARE @ErrorMessage AS nvarchar(3000) ,@ErrorSeverity AS int SET @ErrorMessage = ISNULL(DB_NAME(DB_ID()) + N'.' + SCHEMA_NAME(SCHEMA_ID()) + N'.' + OBJECT_NAME(@@PROCID, DB_ID()), N'SQL Object Name Not Available') + N': Error: ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + N' Line: ' + CONVERT(nvarchar(5), ERROR_LINE()) + N' - ' + ERROR_MESSAGE() SET @ErrorSeverity = ERROR_SEVERITY() RAISERROR(@ErrorMessage, @ErrorSeverity, 1) END CATCH SET NOCOUNT OFF GO
Last Step
Finally, I created a stored procedure that joined the metrics from the ExecutionLog3 View that is provided in the ReportServer database for SSRS report usage metrics with the metrics I captured in the ReportLog table.
Now I can generate an overarching report for all reports in my environment.
Thanks for all the fish
Thanks go out to Catherine Wilhelmsen for hosting this month’s T-SQL Tuesday blog party. She is one of my favorite people to catch up with during PASS Summit. I hope you spend some time catching up with her on her blog.
