July 11, 2011 at 6:17 am
Hi,
I capture IO about particular Query by using this script
--batch execution statistics
SELECT s2.dbid,
s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;
go
this output showing one of the query as below too much total_worker_time is 382893559.
select Drug From prescription_d d join prescription_h h on h.presc_no = d.presc_no join user_master um on um.login_id = h.modify_by where h.modify_by = '10026788' Group By Drug Having Count(*) > 10 Order By Drug
please tell me, I want to know this number is(382893559) Milli.seconds or seconds, Is it relay performance issues above query the way of written and both table having 5 lacks records.
rgds
ananda
July 12, 2011 at 12:16 pm
It may be nothing at all...
Try getting your average time by dividing the
total_worker_time / (execution_count *1.0)
I multiply by 1.0 to implicit cast to a float so you get more precise numbers.
total_worker_time is in milliseconds, btw. Divide it by 1000 to get the number in seconds.
Craig Outcalt
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply