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

Creating a Database Snapshot Expand / Collapse
Author
Message
Posted Monday, March 9, 2009 7:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:56 AM
Points: 28, Visits: 300
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.
Post #671500
Posted Monday, March 9, 2009 8:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 1,262, Visits: 3,420
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/
Post #671521
Posted Monday, March 9, 2009 9:38 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 @ 3:22 PM
Points: 42,484, Visits: 35,553
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 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 #671654
Posted Monday, March 9, 2009 10:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:56 AM
Points: 28, Visits: 300
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.
Post #671712
Posted Monday, March 9, 2009 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 @ 3:22 PM
Points: 42,484, Visits: 35,553
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 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 #671726
Posted Tuesday, March 10, 2009 7:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, May 25, 2014 10:09 AM
Points: 283, Visits: 1,114
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
Post #672919
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse