Retrieve SQL Run Time Information

Within our company, one of the requirements we have is to log statistics about what happens within stored procedures / SSIS packages while they are executing for audit purposes. Examples of the required statistics are:

  • Process Name *1
  • Process GUID ID
  • Process Start Time
  • Process End Time
  • Process Duration
  • Source and Destination table names
  • Rows written successfully *2
  • Rows rejected *3
  • Row ID for record inserted into Audit table *4
Within SSIS retrieving the required audit values are easy enough as there are toolbox items such as the Row Count component one can use as well as other system variables that come stock with SSIS. Within a stored procedure a little more work is required. I particularly wanted to retrieve the items above marked with an asterisk from the executing stored procedure.

Process Name (*1)

To retrieve the name of the running stored procedure, one needs to use the built in function “OBJECT_NAME”. The exact syntax to use is:

 

SELECT OBJECT_NAME(@@PROCID).

The output of this query can be written as an output from the procedure, or written to a variable and used later on. Thanks to “Hunchback” for providing the answer on the MSDN forum.

Rows Written and Rejected (*2 & *3)

In order to retrieve the rows affected in particular table (whether by inserts, updates or deletes) one needs to use the T-SQL function @@ROWCOUNT directly after the statement executed that affected a table. So for instance, if I have three statements, one an insert, one an update, and one a delete (all on the same table), and I only want to log the rows that were inserted, then I would use the @@ROWCOUNT T-SQL function as follows:
DECLARE @RowCount bigint;
-- INSERT
INSERT INTO TableA (Column01, Column02, Column03)
VALUES ('10', 'Antibacterial Soap', '7.99');
SET @@RowCount = (SELECT @@ROWCOUNT);

-- UPDATE
UPDATE TableA
   SET TableA.Column02 = 'Sunlight Liquid'
WHERE TableA.Column02 = 'Sonlight Liquid'

DELETE FROM Table A
WHERE Column02 IS NULL
  AND Column03 < 0;
You will notice that I didn’t use the @@ROWCOUNT function after the Update or Delete statements, because every time a table is affected by a T-SQL operation the value stored by SQL in the @@ROWCOUNT variable is altered.

Row ID

We have created an Audit table that stores the run time information for our SSIS packages and SQL Stored Procedures. The way we use this table is that each process that runs inserts a record into the Audit table as its first step, runs the rest of the code, and then updates the Audit record with the correct run time information after the process has completed successfully. The same process can be run a few times (such as when it is run within a loop or a few times in sequence).
Because the same process can be run, it is important to update the correct values in the audit table. The way to do this is to retrieve the Row ID from the Audit Table (if it has one) after a record has been written to it by a particular process, and then to store this ID in a variable for later use. The safest way to retrieve this information is to use the T-SQL function Scope_Identity().
An example of it’s use can be seen below:
DECLARE @RowID BIGINT
CREATE TABLE Audit
(RowID    BIGINT IDENTITY(1,1)
,Column01 INT
,Column02 NVARCHAR(100)
,Column03 MONEY
)
INSERT INTO TableA (Column01, Column02, Column03)
VALUES ('10', 'Antibacterial Soap', '7.99');
SET @ETLPerformanceImportID = CAST(Scope_Identity() AS bigint)
NOTE: I cast the results from the function to a BIGINT because the RowID in the audit table was set as a BIGINT.

Conclusion

I hope this article assists you in getting a bit more out of your procedures than you used to.
God bless.
Colin
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s