Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««12345

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



Group: General Forum Members
Last Login: 2 days ago @ 6:49 AM
Points: 1,634, Visits: 1,621
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 MVP
Database Engineer at BlueMountain Capital Management
Post #1030287
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse