August 22, 2013 at 10:06 am
Is there a query to find total number of logical reads/physical reads done when a given stored procedure is executed?
August 22, 2013 at 2:47 pm
SELECT ss.sum_execution_count
,t.TEXT
,ss.sum_total_elapsed_time
,ss.sum_total_worker_time
,ss.sum_total_logical_reads
,ss.sum_total_logical_writes
FROM (SELECT s.plan_handle
,SUM(s.execution_count) sum_execution_count
,SUM(s.total_elapsed_time) sum_total_elapsed_time
,SUM(s.total_worker_time) sum_total_worker_time
,SUM(s.total_logical_reads) sum_total_logical_reads
,SUM(s.total_logical_writes) sum_total_logical_writes
FROM sys.dm_exec_query_stats s
GROUP BY s.plan_handle
) AS ss
CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t
WHERE t.TEXT LIKE '%PROCEDURE NAME HEREt%'
ORDER BY sum_total_logical_reads DESC
August 22, 2013 at 3:12 pm
sql-lover (8/22/2013)
SELECT ss.sum_execution_count
,t.TEXT
,ss.sum_total_elapsed_time
,ss.sum_total_worker_time
,ss.sum_total_logical_reads
,ss.sum_total_logical_writes
FROM (SELECT s.plan_handle
,SUM(s.execution_count) sum_execution_count
,SUM(s.total_elapsed_time) sum_total_elapsed_time
,SUM(s.total_worker_time) sum_total_worker_time
,SUM(s.total_logical_reads) sum_total_logical_reads
,SUM(s.total_logical_writes) sum_total_logical_writes
FROM sys.dm_exec_query_stats s
GROUP BY s.plan_handle
) AS ss
CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t
WHERE t.TEXT LIKE '%PROCEDURE NAME HEREt%'
ORDER BY sum_total_logical_reads DESC
almost....how do i point this to a particular spid? Thanks for your help.
August 22, 2013 at 4:08 pm
If you want to collect this data for a specific execution, you need to save the data before execution into a table, and then after execution read the DMV again to compute the delta. A presumptions is that there are no other executions of the procedure at the same time.
Rather than using dm.sys_exec_query_stats, you can use dm.sys_exec_procedure_stats, so that you get values on procedure level instead rather than on query level.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 22, 2013 at 8:27 pm
Erland Sommarskog (8/22/2013)
If you want to collect this data for a specific execution, you need to save the data before execution into a table, and then after execution read the DMV again to compute the delta. A presumptions is that there are no other executions of the procedure at the same time.Rather than using dm.sys_exec_query_stats, you can use dm.sys_exec_procedure_stats, so that you get values on procedure level instead rather than on query level.
i wanted at procedure level....i can write one but thought someone might already had a script.
August 24, 2013 at 12:14 am
Run a server side trace , that will tell you exactly how many pages are being read during execution, but should be the same data collected by a DMV
August 24, 2013 at 3:14 am
How could I forget! The solution curious_sqldba is looking for is available on my web site. sp_sqltrace accepts a T-SQL batch as parameter. It sets up a trace filtered for your own spid, runs the batch, and the aggregates the data from the trace.
http://www.sommarskog.se/sqlutil/sqltrace.html.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply