Creating a Database Snapshot
-
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.
-
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?
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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.
-
Jose Ianuck (3/9/2009)
The big problem is to avoid locks or bad performanceLocks 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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
In depth analysis here, if you want more detail:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply