behaviour of isolation level snapshot on partitioned tables and views

Question

behaviour of isolation level snapshot on partitioned tables and views

christian_t

Hall of Fame

Points: 3588
More actions
October 1, 2013 at 5:04 am

#283339

Hi there,
more often then we which for, we have same requests that results in a set of queries that take more then a few hours.
E.g. Calculating something for each day from 2011 and for each day we need 30 minutes.
While the time that is used is totally reasonable we have the issue that those queries often block the imports on the next daily import.
I was thinking about using the isolation level "snapshot" to avoid update/insert-locks because this would fix the locks.
However snapshot would create a "copy" of the table in tempdb.
Now we have large amounts of data that is partitioned by "partitioning functions" or by seperated tables (constrains on table + union all in view)-Partitioning.
In the queries we strictly give elimination hints for those tables (e.g. partion_date = '2013-10-01') and use the option recompile so that it is garanteed that the hint is taken in consideration.
Does anyone have the experience what would happen with the query would use the "snapshot".
Would only the relevant partitions be copied or would be first made the copy and than the query is started?
Kind Regards
Christian

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Snapshot isolation does not create a copy of the entire table in TempDB, so the rest of your question is moot, that's not how it works at all.

http://msdn.microsoft.com/en-us/library/tcbchxcb.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

christian_t Hall of Fame Points: 3588 More actions · Answer 2

Thx for the clarification Gila,

After that article I get what is really happening with snapshot-isolation.

I guess the only disadvantage is performance if you have a long statement on a heavilly changed table?

If I would only update/insert the tables once a day and I would like to prevent my aggregations block my update/insert (when they are running longer, as mentionted above), would there be a reason not to use snapshot isolation level?

kind regards

Christian

Jason L SSC Eights! Points: 849 More actions · Answer 3

have a look at read committed snapshot isolation (RCSI), it might be what you're looking for, a bit less onerous than full snapshot isolation. Queries that run for hours sound like they need some tuning, just saying...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

christian.terhart (10/1/2013)
I guess the only disadvantage is performance if you have a long statement on a heavilly changed table?

Or lots and lots and lots of changes on a seldom-read table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 5

Jason L (10/1/2013)
have a look at read committed snapshot isolation (RCSI), it might be what you're looking for, a bit less onerous than full snapshot isolation.

Actually RCSI is the "big hammer" approach to snapshot isolation. Once turned on it will change all queries that are currently running in read committed isolation level (which is the default) and change them to use the version stores. With Snapshot Isolation, after you turn it on, you must specify SET TRANSACTION ISOLATION LEVEL SNAPSHOT to use it. So Snapshot Isolation can be used only where you want the change unlike RCSI.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Keith Tate (10/1/2013)
Actually RCSI is the "big hammer" approach to snapshot isolation. Once turned on it will change all queries that are currently running in read committed isolation level (which is the default) and change them to use the version stores. With Snapshot Isolation, after you turn it on, you must specify SET TRANSACTION ISOLATION LEVEL SNAPSHOT to use it. So Snapshot Isolation can be used only where you want the change unlike RCSI.

True, however once Allow Snapshot Isolation has been turned on, all data modifications write old versions into the row version store, regardless of whether the isolation level is ever requested. It's very possible to get all the downsides and none of the benefits from snapshot isolation by enabling it but never using the isolation level. At least once RCSI is turned on you immediately get the benefits and downsides.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

christian_t Hall of Fame Points: 3588 More actions · Answer 7

Thank you for your advice.

So I guess if I am willing to "buy" all the disadvantages when enabling snapshot, I can just as well use RCSI so I do not have to adjust all my query-statements?

Queries that run for hours sound like they need some tuning, just saying...

A single query is not running for hours but if we are calculating back a few month or years it can take its time.

We are talking about three-digit million to billion rows in those partitioned tables.

However I can not estimate how "bad" the disadvantages are.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

If you only modify the table once a day, the 'disadvantages' are minimal as there aren't that many data modifications. It's when you have a lot of changes that you can have a heavy impact on TempDB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

christian_t Hall of Fame Points: 3588 More actions · Answer 9

I am running a classic DWH so yes, I am just modifying the tables once a day, reading multiple times.

I will see how this setting is changing my regular imports and aggregations and will let you know.

Cheers,

Christian

christian_t Hall of Fame Points: 3588 More actions · Answer 10

So after two weeks of monitoring I can tell you, that RCSI had no significant performance impact on my system.

I even had the situation that the job importing was overlapping with the aggregation job (due to late delivery of the source system) and the import was not locked by the aggregation.

I also checked that the results of the aggregation where calculated correctly and it behaved like supposed to: it just used the data that have been there at the beginn of the query.

Thanks again for your advice

Christian