September 3, 2008 at 8:01 am
Hi,
I have a stored procedure that I use to test the performance of my system. It's a pretty complicated query and does a good job of putting the server through it's paces.
Usually I execute the stored procedure once and note the execution time in my logs.
But I was wondering if there was a way where I could have the stored procedure execute 10 times, and then take the average of the 10 execution times, and I could use that in my logs.
Is there a way to do this?
Thanks!
September 3, 2008 at 8:37 am
Well, to get it to execute 10 times, just use this syntax:
EXEC myproc;
GO 10
As to how to gather the average of 10 executions... It depends on how you're capturing the performance metrics. I would probably run a trace to capture the information. Adding a script to start & stop a trace before and after your 10 executions, filtered to only capture your procedure should be pretty easy. Then you can load the trace output into a table and run a query to get the aggregated data.
----------------------------------------------------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
September 3, 2008 at 11:39 am
I have a freind by the name of Bill Wonder (MVP for 3 yrs) who set up SQLH2 in one of our servers. This one would collect traces from all the DB servers and process it. And what it did was to give the Least execution time, Average Execution time and the Max execution time for the stored proc.
He is developing a tool called SQL Clue. Maybe you can check it out. It has all those functionalities.
-Roy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply