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 «««12345

Performance Implications of Database Snapshots Expand / Collapse
Author
Message
Posted Saturday, December 4, 2010 10:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:16 AM
Points: 1,612, Visits: 1,537
That's actually a very good question Low Rider.

The snapshot is a read-only, point-in-time database. As such, the queries run against it do not create locks nor do they honor locks. There can be no dirty reads since the data doesn't change so there's no need to lock anything. This means greater concurrency. More queries can read the data at the same time without being blocked by writes.

However, the read of the data still hits the source database's data files, so it still has an impact on the source database. This is why a majority of people (in my experience) use database snapshots for a read-only reporting database on the mirror database in a mirroring partnership. This moves the IO completely off of the live database and server.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1030287
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse