SQL Server Performance Forum – Threads Archive
Query Timer
Does anyone know of any very very accurate ways to measure the time a query takes? Currently I have a stored procedure that runs several hundred times a minute (sometimes a second). It takes milliseconds to respond but I’m struggling to measure how many. Currently at the start of the proc I store the datetime then at the end I deduct the the start time from the current time to give a duration. This is rubbish though because SQL rounds the datetime datatype 3/100 (3 milliseconds) of a second. So on a quick search the value I get back is always 13, or 16 (or sometimes zero). This also generates a very small amount of workload on the proc that I really don’t want. Ideally there is a tool out there that can measure the time the proc takes, maybe to nearest nanosecond. What woudl also be good is if it could seperate network time… or am I asking too much here! PS Client Statistics in QA are no good at these speeds. Any ideas appreciated. SimonQuery Execution plan is ideal tool to give the information and for further analysis refer tohttp://www.sql-server-performance.com/query_execution_plan_analysis.asp article. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
The execution plan does not show times, it shows the resources and methods. I want to do this for load testing, I will have this proc running upto 400 times simaltenously (with different parameters). That’s several thousand times a second. I can’t look at an execution plan for all of them! I need a tool that can graph out the response times like perf mon does, but it has to be very accurate – more so than SQL’s datetime datatype. I think this would have to be outside SQL because of the datetime rounding problem…
]]>