October 4, 2018 at 2:33 am
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.
October 4, 2018 at 6:36 am
I get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2018 at 7:33 am
sgmunson - Thursday, October 4, 2018 6:36 AMI get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.
I created the script as an abstracted example of what we see in a more complex situation. Multiple concurrent transactions are running in snapshot and inserting data in the same table and executing mutliple queries that select this table. The transactions run in snapshot to avoid locking of data and because they should operate independent of eachother. The standard situation is that the table has no records, those are only inserted temporarily during the transactions. The transactions roll back in the end because it's cheaper than deleting and committing.
We found this issue because we could not reproduce the slow queries if we re-ran one of these transactions. They were siginificantly faster than when observed in operation. Finally we were able to pinpoint the mis estimate as the cause. In fact, we could reproduce the slowness if we did insert data in the table in other transactions.
Besides the reproducability, I would be curious to know your opinion about the fact that uncommitted records weigh in in the estimate in snapshot. For a transaction running in read uncommitted isolation I can understand it. Then the actual rows returned would match the estimate. But in snapshot isolation it makes no sense. Even if the first transaction would commit before the second is finished, the second would still not see the data because of its snapshot character.
October 4, 2018 at 7:59 am
Bouke Bruinsma - Thursday, October 4, 2018 7:33 AMsgmunson - Thursday, October 4, 2018 6:36 AMI get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.I created the script as an abstracted example of what we see in a more complex situation. Multiple concurrent transactions are running in snapshot and inserting data in the same table and executing mutliple queries that select this table. The transactions run in snapshot to avoid locking of data and because they should operate independent of eachother. The standard situation is that the table has no records, those are only inserted temporarily during the transactions. The transactions roll back in the end because it's cheaper than deleting and committing.
We found this issue because we could not reproduce the slow queries if we re-ran one of these transactions. They were siginificantly faster than when observed in operation. Finally we were able to pinpoint the mis estimate as the cause. In fact, we could reproduce the slowness if we did insert data in the table in other transactions.Besides the reproducability, I would be curious to know your opinion about the fact that uncommitted records weigh in in the estimate in snapshot. For a transaction running in read uncommitted isolation I can understand it. Then the actual rows returned would match the estimate. But in snapshot isolation it makes no sense. Even if the first transaction would commit before the second is finished, the second would still not see the data because of its snapshot character.
Okay, I get the logic, but are you actually reproducing that behavior, or just simulating it? I have trouble with believing in the results of scripts that are run line by line. SSMS will consider them individual batches. I'm not convinced that running the insert separately from the begin tran will produce the result you are expecting.
I wish I had more time available to play around with this, but just don't at the moment. Are you able to try this using SQLCMD instead of SSMS ? I'd be very curious to see if the behavior differs at all.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2018 at 8:04 am
sgmunson - Thursday, October 4, 2018 7:59 AMBouke Bruinsma - Thursday, October 4, 2018 7:33 AMsgmunson - Thursday, October 4, 2018 6:36 AMI get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.I created the script as an abstracted example of what we see in a more complex situation. Multiple concurrent transactions are running in snapshot and inserting data in the same table and executing mutliple queries that select this table. The transactions run in snapshot to avoid locking of data and because they should operate independent of eachother. The standard situation is that the table has no records, those are only inserted temporarily during the transactions. The transactions roll back in the end because it's cheaper than deleting and committing.
We found this issue because we could not reproduce the slow queries if we re-ran one of these transactions. They were siginificantly faster than when observed in operation. Finally we were able to pinpoint the mis estimate as the cause. In fact, we could reproduce the slowness if we did insert data in the table in other transactions.Besides the reproducability, I would be curious to know your opinion about the fact that uncommitted records weigh in in the estimate in snapshot. For a transaction running in read uncommitted isolation I can understand it. Then the actual rows returned would match the estimate. But in snapshot isolation it makes no sense. Even if the first transaction would commit before the second is finished, the second would still not see the data because of its snapshot character.
Okay, I get the logic, but are you actually reproducing that behavior, or just simulating it? I have trouble with believing in the results of scripts that are run line by line. SSMS will consider them individual batches. I'm not convinced that running the insert separately from the begin tran will produce the result you are expecting.
I wish I had more time available to play around with this, but just don't at the moment. Are you able to try this using SQLCMD instead of SSMS ? I'd be very curious to see if the behavior differs at all.
When I tested it, I ran the script as a whole, not line by line
Far away is close at hand in the images of elsewhere.
Anon.
October 4, 2018 at 8:23 am
David Burrows - Thursday, October 4, 2018 8:04 AMsgmunson - Thursday, October 4, 2018 7:59 AMBouke Bruinsma - Thursday, October 4, 2018 7:33 AMsgmunson - Thursday, October 4, 2018 6:36 AMI get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.I created the script as an abstracted example of what we see in a more complex situation. Multiple concurrent transactions are running in snapshot and inserting data in the same table and executing mutliple queries that select this table. The transactions run in snapshot to avoid locking of data and because they should operate independent of eachother. The standard situation is that the table has no records, those are only inserted temporarily during the transactions. The transactions roll back in the end because it's cheaper than deleting and committing.
We found this issue because we could not reproduce the slow queries if we re-ran one of these transactions. They were siginificantly faster than when observed in operation. Finally we were able to pinpoint the mis estimate as the cause. In fact, we could reproduce the slowness if we did insert data in the table in other transactions.Besides the reproducability, I would be curious to know your opinion about the fact that uncommitted records weigh in in the estimate in snapshot. For a transaction running in read uncommitted isolation I can understand it. Then the actual rows returned would match the estimate. But in snapshot isolation it makes no sense. Even if the first transaction would commit before the second is finished, the second would still not see the data because of its snapshot character.
Okay, I get the logic, but are you actually reproducing that behavior, or just simulating it? I have trouble with believing in the results of scripts that are run line by line. SSMS will consider them individual batches. I'm not convinced that running the insert separately from the begin tran will produce the result you are expecting.
I wish I had more time available to play around with this, but just don't at the moment. Are you able to try this using SQLCMD instead of SSMS ? I'd be very curious to see if the behavior differs at all.When I tested it, I ran the script as a whole, not line by line
I suspected as much. A line by line execution could easily produce very different results because to the best of my knowledge, lines that you execute separately have no connection to other lines previously executed...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2018 at 10:40 am
sgmunson - Thursday, October 4, 2018 8:23 AMDavid Burrows - Thursday, October 4, 2018 8:04 AMsgmunson - Thursday, October 4, 2018 7:59 AMBouke Bruinsma - Thursday, October 4, 2018 7:33 AMsgmunson - Thursday, October 4, 2018 6:36 AMI get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.I created the script as an abstracted example of what we see in a more complex situation. Multiple concurrent transactions are running in snapshot and inserting data in the same table and executing mutliple queries that select this table. The transactions run in snapshot to avoid locking of data and because they should operate independent of eachother. The standard situation is that the table has no records, those are only inserted temporarily during the transactions. The transactions roll back in the end because it's cheaper than deleting and committing.
We found this issue because we could not reproduce the slow queries if we re-ran one of these transactions. They were siginificantly faster than when observed in operation. Finally we were able to pinpoint the mis estimate as the cause. In fact, we could reproduce the slowness if we did insert data in the table in other transactions.Besides the reproducability, I would be curious to know your opinion about the fact that uncommitted records weigh in in the estimate in snapshot. For a transaction running in read uncommitted isolation I can understand it. Then the actual rows returned would match the estimate. But in snapshot isolation it makes no sense. Even if the first transaction would commit before the second is finished, the second would still not see the data because of its snapshot character.
Okay, I get the logic, but are you actually reproducing that behavior, or just simulating it? I have trouble with believing in the results of scripts that are run line by line. SSMS will consider them individual batches. I'm not convinced that running the insert separately from the begin tran will produce the result you are expecting.
I wish I had more time available to play around with this, but just don't at the moment. Are you able to try this using SQLCMD instead of SSMS ? I'd be very curious to see if the behavior differs at all.When I tested it, I ran the script as a whole, not line by line
I suspected as much. A line by line execution could easily produce very different results because to the best of my knowledge, lines that you execute separately have no connection to other lines previously executed...
The first screenshot is a display of the estimated plan (not actual) and likely after executing the first part and leaving the transaction open to test what was going on. Not including the entire thing - no drop and recreating the table. So I think it's only picking up the previous execution from the same session when after that selecting the text to display the estimated plan. It's the estimated plan, not the actual.
Sue
October 4, 2018 at 11:37 am
sgmunson - Thursday, October 4, 2018 8:23 AMDavid Burrows - Thursday, October 4, 2018 8:04 AMsgmunson - Thursday, October 4, 2018 7:59 AMBouke Bruinsma - Thursday, October 4, 2018 7:33 AMsgmunson - Thursday, October 4, 2018 6:36 AMI get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.I created the script as an abstracted example of what we see in a more complex situation. Multiple concurrent transactions are running in snapshot and inserting data in the same table and executing mutliple queries that select this table. The transactions run in snapshot to avoid locking of data and because they should operate independent of eachother. The standard situation is that the table has no records, those are only inserted temporarily during the transactions. The transactions roll back in the end because it's cheaper than deleting and committing.
We found this issue because we could not reproduce the slow queries if we re-ran one of these transactions. They were siginificantly faster than when observed in operation. Finally we were able to pinpoint the mis estimate as the cause. In fact, we could reproduce the slowness if we did insert data in the table in other transactions.Besides the reproducability, I would be curious to know your opinion about the fact that uncommitted records weigh in in the estimate in snapshot. For a transaction running in read uncommitted isolation I can understand it. Then the actual rows returned would match the estimate. But in snapshot isolation it makes no sense. Even if the first transaction would commit before the second is finished, the second would still not see the data because of its snapshot character.
Okay, I get the logic, but are you actually reproducing that behavior, or just simulating it? I have trouble with believing in the results of scripts that are run line by line. SSMS will consider them individual batches. I'm not convinced that running the insert separately from the begin tran will produce the result you are expecting.
I wish I had more time available to play around with this, but just don't at the moment. Are you able to try this using SQLCMD instead of SSMS ? I'd be very curious to see if the behavior differs at all.When I tested it, I ran the script as a whole, not line by line
I suspected as much. A line by line execution could easily produce very different results because to the best of my knowledge, lines that you execute separately have no connection to other lines previously executed...
The first screenshot is a display of the estimated plan (not actual) and likely after executing the first part and leaving the transaction open to test what was going on. Not including the entire thing - no drop and recreating the table. So I think it's only picking up the previous execution from the same session when after that selecting the text to display the estimated plan. It's the estimated plan, not the actual.
Sue
Sue,
Just to be clear, I was responding to David Burrows, who was going about the task they way I would have. What you mentioned is part of why I was having trouble believing that what I was seeing was entirely real. I'm not convinced that a transaction left open necessarily ever commits, as a separate batch run of an insert that is not directly a part of the tran open batch doesn't seem likely to me to end up as part of anything but an implicit transaction of its own. I may be wrong, but I need better evidence. I'm hearing others test using the full scripts, and not seeing the problem. I have a hard time buying that the line by line run that Bouke was doing was going to act the same. Again, maybe I'm right, maybe not. The evidence presented suggests I might be.... or that I'm misinterpreting what I'm seeing.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2018 at 12:22 pm
Sue_H - Thursday, October 4, 2018 10:40 AMsgmunson - Thursday, October 4, 2018 8:23 AMDavid Burrows - Thursday, October 4, 2018 8:04 AMsgmunson - Thursday, October 4, 2018 7:59 AMBouke Bruinsma - Thursday, October 4, 2018 7:33 AMsgmunson - Thursday, October 4, 2018 6:36 AMI get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.I created the script as an abstracted example of what we see in a more complex situation. Multiple concurrent transactions are running in snapshot and inserting data in the same table and executing mutliple queries that select this table. The transactions run in snapshot to avoid locking of data and because they should operate independent of eachother. The standard situation is that the table has no records, those are only inserted temporarily during the transactions. The transactions roll back in the end because it's cheaper than deleting and committing.
We found this issue because we could not reproduce the slow queries if we re-ran one of these transactions. They were siginificantly faster than when observed in operation. Finally we were able to pinpoint the mis estimate as the cause. In fact, we could reproduce the slowness if we did insert data in the table in other transactions.Besides the reproducability, I would be curious to know your opinion about the fact that uncommitted records weigh in in the estimate in snapshot. For a transaction running in read uncommitted isolation I can understand it. Then the actual rows returned would match the estimate. But in snapshot isolation it makes no sense. Even if the first transaction would commit before the second is finished, the second would still not see the data because of its snapshot character.
Okay, I get the logic, but are you actually reproducing that behavior, or just simulating it? I have trouble with believing in the results of scripts that are run line by line. SSMS will consider them individual batches. I'm not convinced that running the insert separately from the begin tran will produce the result you are expecting.
I wish I had more time available to play around with this, but just don't at the moment. Are you able to try this using SQLCMD instead of SSMS ? I'd be very curious to see if the behavior differs at all.When I tested it, I ran the script as a whole, not line by line
I suspected as much. A line by line execution could easily produce very different results because to the best of my knowledge, lines that you execute separately have no connection to other lines previously executed...
The first screenshot is a display of the estimated plan (not actual) and likely after executing the first part and leaving the transaction open to test what was going on. Not including the entire thing - no drop and recreating the table. So I think it's only picking up the previous execution from the same session when after that selecting the text to display the estimated plan. It's the estimated plan, not the actual.
Sue
Sue,
Just to be clear, I was responding to David Burrows, who was going about the task they way I would have. What you mentioned is part of why I was having trouble believing that what I was seeing was entirely real. I'm not convinced that a transaction left open necessarily ever commits, as a separate batch run of an insert that is not directly a part of the tran open batch doesn't seem likely to me to end up as part of anything but an implicit transaction of its own. I may be wrong, but I need better evidence. I'm hearing others test using the full scripts, and not seeing the problem. I have a hard time buying that the line by line run that Bouke was doing was going to act the same. Again, maybe I'm right, maybe not. The evidence presented suggests I might be.... or that I'm misinterpreting what I'm seeing.
Yes...I realized the post you were responding too. I'm not saying its correct to do, I was just trying to explain how the poster would get the results.The estimated execution plan (estimated) does show uncommitted transactions but the poster would be executing part of the the posted script for it to work with getting the result they got. Just play with this part: 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
You can't run the entire script in two different sessions and leave the transaction open - one would just hang. I think the create and drop table thing is getting in the way of what they were trying to explain about the issue they are seeing. I could repeat the issue they were seeing but not with running the drop and create. If you leave that out, that is how I could see what was reported and pretty sure it's the only way to get those results.
Just create the table and leave that out of it. Then open two windows and use the snippet I posted. Then highlight the select * from Test and show estimated execution plan. Go to the other window and run the snippet a couple of times. Go over to the other window, highlight the select statement, show estimated execution plan.
Sue
October 4, 2018 at 12:47 pm
sgmunson - Thursday, October 4, 2018 11:37 AMSue_H - Thursday, October 4, 2018 10:40 AMsgmunson - Thursday, October 4, 2018 8:23 AMDavid Burrows - Thursday, October 4, 2018 8:04 AMsgmunson - Thursday, October 4, 2018 7:59 AMBouke Bruinsma - Thursday, October 4, 2018 7:33 AMsgmunson - Thursday, October 4, 2018 6:36 AMI get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.I created the script as an abstracted example of what we see in a more complex situation. Multiple concurrent transactions are running in snapshot and inserting data in the same table and executing mutliple queries that select this table. The transactions run in snapshot to avoid locking of data and because they should operate independent of eachother. The standard situation is that the table has no records, those are only inserted temporarily during the transactions. The transactions roll back in the end because it's cheaper than deleting and committing.
We found this issue because we could not reproduce the slow queries if we re-ran one of these transactions. They were siginificantly faster than when observed in operation. Finally we were able to pinpoint the mis estimate as the cause. In fact, we could reproduce the slowness if we did insert data in the table in other transactions.Besides the reproducability, I would be curious to know your opinion about the fact that uncommitted records weigh in in the estimate in snapshot. For a transaction running in read uncommitted isolation I can understand it. Then the actual rows returned would match the estimate. But in snapshot isolation it makes no sense. Even if the first transaction would commit before the second is finished, the second would still not see the data because of its snapshot character.
Okay, I get the logic, but are you actually reproducing that behavior, or just simulating it? I have trouble with believing in the results of scripts that are run line by line. SSMS will consider them individual batches. I'm not convinced that running the insert separately from the begin tran will produce the result you are expecting.
I wish I had more time available to play around with this, but just don't at the moment. Are you able to try this using SQLCMD instead of SSMS ? I'd be very curious to see if the behavior differs at all.When I tested it, I ran the script as a whole, not line by line
I suspected as much. A line by line execution could easily produce very different results because to the best of my knowledge, lines that you execute separately have no connection to other lines previously executed...
The first screenshot is a display of the estimated plan (not actual) and likely after executing the first part and leaving the transaction open to test what was going on. Not including the entire thing - no drop and recreating the table. So I think it's only picking up the previous execution from the same session when after that selecting the text to display the estimated plan. It's the estimated plan, not the actual.
Sue
Sue,
Just to be clear, I was responding to David Burrows, who was going about the task they way I would have. What you mentioned is part of why I was having trouble believing that what I was seeing was entirely real. I'm not convinced that a transaction left open necessarily ever commits, as a separate batch run of an insert that is not directly a part of the tran open batch doesn't seem likely to me to end up as part of anything but an implicit transaction of its own. I may be wrong, but I need better evidence. I'm hearing others test using the full scripts, and not seeing the problem. I have a hard time buying that the line by line run that Bouke was doing was going to act the same. Again, maybe I'm right, maybe not. The evidence presented suggests I might be.... or that I'm misinterpreting what I'm seeing.
Yes...I realized the post you were responding too. I'm not saying its correct to do, I was just trying to explain how the poster would get the results.The estimated execution plan (estimated) does show uncommitted transactions but the poster would be executing part of the the posted script for it to work with getting the result they got. Just play with this part: 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
You can't run the entire script in two different sessions and leave the transaction open - one would just hang. I think the create and drop table thing is getting in the way of what they were trying to explain about the issue they are seeing. I could repeat the issue they were seeing but not with running the drop and create. If you leave that out, that is how I could see what was reported and pretty sure it's the only way to get those results.
Just create the table and leave that out of it. Then open two windows and use the snippet I posted. Then highlight the select * from Test and show estimated execution plan. Go to the other window and run the snippet a couple of times. Go over to the other window, highlight the select statement, show estimated execution plan.
Sue
Yeah, that's what I was thinking.... I'm pretty sure the OP's testing methodology is somewhat flawed. I can't imagine doing a test without a full script getting run, including any commits and/or rollbacks. One can always use WAITFOR DELAY to script in an intentional time delay to help coordinate a time difference.
I'm still curious, however, as to whether or not running the OP's test methodology, however flawed, would behave any differently.... If you get a chance to try it, I'd be very interested in knowing the results... Thanks, Sue!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2018 at 1:39 pm
Sue_H - Thursday, October 4, 2018 12:22 PMsgmunson - Thursday, October 4, 2018 11:37 AMSue_H - Thursday, October 4, 2018 10:40 AMsgmunson - Thursday, October 4, 2018 8:23 AMDavid Burrows - Thursday, October 4, 2018 8:04 AMsgmunson - Thursday, October 4, 2018 7:59 AMBouke Bruinsma - Thursday, October 4, 2018 7:33 AMsgmunson - Thursday, October 4, 2018 6:36 AMI get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.I created the script as an abstracted example of what we see in a more complex situation. Multiple concurrent transactions are running in snapshot and inserting data in the same table and executing mutliple queries that select this table. The transactions run in snapshot to avoid locking of data and because they should operate independent of eachother. The standard situation is that the table has no records, those are only inserted temporarily during the transactions. The transactions roll back in the end because it's cheaper than deleting and committing.
We found this issue because we could not reproduce the slow queries if we re-ran one of these transactions. They were siginificantly faster than when observed in operation. Finally we were able to pinpoint the mis estimate as the cause. In fact, we could reproduce the slowness if we did insert data in the table in other transactions.Besides the reproducability, I would be curious to know your opinion about the fact that uncommitted records weigh in in the estimate in snapshot. For a transaction running in read uncommitted isolation I can understand it. Then the actual rows returned would match the estimate. But in snapshot isolation it makes no sense. Even if the first transaction would commit before the second is finished, the second would still not see the data because of its snapshot character.
Okay, I get the logic, but are you actually reproducing that behavior, or just simulating it? I have trouble with believing in the results of scripts that are run line by line. SSMS will consider them individual batches. I'm not convinced that running the insert separately from the begin tran will produce the result you are expecting.
I wish I had more time available to play around with this, but just don't at the moment. Are you able to try this using SQLCMD instead of SSMS ? I'd be very curious to see if the behavior differs at all.When I tested it, I ran the script as a whole, not line by line
I suspected as much. A line by line execution could easily produce very different results because to the best of my knowledge, lines that you execute separately have no connection to other lines previously executed...
The first screenshot is a display of the estimated plan (not actual) and likely after executing the first part and leaving the transaction open to test what was going on. Not including the entire thing - no drop and recreating the table. So I think it's only picking up the previous execution from the same session when after that selecting the text to display the estimated plan. It's the estimated plan, not the actual.
Sue
Sue,
Just to be clear, I was responding to David Burrows, who was going about the task they way I would have. What you mentioned is part of why I was having trouble believing that what I was seeing was entirely real. I'm not convinced that a transaction left open necessarily ever commits, as a separate batch run of an insert that is not directly a part of the tran open batch doesn't seem likely to me to end up as part of anything but an implicit transaction of its own. I may be wrong, but I need better evidence. I'm hearing others test using the full scripts, and not seeing the problem. I have a hard time buying that the line by line run that Bouke was doing was going to act the same. Again, maybe I'm right, maybe not. The evidence presented suggests I might be.... or that I'm misinterpreting what I'm seeing.
Yes...I realized the post you were responding too. I'm not saying its correct to do, I was just trying to explain how the poster would get the results.The estimated execution plan (estimated) does show uncommitted transactions but the poster would be executing part of the the posted script for it to work with getting the result they got. Just play with this part:
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 TestYou can't run the entire script in two different sessions and leave the transaction open - one would just hang. I think the create and drop table thing is getting in the way of what they were trying to explain about the issue they are seeing. I could repeat the issue they were seeing but not with running the drop and create. If you leave that out, that is how I could see what was reported and pretty sure it's the only way to get those results.
Just create the table and leave that out of it. Then open two windows and use the snippet I posted. Then highlight the select * from Test and show estimated execution plan. Go to the other window and run the snippet a couple of times. Go over to the other window, highlight the select statement, show estimated execution plan.Sue
Yeah, that's what I was thinking.... I'm pretty sure the OP's testing methodology is somewhat flawed. I can't imagine doing a test without a full script getting run, including any commits and/or rollbacks. One can always use WAITFOR DELAY to script in an intentional time delay to help coordinate a time difference.
I'm still curious, however, as to whether or not running the OP's test methodology, however flawed, would behave any differently.... If you get a chance to try it, I'd be very interested in knowing the results... Thanks, Sue!
I have some concerns about the methodology but you really can't test the scripts and do as posted - it will just be blocked and hang. So no one would be able to test it with the two sessions per the instructions and that was posted by Jeff earlier. I was just trying to figure out how the poster got the results they did and what they were seeing. I would guess that others who reported running it successfully included the rollback - which isn't what the poster meant. He was asking about the uncommitted transaction showing up and was trying to emulate that.
So I get the reason the poster did it that way though - they were looking into the estimated and actual row counts differences and wanted to test if the differences were due to uncommitted transactions from other sessions affecting the estimate and actual row counts. Those aren't the actually pieces of code causing the issues though so that also impacts things as the real table could have 20 billion rows and who knows what the inserts are. I'm guessing its comparing apples to a bicycle chains.
Sue
October 4, 2018 at 3:08 pm
sgmunson - Thursday, October 4, 2018 12:47 PMSue_H - Thursday, October 4, 2018 12:22 PMsgmunson - Thursday, October 4, 2018 11:37 AMSue_H - Thursday, October 4, 2018 10:40 AMsgmunson - Thursday, October 4, 2018 8:23 AMDavid Burrows - Thursday, October 4, 2018 8:04 AMsgmunson - Thursday, October 4, 2018 7:59 AMBouke Bruinsma - Thursday, October 4, 2018 7:33 AMsgmunson - Thursday, October 4, 2018 6:36 AMI get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.I created the script as an abstracted example of what we see in a more complex situation. Multiple concurrent transactions are running in snapshot and inserting data in the same table and executing mutliple queries that select this table. The transactions run in snapshot to avoid locking of data and because they should operate independent of eachother. The standard situation is that the table has no records, those are only inserted temporarily during the transactions. The transactions roll back in the end because it's cheaper than deleting and committing.
We found this issue because we could not reproduce the slow queries if we re-ran one of these transactions. They were siginificantly faster than when observed in operation. Finally we were able to pinpoint the mis estimate as the cause. In fact, we could reproduce the slowness if we did insert data in the table in other transactions.Besides the reproducability, I would be curious to know your opinion about the fact that uncommitted records weigh in in the estimate in snapshot. For a transaction running in read uncommitted isolation I can understand it. Then the actual rows returned would match the estimate. But in snapshot isolation it makes no sense. Even if the first transaction would commit before the second is finished, the second would still not see the data because of its snapshot character.
Okay, I get the logic, but are you actually reproducing that behavior, or just simulating it? I have trouble with believing in the results of scripts that are run line by line. SSMS will consider them individual batches. I'm not convinced that running the insert separately from the begin tran will produce the result you are expecting.
I wish I had more time available to play around with this, but just don't at the moment. Are you able to try this using SQLCMD instead of SSMS ? I'd be very curious to see if the behavior differs at all.When I tested it, I ran the script as a whole, not line by line
I suspected as much. A line by line execution could easily produce very different results because to the best of my knowledge, lines that you execute separately have no connection to other lines previously executed...
The first screenshot is a display of the estimated plan (not actual) and likely after executing the first part and leaving the transaction open to test what was going on. Not including the entire thing - no drop and recreating the table. So I think it's only picking up the previous execution from the same session when after that selecting the text to display the estimated plan. It's the estimated plan, not the actual.
Sue
Sue,
Just to be clear, I was responding to David Burrows, who was going about the task they way I would have. What you mentioned is part of why I was having trouble believing that what I was seeing was entirely real. I'm not convinced that a transaction left open necessarily ever commits, as a separate batch run of an insert that is not directly a part of the tran open batch doesn't seem likely to me to end up as part of anything but an implicit transaction of its own. I may be wrong, but I need better evidence. I'm hearing others test using the full scripts, and not seeing the problem. I have a hard time buying that the line by line run that Bouke was doing was going to act the same. Again, maybe I'm right, maybe not. The evidence presented suggests I might be.... or that I'm misinterpreting what I'm seeing.
Yes...I realized the post you were responding too. I'm not saying its correct to do, I was just trying to explain how the poster would get the results.The estimated execution plan (estimated) does show uncommitted transactions but the poster would be executing part of the the posted script for it to work with getting the result they got. Just play with this part:
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 TestYou can't run the entire script in two different sessions and leave the transaction open - one would just hang. I think the create and drop table thing is getting in the way of what they were trying to explain about the issue they are seeing. I could repeat the issue they were seeing but not with running the drop and create. If you leave that out, that is how I could see what was reported and pretty sure it's the only way to get those results.
Just create the table and leave that out of it. Then open two windows and use the snippet I posted. Then highlight the select * from Test and show estimated execution plan. Go to the other window and run the snippet a couple of times. Go over to the other window, highlight the select statement, show estimated execution plan.Sue
Yeah, that's what I was thinking.... I'm pretty sure the OP's testing methodology is somewhat flawed. I can't imagine doing a test without a full script getting run, including any commits and/or rollbacks. One can always use WAITFOR DELAY to script in an intentional time delay to help coordinate a time difference.
I'm still curious, however, as to whether or not running the OP's test methodology, however flawed, would behave any differently.... If you get a chance to try it, I'd be very interested in knowing the results... Thanks, Sue!
I have some concerns about the methodology but you really can't test the scripts and do as posted - it will just be blocked and hang. So no one would be able to test it with the two sessions per the instructions and that was posted by Jeff earlier. I was just trying to figure out how the poster got the results they did and what they were seeing. I would guess that others who reported running it successfully included the rollback - which isn't what the poster meant. He was asking about the uncommitted transaction showing up and was trying to emulate that.
So I get the reason the poster did it that way though - they were looking into the estimated and actual row counts differences and wanted to test if the differences were due to uncommitted transactions from other sessions affecting the estimate and actual row counts. Those aren't the actually pieces of code causing the issues though so that also impacts things as the real table could have 20 billion rows and who knows what the inserts are. I'm guessing its comparing apples to a bicycle chains.
Sue
Yeah, that's been my perspective from nearly the get-go.... Interesting topic though... Still gotta wonder if SQLCMD could reproduce it, even the same way the poster did...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 5, 2018 at 1:02 am
Sue_H - Thursday, October 4, 2018 1:39 PMsgmunson - Thursday, October 4, 2018 12:47 PMSue_H - Thursday, October 4, 2018 12:22 PMsgmunson - Thursday, October 4, 2018 11:37 AMSue_H - Thursday, October 4, 2018 10:40 AMsgmunson - Thursday, October 4, 2018 8:23 AMDavid Burrows - Thursday, October 4, 2018 8:04 AMsgmunson - Thursday, October 4, 2018 7:59 AMBouke Bruinsma - Thursday, October 4, 2018 7:33 AMsgmunson - Thursday, October 4, 2018 6:36 AMI get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.I created the script as an abstracted example of what we see in a more complex situation. Multiple concurrent transactions are running in snapshot and inserting data in the same table and executing mutliple queries that select this table. The transactions run in snapshot to avoid locking of data and because they should operate independent of eachother. The standard situation is that the table has no records, those are only inserted temporarily during the transactions. The transactions roll back in the end because it's cheaper than deleting and committing.
We found this issue because we could not reproduce the slow queries if we re-ran one of these transactions. They were siginificantly faster than when observed in operation. Finally we were able to pinpoint the mis estimate as the cause. In fact, we could reproduce the slowness if we did insert data in the table in other transactions.Besides the reproducability, I would be curious to know your opinion about the fact that uncommitted records weigh in in the estimate in snapshot. For a transaction running in read uncommitted isolation I can understand it. Then the actual rows returned would match the estimate. But in snapshot isolation it makes no sense. Even if the first transaction would commit before the second is finished, the second would still not see the data because of its snapshot character.
Okay, I get the logic, but are you actually reproducing that behavior, or just simulating it? I have trouble with believing in the results of scripts that are run line by line. SSMS will consider them individual batches. I'm not convinced that running the insert separately from the begin tran will produce the result you are expecting.
I wish I had more time available to play around with this, but just don't at the moment. Are you able to try this using SQLCMD instead of SSMS ? I'd be very curious to see if the behavior differs at all.When I tested it, I ran the script as a whole, not line by line
I suspected as much. A line by line execution could easily produce very different results because to the best of my knowledge, lines that you execute separately have no connection to other lines previously executed...
The first screenshot is a display of the estimated plan (not actual) and likely after executing the first part and leaving the transaction open to test what was going on. Not including the entire thing - no drop and recreating the table. So I think it's only picking up the previous execution from the same session when after that selecting the text to display the estimated plan. It's the estimated plan, not the actual.
Sue
Sue,
Just to be clear, I was responding to David Burrows, who was going about the task they way I would have. What you mentioned is part of why I was having trouble believing that what I was seeing was entirely real. I'm not convinced that a transaction left open necessarily ever commits, as a separate batch run of an insert that is not directly a part of the tran open batch doesn't seem likely to me to end up as part of anything but an implicit transaction of its own. I may be wrong, but I need better evidence. I'm hearing others test using the full scripts, and not seeing the problem. I have a hard time buying that the line by line run that Bouke was doing was going to act the same. Again, maybe I'm right, maybe not. The evidence presented suggests I might be.... or that I'm misinterpreting what I'm seeing.
Yes...I realized the post you were responding too. I'm not saying its correct to do, I was just trying to explain how the poster would get the results.The estimated execution plan (estimated) does show uncommitted transactions but the poster would be executing part of the the posted script for it to work with getting the result they got. Just play with this part:
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 TestYou can't run the entire script in two different sessions and leave the transaction open - one would just hang. I think the create and drop table thing is getting in the way of what they were trying to explain about the issue they are seeing. I could repeat the issue they were seeing but not with running the drop and create. If you leave that out, that is how I could see what was reported and pretty sure it's the only way to get those results.
Just create the table and leave that out of it. Then open two windows and use the snippet I posted. Then highlight the select * from Test and show estimated execution plan. Go to the other window and run the snippet a couple of times. Go over to the other window, highlight the select statement, show estimated execution plan.Sue
Yeah, that's what I was thinking.... I'm pretty sure the OP's testing methodology is somewhat flawed. I can't imagine doing a test without a full script getting run, including any commits and/or rollbacks. One can always use WAITFOR DELAY to script in an intentional time delay to help coordinate a time difference.
I'm still curious, however, as to whether or not running the OP's test methodology, however flawed, would behave any differently.... If you get a chance to try it, I'd be very interested in knowing the results... Thanks, Sue!
I have some concerns about the methodology but you really can't test the scripts and do as posted - it will just be blocked and hang. So no one would be able to test it with the two sessions per the instructions and that was posted by Jeff earlier. I was just trying to figure out how the poster got the results they did and what they were seeing. I would guess that others who reported running it successfully included the rollback - which isn't what the poster meant. He was asking about the uncommitted transaction showing up and was trying to emulate that.
So I get the reason the poster did it that way though - they were looking into the estimated and actual row counts differences and wanted to test if the differences were due to uncommitted transactions from other sessions affecting the estimate and actual row counts. Those aren't the actually pieces of code causing the issues though so that also impacts things as the real table could have 20 billion rows and who knows what the inserts are. I'm guessing its comparing apples to a bicycle chains.Sue
Yeah, that's been my perspective from nearly the get-go.... Interesting topic though... Still gotta wonder if SQLCMD could reproduce it, even the same way the poster did...
I apologize for not making absolutely clear what I was doing. The create table part is just there for completeness. Once the table test is created, you just need 2 sessions in which transactions insert into test at the same time without committing. If you then run a select * from test in either session or run it in a third session then you should see the estimate I am seeing. You need to run all transactions in snapshot isolation otherwise you'll just get blocking. Note that if you run the select * from test in a third session you will get an estimate of 10 but 0 rows returned.
So first create table test and then run the following in 2 separate transactions including the actual execution plan without committing. And I indeed run it line by line.
set transaction isolation level snapshot
GO
begin tran
insert into test values (1),(2),(3),(4),(5)
select * from Test
It is indeed true that running it line by line gives a different result than executing it as a single batch. In my test the estimate in session 2 then shows 1 and returns 5 rows. But if GOs are added, the estimate of 10 is there again.
In the originating problem the queries are run from a .NET application so it seems that line by line execution or GO-delimited execution mimics how .NET is doing it. In a profiler we ran we indeed see separate sql statements issued by the same transaction.
If I have time I will try to set up an SQLCMD reproduction.
set transaction isolation level snapshot
GO
begin tran
GO
insert into test values (1),(2),(3),(4),(5)
GO
select * from Test
October 5, 2018 at 6:07 am
I think the issue here is what the estimator does
The testing I have done shows that the estimated row counts in the Actual Plan include rows in uncommitted transactrions when using SNAPSHOT ISOLATION
Far away is close at hand in the images of elsewhere.
Anon.
October 5, 2018 at 10:46 am
sgmunson - Thursday, October 4, 2018 3:08 PMSue_H - Thursday, October 4, 2018 1:39 PMsgmunson - Thursday, October 4, 2018 12:47 PMSue_H - Thursday, October 4, 2018 12:22 PMsgmunson - Thursday, October 4, 2018 11:37 AMSue_H - Thursday, October 4, 2018 10:40 AMsgmunson - Thursday, October 4, 2018 8:23 AMDavid Burrows - Thursday, October 4, 2018 8:04 AMsgmunson - Thursday, October 4, 2018 7:59 AMBouke Bruinsma - Thursday, October 4, 2018 7:33 AMsgmunson - Thursday, October 4, 2018 6:36 AMI get the feeling this has to do with running a part of the script separately, which might be getting treated differently than if it were all in the same batch. I'm not a fan of leaving a transaction hanging within whatever script you run, and this is pretty convincing as to a good reason why not. Curious as to the results if you were to test this entirely within SQLCMD.I created the script as an abstracted example of what we see in a more complex situation. Multiple concurrent transactions are running in snapshot and inserting data in the same table and executing mutliple queries that select this table. The transactions run in snapshot to avoid locking of data and because they should operate independent of eachother. The standard situation is that the table has no records, those are only inserted temporarily during the transactions. The transactions roll back in the end because it's cheaper than deleting and committing.
We found this issue because we could not reproduce the slow queries if we re-ran one of these transactions. They were siginificantly faster than when observed in operation. Finally we were able to pinpoint the mis estimate as the cause. In fact, we could reproduce the slowness if we did insert data in the table in other transactions.Besides the reproducability, I would be curious to know your opinion about the fact that uncommitted records weigh in in the estimate in snapshot. For a transaction running in read uncommitted isolation I can understand it. Then the actual rows returned would match the estimate. But in snapshot isolation it makes no sense. Even if the first transaction would commit before the second is finished, the second would still not see the data because of its snapshot character.
Okay, I get the logic, but are you actually reproducing that behavior, or just simulating it? I have trouble with believing in the results of scripts that are run line by line. SSMS will consider them individual batches. I'm not convinced that running the insert separately from the begin tran will produce the result you are expecting.
I wish I had more time available to play around with this, but just don't at the moment. Are you able to try this using SQLCMD instead of SSMS ? I'd be very curious to see if the behavior differs at all.When I tested it, I ran the script as a whole, not line by line
I suspected as much. A line by line execution could easily produce very different results because to the best of my knowledge, lines that you execute separately have no connection to other lines previously executed...
The first screenshot is a display of the estimated plan (not actual) and likely after executing the first part and leaving the transaction open to test what was going on. Not including the entire thing - no drop and recreating the table. So I think it's only picking up the previous execution from the same session when after that selecting the text to display the estimated plan. It's the estimated plan, not the actual.
Sue
Sue,
Just to be clear, I was responding to David Burrows, who was going about the task they way I would have. What you mentioned is part of why I was having trouble believing that what I was seeing was entirely real. I'm not convinced that a transaction left open necessarily ever commits, as a separate batch run of an insert that is not directly a part of the tran open batch doesn't seem likely to me to end up as part of anything but an implicit transaction of its own. I may be wrong, but I need better evidence. I'm hearing others test using the full scripts, and not seeing the problem. I have a hard time buying that the line by line run that Bouke was doing was going to act the same. Again, maybe I'm right, maybe not. The evidence presented suggests I might be.... or that I'm misinterpreting what I'm seeing.
Yes...I realized the post you were responding too. I'm not saying its correct to do, I was just trying to explain how the poster would get the results.The estimated execution plan (estimated) does show uncommitted transactions but the poster would be executing part of the the posted script for it to work with getting the result they got. Just play with this part:
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 TestYou can't run the entire script in two different sessions and leave the transaction open - one would just hang. I think the create and drop table thing is getting in the way of what they were trying to explain about the issue they are seeing. I could repeat the issue they were seeing but not with running the drop and create. If you leave that out, that is how I could see what was reported and pretty sure it's the only way to get those results.
Just create the table and leave that out of it. Then open two windows and use the snippet I posted. Then highlight the select * from Test and show estimated execution plan. Go to the other window and run the snippet a couple of times. Go over to the other window, highlight the select statement, show estimated execution plan.Sue
Yeah, that's what I was thinking.... I'm pretty sure the OP's testing methodology is somewhat flawed. I can't imagine doing a test without a full script getting run, including any commits and/or rollbacks. One can always use WAITFOR DELAY to script in an intentional time delay to help coordinate a time difference.
I'm still curious, however, as to whether or not running the OP's test methodology, however flawed, would behave any differently.... If you get a chance to try it, I'd be very interested in knowing the results... Thanks, Sue!
I have some concerns about the methodology but you really can't test the scripts and do as posted - it will just be blocked and hang. So no one would be able to test it with the two sessions per the instructions and that was posted by Jeff earlier. I was just trying to figure out how the poster got the results they did and what they were seeing. I would guess that others who reported running it successfully included the rollback - which isn't what the poster meant. He was asking about the uncommitted transaction showing up and was trying to emulate that.
So I get the reason the poster did it that way though - they were looking into the estimated and actual row counts differences and wanted to test if the differences were due to uncommitted transactions from other sessions affecting the estimate and actual row counts. Those aren't the actually pieces of code causing the issues though so that also impacts things as the real table could have 20 billion rows and who knows what the inserts are. I'm guessing its comparing apples to a bicycle chains.Sue
Yeah, that's been my perspective from nearly the get-go.... Interesting topic though... Still gotta wonder if SQLCMD could reproduce it, even the same way the poster did...
I apologize for not making absolutely clear what I was doing. The create table part is just there for completeness. Once the table test is created, you just need 2 sessions in which transactions insert into test at the same time without committing. If you then run a select * from test in either session or run it in a third session then you should see the estimate I am seeing. You need to run all transactions in snapshot isolation otherwise you'll just get blocking. Note that if you run the select * from test in a third session you will get an estimate of 10 but 0 rows returned.
So first create table test and then run the following in 2 separate transactions including the actual execution plan without committing. And I indeed run it line by line.
set transaction isolation level snapshot
GO
begin tran
insert into test values (1),(2),(3),(4),(5)
select * from Test
It is indeed true that running it line by line gives a different result than executing it as a single batch. In my test the estimate in session 2 then shows 1 and returns 5 rows. But if GOs are added, the estimate of 10 is there again.
In the originating problem the queries are run from a .NET application so it seems that line by line execution or GO-delimited execution mimics how .NET is doing it. In a profiler we ran we indeed see separate sql statements issued by the same transaction.
If I have time I will try to set up an SQLCMD reproduction.
set transaction isolation level snapshot
GO
begin tran
GO
insert into test values (1),(2),(3),(4),(5)
GO
select * from Test
It's okay...we figured out what you were doing 🙂
I really doubt the estimates would change when done using two sqlcmd sessions to do the same thing. I like your post though. It was an interesting thing to see as I didn't expect the estimated or actual plans to read uncommitted transactions either when using isolation level snapshot....which is your original question. I would also love to see more information of the uncommitted transactions coming into play with the execution plans.
In your real production scenario - what is the number difference you are seeing with the estimated rows and actual rows?
Sue
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply