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 |
|
Van
Constraint Violating Yak Guru
462 Posts |
 Posted - 2008-03-14 : 15:29:13
|
| I need to know the exact difference in these two queries. The results are similar but one says it returns info about queries that are being executed in batches.--This scenario provides information about CPU time, IO reads and writes, and number of executions for the top five queries by average CPU time.SELECT total_worker_time/execution_count AS [Avg CPU Time],total_physical_reads/execution_count AS [Avg Physical Reads], total_logical_writes/execution_count AS [Avg Logical Writes], execution_count,(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats ORDER BY [Avg CPU Time] DESC --This scenario provides statistical information about the queries that are being executed in batches.SELECT s2.dbid, s1.sql_handle, ( SELECT TOP 1 SUBSTRING(s2.text, statement_start_offset / 2, ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) 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 s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE ( s2.objectid is null ) ORDER BY total_worker_time desc |
|
|
|
|
|
|
|
Can someone explain the exact difference in these