September 23, 2008 at 12:32 am
Comments posted to this topic are about the item Performance Implications of Database Snapshots
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 23, 2008 at 12:49 am
Brilliant article! It just goes to show that there are a whole host of things to consider whenever you're devising a backup strategy for your systems. Thanks to the information you've provided Gail, I'm sure you've saved a lot of people a considerable amount of time if they're looking to evaluate the effectiveness of snapshots as a backup and recovery solution.
September 23, 2008 at 1:06 am
Thanks. Glad you liked it.
Just one thing though. Snapshots are not a backup strategy. They're great for rolling back accidental changes, but if the source DB is damaged, dropped, corrupted, offline, etc, the snapshot will be inaccessible and will have to be dropped.
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 23, 2008 at 1:10 am
All the more reason for me to read up some more on them! 🙂
September 23, 2008 at 1:28 am
Phil Melling (9/23/2008)
All the more reason for me to read up some more on them! 🙂
:hehe: When I get a chance I'll try and finish the follow-up to this article - what snapshots are and what they're not.
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 23, 2008 at 2:21 am
I would say the conclusion is a bit of an understatement - with 5 snapshots the graphs show performance degradation of 500% at best, and 5000% at worst...
Put another way, unless your SQL Server is under about 2% load or less, then putting in 5 snapshots would have the distinct possibility that your server would then be under 100% load.
Of course, that's an over-simplification, and the tests aren't very specific about the bandwidth of disk IO etc etc - but even so, those graphs are distinctly scary.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 23, 2008 at 2:22 am
We use Snapshots in our production system, what I am wondering is whether the snapshots for the tests were on the same disks as the main database file, or seperate ones, and also whether the snapshots themselves were on seperate disks?
People should also be incredibly wary of putting a snapshot on the same disk as a DB, due to the nature of how snapshots work they become very fragmented (this should probably be addressed by MS), should your filegroup grow on that disk then the fragmentation will carry over to that too, leading to a performance loss.
September 23, 2008 at 2:43 am
Nice article Gail.
The pages from the source database get written to the snapshot's sparse file the first time the page gets modified in the source database. Is that right?
If that's so, does then the performance degradation happen only for the first time a page gets modified.
"Keep Trying"
September 23, 2008 at 2:45 am
Mike Metcalf (9/23/2008)
We use Snapshots in our production system, what I am wondering is whether the snapshots for the tests were on the same disks as the main database file, or seperate ones, and also whether the snapshots themselves were on seperate disks?
For the first set of tests (insert, update, delete) everything was on 1 drive. It was a workstation PC, it only had one drive.
The second test that I did (deletes on the server) the snapshot files were separate from the data and log files. iirc, I put them onto the drive used for backups (which was not in use at that time)
I didn't have enough drives (even on the server) to test with one snapshot per drive. If they are, I would imagine the effect is drastically reduced.
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 23, 2008 at 2:47 am
Chirag (9/23/2008)
Nice article Gail.The pages from the source database get written to the snapshot's sparse file the first time the page gets modified in the source database. Is that right?
If that's so, does then the performance degradation happen only for the first time a page gets modified.
Yes to both.
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 23, 2008 at 2:47 am
Chirag (9/23/2008)
The pages from the source database get written to the snapshot's sparse file the first time the page gets modified in the source database. Is that right?If that's so, does then the performance degradation happen only for the first time a page gets modified.
If that were the case, then delete performance wouldn't be affected, and neither would update... ??
... sorry didn't see that the deletes were on different rows! 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 23, 2008 at 2:49 am
Matt Whitfield (9/23/2008)
Chirag (9/23/2008)
The pages from the source database get written to the snapshot's sparse file the first time the page gets modified in the source database. Is that right?If that's so, does then the performance degradation happen only for the first time a page gets modified.
If that were the case, then delete performance wouldn't be affected, and neither would update... ??
Sure it would. Both are making changes to the source DB. If the pages aren't already in the snapshot file then they have to be copied there before the update/delete can occur.
In the tests I did, I was very, very careful that the inserts, the updates and the deletes affected different pages. Otherwise the results would be skewed.
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 23, 2008 at 2:50 am
GilaMonster (9/23/2008)
In the tests I did, I was very, very careful that the inserts, the updates and the deletes affected different pages.
Yeah - i was just editing my post to say i'd missed that bit 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 23, 2008 at 4:18 am
Is the snapshot functionality mentioned here the same that is used for snapshot replication? do the same issues apply for this if you run the replication once a day on a schedule?
Oraculum
September 23, 2008 at 4:19 am
completely different 🙂
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply