As I do a lot of Data Warehousing I am always concerned that the data warehousing process itself could possibly impact performance.

In order to track the various data warehousing procedures and insure that they do not harm performance I came up with the idea of having a table that each procedure would write to so I could track how long each script ran and then adjust the frequency of their execution as needed. To make things easier and more consistent I created a table in my ADMIN Database and two stored procedures for this purpose.

The first procedure inserts a record into the database and returns the record id that is created.
This id is then passed to the second stored procedure to update the record when the data warehousing is complete.

In a lot of my scripts you will see something like this a the beginning:
declare @id int
declare @ParamList nvarchar(128)
set @ParamList = '@dbName: ' + @dbName + '; @FileName: ' + @FileName
exec @id = admin.dbo.up_AddScriptHistory 'Procedure name', @dbName, @paramList, 'Reason'

Then I will do what ever the stored procedure is meant to do and then somewhere at the end you will see a line that looks something like this:

exec admin.dbo.up_UpdateScriptHistory @id, @@Error

Note 1: the @id is the returned record id that the second stored will be updating. Which has the advantage that if you have a long script you can nest the up_AddScriptHistory .. up_UpdateScriptHistory calls within one another. Just keep the @id parameter straight.

Note 2: if you do not use an ADMIN database or if it is named something other than ADMIN then you will need to change the exec statements to reflect your database name.

Note 3: if you decide not to use these then you can always just comment out or delete the lines in my future scripts.

Note 4: In the attached zip file you should find a script to create the ScriptHistory Table, the two stored procedures, and a script to report on the Script History table. Maybe one day I will actually create a SSRS Report for this but for now the script works good enough for me.

Note 5: you can get the code to create the table, both stored procedures, and a simple report script from this GPUG Share My Code post.

So, go there, download it, give it a try and feel free to let me know what you think.

Till later,