How to look a single SQL Server query takes how much cpu time for completation

Question

How to look a single SQL Server query takes how much cpu time for completion

when i run the query via sql server management studio i want to see how much cpu time did this query consumed

it will be pretty low but i need to see for comparing different query loads

and it would be even greater if it is possible to run query 1000 times and see total consumed time

thank you

ok this is the full answer

set statistics time on

execute your query

set statistics time off

and after this you have to look messages window not results window

score 4 · Accepted Answer · answered Nov 15 '10 at 22:14

4

Before you run your query, execute this command in the same SSMS window.

set statistics time on

Note that in a multi-processor environment, the CPU time reported may actually exceed the elapsed time if any parallel processing occurs when executing the query.

answered Nov 15 '10 at 22:14

Joe Stefanelli

132,803
19
237
235

what would this do ? i runned the query but nothing at result window – Furkan Gözükara Nov 15 '10 at 22:42
@PokemonCraft: Look on the `Messages` tab. You should see output like: `SQL Server Execution Times: CPU time = 16 ms, elapsed time = 29 ms.` – Joe Stefanelli Nov 15 '10 at 22:55

score 2 · Answer 2 · answered Nov 15 '10 at 22:16

2

If you open SqlServer Profiler you can see the resources consumed by a query. If you need to run the query a 1000 times just do it directly in SSMS with a loop:

Set @counter = 1
While @counter < 1000

Begin

    --- Your query goes here

    Set @counter = @counter + 1

End

answered Nov 15 '10 at 22:16

Klaus Byskov Pedersen

117,245
29
183
222

i would rather to see at sql server managament studio instead of profiler is this possible ? – Furkan Gözükara Nov 15 '10 at 22:41

How to look a single SQL Server query takes how much cpu time for completation

2 Answers2