SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Implications of Database Snapshots


Performance Implications of Database Snapshots

Author
Message
matt stockham
matt stockham
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3178
Nice article. I have a few questions:

1) why do inserts scale so much better than updates and deletes on a workstation? A page is a page, assuming that the same number of pages were modified in the insert load as the update and delete loads I would expect them to scale similarly. Did you happen to look at the wait stats by any chance?

2) any plans for an article on read performance with multiple snapshots?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86805 Visits: 45254
matt stockham (9/23/2008)
Nice article. I have a few questions:

1) why do inserts scale so much better than updates and deletes on a workstation? A page is a page, assuming that the same number of pages were modified in the insert load as the update and delete loads I would expect them to scale similarly. Did you happen to look at the wait stats by any chance?


I'm not sure. I suspect it may have something to do with what needs to be copied to the snapshot. In the case of an insert that's writing into a new page added to the table, all that needs to be done to the snapshot file is that empty page adding.

In the case of updates and deletes, the entire old version of the page (all 8k of it) has to be written intact to the snapshot.


2) any plans for an article on read performance with multiple snapshots?


I don't. I can look into it, but I suspect there will be little to see. Regardless of how many snapshots are in place, when a read occurs aginst a snapshot, SQL will check to see if the affected pages are in the snapshot, if so read from snapshot otherwise read from the source DB

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


matt stockham
matt stockham
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3178
Re. inserts ... wouldn't it have to write an entire page of null bytes, so still the same data size?

Logically it shouldn't need to do anything at all with a new page - the snapshot won't care because the page didn't exist at the time it was created. Following that train of thought makes me wonder why there is any overhead at all unless there is some requirement on the index pages or any of the system-type pages - maybe contention on whichever set of pages determines if the snapshot file holds the page to look at? Am I missing something obvious here?

re. read performance ... I wouldn't expect a great deal as there shouldn't be any locking (snapshots don't lock because they are read-only, correct?). I only asked because the article covered all the other obvious scenarios - there are probably a number of good questions that could be answered on the read side - does the snapshot have its own procedure cache entries etc.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86805 Visits: 45254
matt stockham (9/24/2008)
Re. inserts ... wouldn't it have to write an entire page of null bytes, so still the same data size?

Logically it shouldn't need to do anything at all with a new page - the snapshot won't care because the page didn't exist at the time it was created. Following that train of thought makes me wonder why there is any overhead at all unless there is some requirement on the index pages or any of the system-type pages - maybe contention on whichever set of pages determines if the snapshot file holds the page to look at? Am I missing something obvious here?


Possibly the overhead is caused by necessary changes to the allocation pages. I'm just guessing here, as I don't know the exact details of what changes are made to what and when when it comes to snapshots.

I do know someone to ask though. Cool

I only asked because the article covered all the other obvious scenarios - there are probably a number of good questions that could be answered on the read side - does the snapshot have its own procedure cache entries etc.


I'll put said article on the to-do list.

From what I recall, the snapshots do have their own cache entries, both procedure and data. This applies even to data pages read from the source database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2734 Visits: 1623
matt stockham (9/24/2008)
Re. inserts ... wouldn't it have to write an entire page of null bytes, so still the same data size?

Logically it shouldn't need to do anything at all with a new page - the snapshot won't care because the page didn't exist at the time it was created.


You're assuming that inserts are put into new pages. That's not always true. Inserts will be added to existing pages until SQL needs a new page. So inserts will be affected by snapshots in these cases.



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
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2734 Visits: 1623
Great article Gail!!! I'd love to see a follow-up where you measure the effects that snapshots on a mirrored database (on the mirror partner) running in synchronous mode has on the principal partner.



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
matt stockham
matt stockham
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3178
Robert Davis (9/24/2008)
matt stockham (9/24/2008)
Re. inserts ... wouldn't it have to write an entire page of null bytes, so still the same data size?

Logically it shouldn't need to do anything at all with a new page - the snapshot won't care because the page didn't exist at the time it was created.


You're assuming that inserts are put into new pages. That's not always true. Inserts will be added to existing pages until SQL needs a new page. So inserts will be affected by snapshots in these cases.


Correct ... the article stated that the table was set to force 2 entries per page with an identitifer clustered index, and my assumption is that the identity seed had not been reset.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86805 Visits: 45254
Robert Davis (9/24/2008)
Great article Gail!!! I'd love to see a follow-up where you measure the effects that snapshots on a mirrored database (on the mirror partner) running in synchronous mode has on the principal partner.


Interesting idea. I'll put it on the list of things to do.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


david_lundell
david_lundell
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 22
Gail,

Good job on delivering a quantitative measure of the impact of snapshots.
Low Rider
Low Rider
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 487
I have a doubt regarding the performance part of database snapshots.Now it's said that one can use a database snapshot as a reporing database where the reports can point to the snapshot database instead of the original db.

Now if the data in the pages is not modified doesn't a query which runs against the snapshot db would retrieve the same from the pages/extents of the original database.

Since the orginal database has to serve the request how exactly the database snapshot will give any performance benefit ?

thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search