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


Creating a Database Snapshot


Creating a Database Snapshot

Author
Message
Jose Ianuck
Jose Ianuck
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 419
Hi, all. We created a Database snapshot to work read only, just reports by web application, so what impact does a database snapshot have on application transaction throughput and performance?

Thank´s
Ian.
Dugi
Dugi
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7152 Visits: 3511
Jose Ianuck (3/9/2009)
Hi, all. We created a Database snapshot to work read only, just reports by web application, so what impact does a database snapshot have on application transaction throughput and performance?

Thank´s
Ian.


DB snapshots are read-only and you cannot do any transactions as you mentioned with application transaction, and till you are planing to use it just for reports, you would create every 15-30 min snapshot ( it depends from number of transactions) and this procedure should happened dropping snapshot then creating again with same name ...so at this time if the user is viewing any data as reports with application, will lose conn or cannot view data properly or some other issue etc!

I prefer that you can create another DB and copying from your production server all the data to that DB, so this DB will better if you use it for reporting! This reporting DB can have the latest data every 20-30 min ( it depends from number of transactions) and no connection with production server to do something bad with performance!

P.S. Why do you want to use snapshot DB for reporting?

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)

Group: General Forum Members
Points: 369889 Visits: 46948
Jose Ianuck (3/9/2009)
so what impact does a database snapshot have on application transaction throughput and performance?


Depends on your app, your hardware and the DB's usage. Could be anything from no impact to intolerable
I did some tests a while back - http://www.sqlservercentral.com/articles/Performance/64080/

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


Jose Ianuck
Jose Ianuck
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 419
The big problem is to avoid locks or bad performance, then the use a snapshot Database for consult and reports web application, I don´t know this is a good solution. But create a snapshot database, it is so fast.

Thank´s
Ian.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)

Group: General Forum Members
Points: 369889 Visits: 46948
Jose Ianuck (3/9/2009)
The big problem is to avoid locks or bad performance

Locks it will avoid, bad performance, maybe , maybe not

But create a snapshot database, it is so fast.

Yup. Do you know why it's so fast?

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


Richard Fryar
Richard Fryar
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1473 Visits: 1172
In depth analysis here, if you want more detail:

http://sqlcat.com/whitepapers/archive/2008/02/11/database-snapshot-performance-considerations-under-i-o-intensive-workloads.aspx


Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
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