Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

behaviour of isolation level snapshot on partitioned tables and views Expand / Collapse
Author
Message
Posted Tuesday, October 1, 2013 5:04 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:48 AM
Points: 103, Visits: 451
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


Post #1500341
Posted Tuesday, October 1, 2013 5:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 40,663, Visits: 37,128
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 2008, MVP
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

Post #1500348
Posted Tuesday, October 1, 2013 6:14 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:48 AM
Points: 103, Visits: 451
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
Post #1500363
Posted Tuesday, October 1, 2013 6:20 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 6:00 AM
Points: 535, Visits: 437
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...
Post #1500367
Posted Tuesday, October 1, 2013 6:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 40,663, Visits: 37,128
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 2008, MVP
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

Post #1500375
Posted Tuesday, October 1, 2013 9:57 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 3:02 PM
Points: 598, Visits: 940
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
Post #1500481
Posted Tuesday, October 1, 2013 10:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 40,663, Visits: 37,128
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 2008, MVP
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

Post #1500492
Posted Tuesday, October 1, 2013 11:48 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:48 AM
Points: 103, Visits: 451
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.
Post #1500520
Posted Tuesday, October 1, 2013 12:02 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 40,663, Visits: 37,128
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 2008, MVP
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

Post #1500527
Posted Tuesday, October 1, 2013 2:01 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:48 AM
Points: 103, Visits: 451
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

Post #1500586
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse