none
Load estimated row count RRS feed

  • Question

  • Hi Experts,

    Am trying to find a way to view the estimated row count for an update statement inside of a stored procedure before actual execution. 

    Let say Sp_1 contains an Update statement but before its actual execution inside the stored procedure I wish to capture the amount of rows that may get change if the estimated row count is above some certain no raise an error or else allow the update statement to be executed. 

    Is there a way to achieve it.

    Thanks

    Priya

    Sunday, June 16, 2019 6:19 AM

Answers

  • I don't think this can be done with a reasonable amount of effort. It may not even be possible with an unreasonable amount of effort.

    It would be somewhat easier if the UPDATE statement had been generated in a client, as the client first could submit the statement with SET SHOWPLAN and capture the estimates that way.

    But this is more difficult in a stored procedure, the various SET SHOWPLAN commants produces result sets that goes to the client. You might think that you could do:

       CREATE TABLE #plans (x xml)
       INSERT #plans(x)
          EXEC('SET SHOWPLAN_XML ON
               SELECT * FROM Orders WHERE CustomerID = ''ALFKI''')

    But the error message is

       Msg 1067, Level 15, State 2, Line 13
       The SET SHOWPLAN statements must be the only statements in the batch.

    So you would have to set up a loopback connection, which requires that you use the CLR or possibly a Python script if you are on SQL 2017. Or, well, you could get it going with xp_cmdshell, if you write an SQL script which starts with a SET SHOWPLAN command followed by go and then the query. Then you would look up the plan in the cache. Keep in mind that this includes a lot of actions that required elevated permissions, so you would need to sign the procedure with a certiticate.

    There are still a few more things to consider:
    * I am not sure that generating an estimate plan puts something into the cache. May be it does, but I don't remember.

    * You need to take precautions, so that the query is not already in the cache already from a previous execution; you would need to add something to make the query text unique. (But this can be in a comment.)

    * And in the end, you are only looking at an estimate, and the estimate may be way off, so you may still let something through the gate that bogs the server entirely.

    Going back to what may be your original problem, I think you should have a look at Resource Governor.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, June 16, 2019 9:19 AM

All replies

  • You are trying to identify the estimated number of records and compare the actual number of records , this is something which I think cannot be done, may be you should not try. Estimated number can be obtained from cached plan, but that would be a very expensive operation for you. And it depends on lots of factors. So the suggestion would be fix the number of records that you want to process in a batch and control it in a programatically.  

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    Sunday, June 16, 2019 6:31 AM
  • I don't think this can be done with a reasonable amount of effort. It may not even be possible with an unreasonable amount of effort.

    It would be somewhat easier if the UPDATE statement had been generated in a client, as the client first could submit the statement with SET SHOWPLAN and capture the estimates that way.

    But this is more difficult in a stored procedure, the various SET SHOWPLAN commants produces result sets that goes to the client. You might think that you could do:

       CREATE TABLE #plans (x xml)
       INSERT #plans(x)
          EXEC('SET SHOWPLAN_XML ON
               SELECT * FROM Orders WHERE CustomerID = ''ALFKI''')

    But the error message is

       Msg 1067, Level 15, State 2, Line 13
       The SET SHOWPLAN statements must be the only statements in the batch.

    So you would have to set up a loopback connection, which requires that you use the CLR or possibly a Python script if you are on SQL 2017. Or, well, you could get it going with xp_cmdshell, if you write an SQL script which starts with a SET SHOWPLAN command followed by go and then the query. Then you would look up the plan in the cache. Keep in mind that this includes a lot of actions that required elevated permissions, so you would need to sign the procedure with a certiticate.

    There are still a few more things to consider:
    * I am not sure that generating an estimate plan puts something into the cache. May be it does, but I don't remember.

    * You need to take precautions, so that the query is not already in the cache already from a previous execution; you would need to add something to make the query text unique. (But this can be in a comment.)

    * And in the end, you are only looking at an estimate, and the estimate may be way off, so you may still let something through the gate that bogs the server entirely.

    Going back to what may be your original problem, I think you should have a look at Resource Governor.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, June 16, 2019 9:19 AM
  • Hi Experts,

    Am trying to find a way to view the estimated row count for an update statement inside of a stored procedure before actual execution. 

    Let say Sp_1 contains an Update statement but before its actual execution inside the stored procedure I wish to capture the amount of rows that may get change if the estimated row count is above some certain no raise an error or else allow the update statement to be executed. 

    Is there a way to achieve it.

    Thanks

    Priya

    Good day Priya,

    You can simply use a SELECT query before your UPDATE query in order to get the estimated number of rows which will be updated. Use the same filter in the SELECT as you use in the UPDATE and you should get a good estimated. In the SELECT you can use COUNT(*)

    * Notice that your isolation level and lock level will be the main parameter to impact how good this estimated is. For example if you use SELECT with (NOLOCK) then maybe someone changed the rows between your SELECT and your UPDATE.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Sunday, June 16, 2019 10:56 AM
    Moderator