December 4, 2008 at 12:19 am
We'd like to add monitoring of stored procedures to audit/log the following:
- Stored procedure name
- Execution time
- Parameters used
- Date/time executed
Please would anyone let me know if there are any "clever" ways of doing this besides adding logging code to each stored procedure.
If adding code is the only answer is there any way to get the parameters via a system function when the stored procedure executes?
At the moment the only option we can see is to use the following:
/*======= Audit Params ====================*/
DECLARE @starttime datetime
DECLARE @name sysname
DECLARE @params nvarchar(max)
DECLARE @duration int
SET @starttime = getdate()
/*======= Audit Params ====================*/
and the insert into the tracking table at the end of the procedure:
/*======= Audit Params ====================*/
SET @name = 'usp_REPORT_Portfolio_IP_Liquidity_S'
SET @params = '@DataChannelID = ' + ISNULL(CAST(@DataChannelID as nvarchar(max)), 'NULL') + '|' + '@EffectiveDate = ' + ISNULL(CONVERT(nvarchar(30), @EffectiveDate, 121), 'NULL') + '|' + '@PortfolioClassificationCode = ' + ISNULL(CAST(@PortfolioClassificationCode as nvarchar(max)), 'NULL') + '|' + '@InstrumentClassificationCode = ' + ISNULL(CAST(@InstrumentClassificationCode as nvarchar(max)), 'NULL')
SET @duration = DATEDIFF(ms, @starttime, GETDATE())
EXECUTE SQLAuditing.dbo.InsertTrackProcedure @name, @params, @duration
/*======= Audit Params ====================*/
December 4, 2008 at 12:23 am
csales (12/4/2008)
We'd like to add monitoring of stored procedures to audit/log the following:- Stored procedure name
- Execution time
- Parameters used
- Date/time executed
Please would anyone let me know if there are any "clever" ways of doing this besides adding logging code to each stored procedure.
If adding code is the only answer is there any way to get the parameters via a system function when the stored procedure executes?
At the moment the only option we can see is to use the following:
/*======= Audit Params ====================*/
DECLARE @starttime datetime
DECLARE @name sysname
DECLARE @params nvarchar(max)
DECLARE @duration int
SET @starttime = getdate()
/*======= Audit Params ====================*/
and the insert into the tracking table at the end of the procedure:
/*======= Audit Params ====================*/
SET @name = 'usp_REPORT_Portfolio_IP_Liquidity_S'
SET @params = '@DataChannelID = ' + ISNULL(CAST(@DataChannelID as nvarchar(max)), 'NULL') + '|' + '@EffectiveDate = ' + ISNULL(CONVERT(nvarchar(30), @EffectiveDate, 121), 'NULL') + '|' + '@PortfolioClassificationCode = ' + ISNULL(CAST(@PortfolioClassificationCode as nvarchar(max)), 'NULL') + '|' + '@InstrumentClassificationCode = ' + ISNULL(CAST(@InstrumentClassificationCode as nvarchar(max)), 'NULL')
SET @duration = DATEDIFF(ms, @starttime, GETDATE())
EXECUTE SQLAuditing.dbo.InsertTrackProcedure @name, @params, @duration
/*======= Audit Params ====================*/
SQL Profiler
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
December 4, 2008 at 12:30 am
Sure - I know it can do be done using SQLProfiler - but whats the impact of having that running all the time in production?
December 4, 2008 at 12:36 am
csales (12/4/2008)
Sure - I know it can do be done using SQLProfiler - but whats the impact of having that running all the time in production?
Oh really,
One thing we can do but that can require aroung 2- 3 hours.
We can create a log table and define the logic to get the required information inside the sp(s).
For this we are required to script out all the stored procedure and by using any UI utility we can add our extra statements before
"Create Procedure"
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
December 4, 2008 at 12:52 am
Sorrry but I'm not sure I follow you? Can you provide more detail?
December 4, 2008 at 1:44 am
csales (12/4/2008)
Sorrry but I'm not sure I follow you? Can you provide more detail?
for example:
here is the sp
Create procedure procedurename
as
select col1, col2 from tablename
now to log this sp , make entry in the log table and write down that statement before and after the execution of the sp like
Create procedure procedurename
as
select col1, col2 from tablename
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
December 4, 2008 at 1:49 am
Is that any different from what I originally posted?
December 4, 2008 at 2:31 am
csales (12/4/2008)
Is that any different from what I originally posted?
Yes as i prefer to perform these task by the use of a function or another stored procedure, and the log table must be global for all the related databases
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
December 4, 2008 at 2:36 am
I agree - would prefer to have everything wrapped up in a function.
But the big question is how do you programmatically get to stored procedure parameters without hard coding them? Can you show me an example?
December 4, 2008 at 6:19 am
In addition to Profiler, if you want to get real time information on queries that are currently in cache, you can get aggregate performance information from sys.dm_exec_query_stats. You can get information about stuff as it is executing from sys.dm_exec_requests. Then you can combine that information with either sys.dm_exec_sql_text to get the TSQL or sys.dm_exec_query_plan to see the execution plan of the query.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply