Getting Statistics for stored procedure

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.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Getting Statistics for stored procedure

Author

Topic

michaeljc70
Starting Member

7 Posts

Posted - 2010-07-26 : 12:38:40

I am trying to get the total logical reads for a sproc. If I turn the stats on, there are way way too many individual SQL statements to add up he logical reads.

I started SQL Profiler and can see the sproc getting executed (I selected to monitor via SPID). I must be blind/clueless though as to where to see the total IO after the proc finishes and I stop the trace. What am I missing?

Kristen
Test

22859 Posts

Posted - 2010-07-26 : 12:51:57

I do



SET STATISTICS IO ON; SET STATISTICS TIME ON
GO

EXEC MySproc
GO

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO

but presumably that's what you are referring to as having too many individual SQL statements to then add up?

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-26 : 14:40:08

you can also use:


set showplan_all on
set showplan_xml on
{your query}
set showplan_all off
set showplan_xml off

and you can always use actual execution plan as well as estimated execution plan.

michaeljc70
Starting Member

7 Posts

Posted - 2010-07-27 : 10:43:03

Right. The sproc calls others and there are literally 100's of them to add up. I manipulated them in Excel, but it seems there must be an easier way.

quote:
Originally posted by Kristen

I do

SET STATISTICS IO ON; SET STATISTICS TIME ON
GO

EXEC MySproc
GO

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO

but presumably that's what you are referring to as having too many individual SQL statements to then add up?