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 Tuesday, September 23, 2008 9:16 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 750, Visits: 3,090
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?

Post #574870
Posted Wednesday, September 24, 2008 12:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
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 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 #574920
Posted Wednesday, September 24, 2008 8:03 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 750, Visits: 3,090
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.
Post #575217
Posted Wednesday, September 24, 2008 1:05 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
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.

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 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 #575526
Posted Wednesday, September 24, 2008 1:46 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:29 PM
Points: 1,593, Visits: 1,489
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #575558
Posted Wednesday, September 24, 2008 1:49 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:29 PM
Points: 1,593, Visits: 1,489
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #575562
Posted Wednesday, September 24, 2008 2:26 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 3:50 AM
Points: 750, Visits: 3,090
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.

Post #575579
Posted Thursday, September 25, 2008 10:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
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 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 #576164
Posted Monday, November 23, 2009 11:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:58 AM
Points: 3, Visits: 18
Gail,

Good job on delivering a quantitative measure of the impact of snapshots.
Post #823437
Posted Saturday, December 04, 2010 3:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 10, 2014 9:53 AM
Points: 51, Visits: 446
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

Post #1030252
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse