Multiple Longer Processes Can Be Hard to Monitor

Do you have some long import processes or a series of queries that perform calculations daily? Sometimes it can be hard to trace down which ones have been getting steadily slower over time. There is a very simple solution that will create a nice trending table of data for you.

---

What The Process Might Look Like

To capture start and end times for a series of procedures (in a very very simple way), I would recommend creating a table. For our purposes, we will use a data import example. In our example, we will be moving data into our database and then performing a series of calculations on the data to get it formatted/ready for end users.

CREATE PROCEDURE [dbo].[spi_DataImport]
/*
Purpose: Import data and process it for end users
Author: Jason Wittenauer
Date: 6/16/2011
Change Log:

*/
(
  @Date
)
AS
BEGIN

EXEC spi_ImportDataIntoStaging @Date

EXEC sps_CalculateReportsForExecutives @Date

EXEC sps_CalculateReportsForSupervisors @Date

END

Now, let's say that this entire process takes 45 minutes to run and it used to take 5 minutes to run. We will need to track down which procedure is causing the issue. While 3 procedures are not too many, imagine if it were 20.

---

Capturing Performance Made Simple

To capture the procedure times, you can create a table to record the start and end times of the processes.

CREATE TABLE ImportTimes(
  PKImportTimes INT Identity(1,1),
  Procedurename VARCHAR(50),
  StartTime DATETIME,
  EndTime DATETIME,
  ImportDate DATETIME
)

Now we can change our procedure to capture these times and quickly find out which procedure is taking too long to run.

CREATE PROCEDURE [dbo].[spi_DataImport]
/*
Purpose: Import data and process it for end users
Author: Jason Wittenauer
Date: 6/16/2011
Change Log:

*/
(
  @Date
)
AS
BEGIN

DECLARE @StartTime DATETIME

SELECT @StartTime = GETDATE()
EXEC spi_ImportDataIntoStaging @Date
INSERT INTO ImportTimes VALUE('spi_ImportDataIntoStaging', @StartTime, GETDATE(), @Date)

SELECT @StartTime = GETDATE()
EXEC sps_CalculateReportsForExecutives @Date
INSERT INTO ImportTimes VALUE('sps_CalculateReportsForExecutives', @StartTime, GETDATE(), @Date)

SELECT @StartTime = GETDATE()
EXEC sps_CalculateReportsForSupervisors @Date
INSERT INTO ImportTimes VALUE('sps_CalculateReportsForSupervisors', @StartTime, GETDATE(), @Date)

END

Whenever you need to review your import times, just query on your new table.

---