Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Marioi
Posting Yak Master
132 Posts |
 Posted - 2005-12-05 : 20:53:09
|
What is the best way to time and benchmark query execution and execution of sProcs and UDFs?In another thread (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58433) Kristen suggested the following code to time query execution:DECLARE @StartTime datetimeSET @StartTime = GetDate()-- Put code to "time" here:UPDATE MyTable SET MyColumn = 'FOO' WHERE MyColumn = 'BAR'--SELECT [Elapsed Time] = DateDiff(Seconds, @StartTime, GetDate()) rrb commented that "this doesn't take into account load on the server ...its a difficult problem ..."What are the issues? |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-12-05 : 22:13:07
|
| rrb is right, but first things first. No-load timing is important. A query can be seriously slow if the indexes are not properly defined and identified correctly by the query execution plan. You won't need a load on the database to find out either.A quickie way to time a proc ... just run it in Query Analyzer. The execution time (in seconds) appears on the status bar. If you have a missing index, and any serious amount of data, you'll know it without having to code Kristen's GETDATE()...Sam |
 |
|
|
|
|
|
Benchmarking query execution