To find any SQL server proc, in a specific database that is returning more than N rows

Question

To find any SQL server proc, in a specific database that is returning more than N rows

Sainath Kesavan

Valued Member

Points: 65
More actions
February 10, 2015 at 11:14 am

#304017

Hi,
Is there a way, maybe via SQL trace, to identify any procedure that returns more than N rows back to the caller?
For example I may have stored procedure(s) which when called for any set of parameters (or not) always returns 20 rows or more . The intent is to identify such procedures for a web application I am optimizing and drill down to see if the number of rows being returned is really required or not.
If not SQL Trace and some other option (not involving a third party tool) exists, that will do too.
Thank you for your advice and help,
Sainath

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you could query the dmv's and shred the xml of the actual execution plans and get the actual number of rows returned for anything still in cache.

after that, it gets deep into the details, you'd have to build a list of possible parameters for every proc, and generate an extiamted execution plan, that'd be tough without a lot of domain knowledge, and that assumes the procs return data.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Trace / SQLProfiler ( at least in 2014, cant test earlier ) has a RowCount column.

Create a serverside trace https://msdn.microsoft.com/en-us/library/cc293613.aspx

throw the results into a staging table and then filer where RowCount > X

I havent used this column in anger, but simple testing shows it gives the correct rowcount even when SET NOCOUNT ON is used in the procedure.

Clear Sky SQL
My Blog[/url]

Sainath Kesavan Valued Member Points: 65 More actions · Answer 3

Hi Dave,

Thank you. I am trying this right now on sql serve 2008 R2. I can see the Rowcounts option in profiler, which is great. However, when I run the proc via the web application, I see a number 127 being displayed in profiler whereas when I run the actual procedure, its only 48 rows.

Interestingly if I profile the same proc via query analyzer, there is a column called Integer Data which seems to return a value that matches the output rows from my proc.

I will keep investigating.

Thanks,

Sainath