SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


behaviour of isolation level snapshot on partitioned tables and views


behaviour of isolation level snapshot on partitioned tables and views

Author
Message
christian_t
christian_t
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 531
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233005 Visits: 46361
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
christian_t
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 531
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
Jason L
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 441
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233005 Visits: 46361
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
Keith Tate
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2390 Visits: 979
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233005 Visits: 46361
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
christian_t
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 531
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233005 Visits: 46361
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
christian_t
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 531
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search