0

I have a SQL Server database which stores accounts with credits (about 200.000 records), and a separate table which stores the transactions (about 20.000.000).

Whenever a transaction is added to the database the credits are updated.

What I need to do is update client programs (using a web service) to store the credits locally, and whenever new transactions are added to the server they are sent to the clients as well (using timestamps for the delta). My main problem is creating the first data set for the client. I need to supply the list of all accounts and the last timestamp on the transaction table.

This would mean I have to create this list and the last timestamp within a snapshot, because any updates during creating this list would mean a mismatch in credits total and last transaction timestamp.

I've researched the ALLOW_SNAPSHOT_ISOLATION setting and using snapshot isolation on the SqlCommand transaction, but from what I've read this will induce a significant performance penalty. Is this true, and can this problem be solved using other means ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

2 Answers2

1

but from what I've read this will induce a significant performance penalty.

I don't even want to know where you read that. I'll refer you to the official document. The costs come from additional tempdb space used for row versions and from traversing old row versions. These problems do not concern you if you have a low write rate.

Snapshot isolation is a boon for solving blocking and consistency issues. It is a perfect match for your scenario.

Many SQL Server questions on Stack Overflow lead me to comment "did you investigate snapshot isolation yet?". Most underused feature.

Oracle and Postgres have it always on.

usr
  • 168,620
  • 35
  • 240
  • 369
  • In the linked article a number of performance consequences are given and there must be a reason sql server default's off... I'll give a try and measure the performance penalty myself instead of trusting what 'the internet' is saying. I was hoping there was another trick to do what I want, but as you stated, my scenario is one of the things snapshot isolation was designed for. – user3575631 Apr 26 '14 at 10:35
  • It's default off because of compatibility. Azure's SQL has it forced-on. If you're concerned you should definitely test it. That said I have never experienced or heard about performance problems. It seems to be a feature that no one complains about. Do you understand what specific perf problems they warn about? You should. They warn about "The costs come from additional tempdb space used for row versions and from traversing old row versions.". To which I said: "These problems do not concern you if you have a low write rate.". Sounds pretty logical to me. – usr Apr 26 '14 at 10:37
  • Just about every SQL Server feature has a perf warning slapped onto it, man! Are you using indexes? These hit 10 times more than snapshots ever can. – usr Apr 26 '14 at 10:38
  • From the article "Data readers face the extra cost of traversing the version link list. The older the snapshot, the slower the process of accessing it in a snapshot isolation transaction." As I have a number of heavy queries for reporting this might be an issue. Is it ok to turn ALLOW_SNAPSHOT_ISOLATION on when needed and off when not needed as some samples do ? – user3575631 Apr 26 '14 at 10:42
  • That's correct. If there are no row versions, though, no perf cost comes of this. Understand, that row versions are created on write and are discarded quickly after the last transaction ended that might use them. They are transitory data. How much writes do you even have?; Turning it on temporarily is ok, but it requires a database X-lock which means killing all sessions. – usr Apr 26 '14 at 10:44
  • ". Is it ok to turn ALLOW_SNAPSHOT_ISOLATION on when needed and off when not needed as some samples do ?" Due to concurrent client updates this is not an option. Should have thought it through before asking. – user3575631 Apr 26 '14 at 10:45
  • I have about 10 writes per second. – user3575631 Apr 26 '14 at 10:45
  • Let's estimate the perf hit then. Let's say the report runs for 1000sec. Then in that time 10k row versions have accumulated. The report will have to read and discard 10k unneeded rows. That should take <10ms of CPU time. You therefore do not have a problem.; If on the other hand you hat 10k writes to the same row per second than that would make for a very nasty version chain. – usr Apr 26 '14 at 10:47
  • SI makes people concerned who have 10TB databases with 100GB of log per hour. In other words, not you and me. SO uses SI and it made their blocking and deadlocking issues go away. They said this on the podcast 3 years ago. – usr Apr 26 '14 at 10:49
1

Don't jump onto SI wagon hastily. As everythng else it has it's benefits and has it's drawbacks.

As the drawbacks are concerned, for example, the application might count on blocking behaviour or/and is willing to wait for that last version of the data. You should thoroughly test the application under SI to be sure it behaves correctly. Further, an uncommitted transaction could make a mess of the version store and lead to dramatic tempdb growth, so monitoring is a must.

Also, SI might be an overkill for you, if normally you don't have blocking issues.

Instead, if what you need is a one-off or close to it, create a database snapshot of your database, create the initial list from that snapshot, and then simply drop it.

dean
  • 9,960
  • 2
  • 25
  • 26
  • We currently have >700 client systems which require a daily snapshot, database is 25Gb in size, so I see some problems using that option. Thanks for the suggestion, will look into it – user3575631 Apr 26 '14 at 10:54
  • +1 because there are good points here. I wouldn't overestimate their importance in most deployments, though. Or maybe I'm the rare exception of someone who has never been bitten by SI? It is such a boon from a developers point of view. – usr Apr 26 '14 at 10:54
  • @usr I do like both RCSI and SI, implemented it myself many times, but always it's a test first, planning, sizing the tempdb, monitoring for any annomalies.. And yes, we had some moments :) – dean Apr 26 '14 at 11:07