Wrong estimate when inserting in multiple sessions
-
Consider the following:
1. Create table test with int column id
2. Session A: insert 5 records, don't commit
3. Session B: insert 5 records, don't commit
4. The estimated rowcount in the table scan operator of select * from test shows 10 in both sessions A and B.
Apparently, the estimate is the sum of all inserted but not necessarily committed records in this table. This does not sound logical to me. Why would not inserted data already appear in estimates? As long as the records are not committed, the estimate is wrong.
The origin of this problem is too complex to elaborate in detail but we have several concurrent processes that are all inserting data in this table and then each is running queries against it (in snapshot). The processes rollback in the end so the test table serves as a temporary table.
The fact that the inserts of other sessions weigh in in the estimate actually makes queries perform poorly. The solution we are thinking about is to have a dedicated table per session. But I still would like to understand why the estimate takes into account uncommitted transactions from other sessions.if object_id('Test') is not null drop table Test
GO
create table Test(id int NOT NULL)
GO
set transaction isolation level snapshot
GO
begin tran --also insert 5 records in different session, but don't commit
insert into test values (1),(2),(3),(4),(5)
select * from Test
rollback -
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) -
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 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) - Jeff Moden - Tuesday, October 2, 2018 7:32 AM
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.
- Bouke Bruinsma - Tuesday, October 2, 2018 7:39 AM
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) -
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/ - Jeff Moden - Tuesday, October 2, 2018 8:11 AM
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. - Bouke Bruinsma - Tuesday, October 2, 2018 6:59 AM
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) -
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.
- Bouke Bruinsma - Tuesday, October 2, 2018 8:41 AMThere 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
- Bouke Bruinsma - Tuesday, October 2, 2018 8:41 AMThere 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) -
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. - Bouke Bruinsma - Tuesday, October 2, 2018 8:41 AMThere 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) - sgmunson - Wednesday, October 3, 2018 2:42 PMBouke Bruinsma - Tuesday, October 2, 2018 8:41 AMThere 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)
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply