September 23, 2008 at 9:16 pm
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?
September 24, 2008 at 12:36 am
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
September 24, 2008 at 8:03 am
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.
September 24, 2008 at 1:05 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2008 at 1:46 pm
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.
September 24, 2008 at 1:49 pm
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.
September 24, 2008 at 2:26 pm
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.
September 25, 2008 at 10:06 am
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
November 23, 2009 at 11:47 am
Gail,
Good job on delivering a quantitative measure of the impact of snapshots.
December 4, 2010 at 3:33 am
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
December 4, 2010 at 10:27 am
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.
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply