How to log execution time of a stored procedure ?
Posted on 2004-12-01
I want to log in a file the execution time of a stored procedure.
So my idea was to do something like
1. Create a sql file like
DECLARE @BeginProcDate datetime
DECLARE @duration datetime
set @duration = @BeginProcDate - getdate()
select CAST(DATEPART(ss, @duration) AS VARCHAR(2)) + '.' + CAST(DATEPART(ms, @duration) AS VARCHAR(3))
2. Use a bat file :
osql -S xxx -U sa -P sa -i "Exec sp.sql" -n -o "Output.txt"
Copy mainfile.txt + outpu.txt mainfile.txt
I was using the latest line to concatenate the result.
It is working fine but the problem is that in the output file, I also have the stored procedure result, ie 400 records, which make the file unreadable.
So I modify my sql file to add SET FMTONLY around my EXEC command.
Problem, my stored procedure is using a temp table and so it doesn't work anymore.
Does anyone have an idea ?