<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Performance Tuning
»
Creating a Database Snapshot
Creating a Database Snapshot
Rate Topic
Display Mode
Topic Options
Author
Message
Jose Ianuck
Jose Ianuck
Posted Monday, March 09, 2009 7:53 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:02 AM
Points: 27,
Visits: 246
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
Dugi
Dugi
Posted Monday, March 09, 2009 8:09 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 02, 2013 1:48 AM
Points: 1,252,
Visits: 3,367
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
GilaMonster
GilaMonster
Posted Monday, March 09, 2009 9:38 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099,
Visits: 30,392
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
Jose Ianuck
Jose Ianuck
Posted Monday, March 09, 2009 10:25 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:02 AM
Points: 27,
Visits: 246
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
GilaMonster
GilaMonster
Posted Monday, March 09, 2009 10:35 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099,
Visits: 30,392
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
Richard Fryar
Richard Fryar
Posted Tuesday, March 10, 2009 7:00 PM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 2:53 AM
Points: 242,
Visits: 884
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
FREE DOWNLOAD
www.sqlcopilot.com
Post #672919
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
