Number of Rows Returned by Query

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 2005 Forums

Transact-SQL (2005)

Number of Rows Returned by Query

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-12-08 : 02:29:15

Hi Team,

I am trying to find out the number of rows returned by each query / SP. I am looking for a query that will list the result columns 1) Query Text 2) Number of Records returned from Actual Execution Plan 3) Number of Records returned from Estimated Execution Plan

Can we achieve this from Actual Execution Plan / Estimated Execution Plan? Can you please help me with such a query?

If execution plan is not the right place for this, is there any DMV for this?

Note: I am looking for the number of rows actually returned by the query; not the number of times SQL Server performed GetNext() ( In execution plan ActualRows counts the number of times GetNext() was called on a physical operator.)

EDIT: Actually, the intention is to list out the values for all queries in the system. @@RowCount will not help for that.

Thanks
Lijo Cheeran Joseph

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-08 : 02:54:22

If you just want the total rows, ignore the plan and use @@rowcount.

If you want estimated and actual rows for various query operators in the plan, you'll need to get very familiar with xquery and the xml form of the plan. It won't be a simple query.

--
Gail Shaw
SQL Server MVP

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-12-08 : 03:01:16

Actually, my intention is to list out the values for all queries in the system. I think @@RowCount will not help for that.

Could you please take some time and help me with such a query that uses plans?

Thanks
Lijo

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-08 : 05:13:57

No, sorry, that's likely to be hours of work to get right.

Getting the plan's also hard, the plans in cache don't have actual row counts (only estimated), so you'll have to get the plan from either profiler or SSMS. The XML is well formed and the schema is available. Read up on xquery and have a go.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources