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

  • 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

  • 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!

  • 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]

  • 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

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

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