March 9, 2009 at 7:17 am
I have 7 stored procs, their execution time varies from 5sec to 40sec. Actually 1 stored proc calls the other 6 procs in it. So, is there any method to log these 7 stored proc timings?? these are being called from an application. I want to log their execution timings to a table....is it possible?? Pls advise me...
Thank you
March 9, 2009 at 7:29 am
Are you running it stand alone in testing? You could just add select getdate() before each call. Or you could add some inserts into a table before each call that adds timing information.
March 9, 2009 at 7:38 am
hmm yeah....may be I will try this....Thank you
March 9, 2009 at 9:04 am
This will not log the results to a table, but in a test environment you could manually determine the running time by using SSMS and running a series of tests
set statistics time on;
-- test using each of the 6 called procedures by properly invoking that single calling procedure
EXEC yourmaincallingproc,
set statistics time off;
This will produce time information for each test in the messages tab of SSMS:
SQL Server Execution Times:
CPU time = 5 ms, elapsed time = 2108 ms.
March 9, 2009 at 9:34 am
this is not for the test environment.....for production...testing everything was fine...but in production, somethimes they take long to finish execution...so may be I will log to a table with getdate()
March 9, 2009 at 9:45 am
You could also fire it with and have it show the actual execution plan, that might give you some insight as well.
March 9, 2009 at 9:55 am
Sure, you can use SQL Profiler to log the durations on the stored procs. Use RPC:Completed or SP:completed event and check the duration there.
thanks
SQL_EXPAT
March 10, 2009 at 10:33 pm
ssismaddy (3/9/2009)
I have 7 stored procs, their execution time varies from 5sec to 40sec. Actually 1 stored proc calls the other 6 procs in it. So, is there any method to log these 7 stored proc timings?? these are being called from an application. I want to log their execution timings to a table....is it possible?? Pls advise me...Thank you
Hi.
If you use SQL Agent to run a scheduled job for the top sp, you can ask the scheduler to create a log file. (See under STEP, Advanced).
In my "top" SP, I have these PRINT commands that will appear in the LOG file created by the scheduled job.
HTH
PRINT '----------- FINISHED 1.3: ETL_YYY: ' + ' ----------- @ ' + convert(varchar(19), getdate(),120) + ' --------------------------------------------'
EXEC dbo.usp_DW_AuditLogAdd 4, 1, 'ETL_XXX'
PRINT '----------- FINISHED 1: fmr ETL_Main : ' + ' ----------- @ ' + convert(varchar(19), getdate(),120) + ' --------------------------------------------'
PRINT '----------- START 2: ETL_OrderLineFiles : ' + ' ----------- @ ' + convert(varchar(19), getdate(),120) + ' --------------------------------------------'
March 10, 2009 at 10:40 pm
ssismaddy (3/9/2009)
I have 7 stored procs, --I want to log their execution timings to a table....is it possible?? Pls advise me...Thank you
To write to a table, you may create a sp that does logging into an audit table. This has a field called "TimeStmp" with a constraint - default of getdate().
Use the sp before/after each of the 6 "steps" to record timing and a description as
EXEC dbo.usp_DW_AuditLogAdd 1, 2, 'ETL_XXX'
CREATE PROCEDURE [dbo].[usp_DW_AuditLogAdd]
(
@i_intJobIDINT
,@i_intStepIDINT
,@i_varDescvarchar(30)
)
AS
------------------------------------------------------------------------BEGIN
INSERT INTO dbo.admin_JobAudit
(
JobID, StepID, Step
)
VALUES
(
@i_intJobID
,@i_intStepID
,@i_varDesc
)
END --proc
March 11, 2009 at 7:49 am
ssismaddy
So, is there any method to log these 7 stored proc timings??
I am going to assume that you are attempting to determine the long running instances of those multiple SPs and then attempt to improve that performance - in other words do some Performance Tuning. If indeed this is your ultimate goal may I suggest that you evaluate what Performance tuning tools are available in Books On Line - starting with
SQL Server 2005 Books Online (November 2008)
Tools for Performance Monitoring and Tuning
http://technet.microsoft.com/en-us/library/ms179428(SQL.90).aspx
and / or
SQL Server 2005 Books Online (November 2008)
Monitoring Events
http://msdn.microsoft.com/en-us/library/ms190378(SQL.90).aspx
plus
SQL Server 2005 Books Online (November 2008)
SQL Server 2005 Tools and Utilities Reference Overview
http://msdn.microsoft.com/en-us/library/ms173764(SQL.90).aspx
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply