Post reply

Wrong estimate when inserting in multiple sessions

  • October 1, 2018 at 12:06 pm

    #2007569

    Check here to see what you can learn:

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • October 2, 2018 at 6:59 am

    #2007667

    Thanks for the link but I know my isolation levels ;). The fact that the transactions run in snapshot is not relevant for the estimates. If you are argueing that the estimates are influenced by the isolation level, then please indicate this clearly. To my knowledge there is no such relation and I would be highly surprised. In read committed isolation level the estimated exec plan shows the same (wrong) estimate. 

    So the question is: why do uncommitted records from other transactions show up in the estimate of a select query without where clause?

  • October 2, 2018 at 7:32 am

    #2007682

    I'm not sure how you're running your test.  If I run the first session without the rollback, the second session doesn't run because of the blocking transaction.  When I rollback the first session, then the second session runs.  In both cases, the actual and estimated row counts are 5, not 10.

    Disclaimer... I did the test on a 2008 box, not 2016.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 2, 2018 at 7:39 am

    #2007689

    Jeff Moden - Tuesday, October 2, 2018 7:32 AM

    I'm not sure how you're running your test.  If I run the first session without the rollback, the second session doesn't run because of the blocking transaction.  When I rollback the first session, then the second session runs.  In both cases, the actual and estimated row counts are 5, not 10.

    Disclaimer... I did the test on a 2008 box, not 2016.

    Even if you are running in the default read committed isolation level, you should be able to see the estimated execution plan without running the query, right? You can also run the transactions in snapshot isolation. You need to view the estimates when more than 1 transaction has inserted records in the table without committing them.

  • October 2, 2018 at 8:11 am

    #2007697

    Bouke Bruinsma - Tuesday, October 2, 2018 7:39 AM

    Jeff Moden - Tuesday, October 2, 2018 7:32 AM

    I'm not sure how you're running your test.  If I run the first session without the rollback, the second session doesn't run because of the blocking transaction.  When I rollback the first session, then the second session runs.  In both cases, the actual and estimated row counts are 5, not 10.

    Disclaimer... I did the test on a 2008 box, not 2016.

    Even if you are running in the default read committed isolation level, you should be able to see the estimated execution plan without running the query, right? You can also run the transactions in snapshot isolation. You need to view the estimates when more than 1 transaction has inserted records in the table without committing them.

    That's what I did.  Even the estimated execution plan of the second session while the first session was uncommitted showed only 5 rows.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 2, 2018 at 8:27 am

    #2007708

    I'm not seeing it either, each one shows 5 rows.

    Maybe our test doesn't match your test???

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • October 2, 2018 at 8:31 am

    #2007711

    Jeff Moden - Tuesday, October 2, 2018 8:11 AM

    Bouke Bruinsma - Tuesday, October 2, 2018 7:39 AM

    Jeff Moden - Tuesday, October 2, 2018 7:32 AM

    I'm not sure how you're running your test.  If I run the first session without the rollback, the second session doesn't run because of the blocking transaction.  When I rollback the first session, then the second session runs.  In both cases, the actual and estimated row counts are 5, not 10.

    Disclaimer... I did the test on a 2008 box, not 2016.

    Even if you are running in the default read committed isolation level, you should be able to see the estimated execution plan without running the query, right? You can also run the transactions in snapshot isolation. You need to view the estimates when more than 1 transaction has inserted records in the table without committing them.

    That's what I did.  Even the estimated execution plan of the second session while the first session was uncommitted showed only 5 rows.

    Then it must be because you are on a 2008 box or because of plan re-usage. I have tested on multiple 2016 instances and was able to reproduce every time.
    Try running with a recompile hint (select * from test option(recompile)) or on a 2016 box.

  • October 2, 2018 at 8:32 am

    #2007712

    Bouke Bruinsma - Tuesday, October 2, 2018 6:59 AM

    Thanks for the link but I know my isolation levels ;). The fact that the transactions run in snapshot is not relevant for the estimates. If you are argueing that the estimates are influenced by the isolation level, then please indicate this clearly. To my knowledge there is no such relation and I would be highly surprised. In read committed isolation level the estimated exec plan shows the same (wrong) estimate. 

    So the question is: why do uncommitted records from other transactions show up in the estimate of a select query without where clause?

    I'm pretty sure all I provided was a link where there might be more information that is useful.   I didn't mean to imply that you don't know your isolation levels, or that snapshot isolation should have any bearing on the estimates you are getting.   If you are not specifying NOLOCK anywhere, then your estimates should indeed be just 5 rows, and I'd love to see an actual execution plan that shows otherwise....

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • October 2, 2018 at 8:41 am

    #2007717

    There must be a way for you guys to reproduce this. I have now done this on 4 different (but all 2016) installations. What I get is what you see below. I addedd the recompile hints. On the left I get the estimate of 5, on the right 10 in the estimated plan. Neither transactions have been committed. 

  • October 2, 2018 at 11:52 am

    #2007756

    Bouke Bruinsma - Tuesday, October 2, 2018 8:41 AM

    There must be a way for you guys to reproduce this. I have now done this on 4 different (but all 2016) installations. What I get is what you see below. I addedd the recompile hints. On the left I get the estimate of 5, on the right 10 in the estimated plan. Neither transactions have been committed. 

    The issue could be that no one understands you are generating this from just an estimated execution plan. They may be thinking it's just a matter of the difference in estimated rows and actual rows of an actual execution plan. It is reproducible if it's any consolation.
    What may be more important is what are the estimated rows and actual rows from the actual execution plan?

    Sue

  • October 2, 2018 at 1:03 pm

    #2007777

    Bouke Bruinsma - Tuesday, October 2, 2018 8:41 AM

    There must be a way for you guys to reproduce this. I have now done this on 4 different (but all 2016) installations. What I get is what you see below. I addedd the recompile hints. On the left I get the estimate of 5, on the right 10 in the estimated plan. Neither transactions have been committed. 

    I'll have to try it on a 2016 box, which I currently can't get to.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 3, 2018 at 2:06 am

    #2007825

    I ran the script several times my dev 2016 box and every time both estimated and actual plans show 5.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • October 3, 2018 at 2:42 pm

    #2007967

    Bouke Bruinsma - Tuesday, October 2, 2018 8:41 AM

    There must be a way for you guys to reproduce this. I have now done this on 4 different (but all 2016) installations. What I get is what you see below. I addedd the recompile hints. On the left I get the estimate of 5, on the right 10 in the estimated plan. Neither transactions have been committed. 

    I'm having a hard time believing that both sessions are actually running in Snapshot Isolation mode.   Or are you executing a single statement at a time ?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • October 4, 2018 at 1:53 am

    #2008001

    sgmunson - Wednesday, October 3, 2018 2:42 PM

    Bouke Bruinsma - Tuesday, October 2, 2018 8:41 AM

    There must be a way for you guys to reproduce this. I have now done this on 4 different (but all 2016) installations. What I get is what you see below. I addedd the recompile hints. On the left I get the estimate of 5, on the right 10 in the estimated plan. Neither transactions have been committed. 

    I'm having a hard time believing that both sessions are actually running in Snapshot Isolation mode.   Or are you executing a single statement at a time ?

    They are running both in snapshot isolation. Below is the actual execution plan from the 2nd session where it is clear that 10 records are estimated and only 5 actually returned.
    I first run the 1st session up until the insert. Then I run the second session up until the insert and then in the 2nd session the select statement.
    I'm running SQL Server 2016 SP2 CU1 (13.0.5149.0)

  • October 4, 2018 at 2:33 am

    #2008003

    Ran a few more tests on my 2016 dev box.

    First query without rollback
    Second query with rollback and actual plan

    WITH GO between SET and BEGIN TRAN
    Estimate 10 rows

    WITHOUT  GO between SET and BEGIN TRAN
    Estimate 5 rows

    🙁

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 15 posts - 1 through 15 (of 30 total)

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