Simple • Straightforward

So easy a Caveman
Programmer can do it.

›› Let's Get Started

Long Daily Processes

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.





Comments

Name:
Title:   
Commenting Currently Disabled


Alexavia
8/3/2011 10:53:01 PM



Ah yes, nciely put, everyone.
Many apperceive the cast Tag Heuer, which has replica watches uk some arising collectible examples. The added abnormal and colorful, the added absorption they get. This includes the Monaco model, although these models accept rolex replica already acquired abundant cachet to be on the top ancillary in agreement of amount if they accept accustomed able affliction and storage. Other brands with abeyant for beginning collectors are Ulysee-Nardin, Universal Geneve and Longines if in excellent condition, all of replica rolex which are featured in Haines book Vintage Wristwatches.