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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy