Database Snapshots in SQL Server 2005
-
Comments posted here are about the content posted at temp
RegardsArvinder
arvinder-khosla.blogspot.com -
Great article! It describes all the things you need to know to get started with snapshots.
I have a question though. Do I understand correctly that a snapshot will cause a LOT of overhead if you leave it running for days or, perhaps, weeks? I tend to use snapshots only in particular situations in which you exactly know what you want to do/find out.
Forgive me, I'm just trying to figure out when you can use snapshots best. for now, I think it might be useful in some situations. Especially for ad hoc reporting, problem solving, debugging and restoring a database after a dba or developer tested some small changes.
-
I was all ready to start trying this until I read:
3. Performance of the source database is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
How much of a performance hit are we talking about?
-
Great article. I wonder how does this compare to Log Shipping. Currently we are using Log Shipping for reporting and have issues of killing all users before applying the log update. Would Snapshot be a better choice for reporting?
-
Hi,
Thanks for the comments.Snapshots would be definitely a better alternative to Log shipping for reporting purposes.For databases that does not undergo large changes,if the user takes a snapshot,then the snapshots would be storing only those pages which have got changed.I think this answers your question.You can mail me at srivathsani_m@infosys.com for further issues.
Regards,
S.Srivathsani
-
Hi,
Thanks for the comments.Snapshots would be definitely a better alternative to Log shipping for reporting purposes.For databases that does not undergo large changes,if the user takes a snapshot,then the snapshots would be storing only those pages which have got changed.I think this answers your question.You can mail me at srivathsani_m@infosys.com for further issues.
Regards,
S.Srivathsani
-
Hi Mark Yelton,
3. Performance of the source database is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
The user needs to use the Snapshot feature judiciously.If the snapshot is taken for a database which undergoes lots of changes,then it would hit the performance of the source database.This is because the when the page gets updated,the original page has to be written to the sparse file.Snapshot is ideal for databases with not too many updates/deletes.
Please feel free to mail me at srivathsani_m@infosys.com for any further queries.
Regards,
S.Srivathsani
-
As you said its very true,Snapshots are ideal for scenarios like auditing,reporting etc.I am not quite clear with the statement that "If the snapshot is allowed to run for weeks".You mean to say that if we keep taking snapshots daily for a week or so.It would be a overhead on the server if the database undeergoes frequent changes.Otherwise,if the database is quite static,then it wouldnt be a overhead.
Regards,
S.Srivathsani
-
Hi, how does the unchanged data is accessed from the snapshot? are they accessed directly from the Source database? if so woudn't that be a problem for the source database performance if reporting or somthing else excessively happening on the snapshot database?
But if you have a log shipped database then it's a completely independent from the source and won't hinder the performance of the source database.
Am i right?
-
Thanks for the reply. What I meant with a "snapshot allowed to run for weeks" is a snapshot taken some weeks ago, that will generate double activity. Every change in the source database will also cause activity in the snapshot. Therefore I wonder if you should be careful with snapshots. Once you don't need a snapshot anymore, you should probably delete it immediately, right?
It's a fact that it will cause impact on performance. But a 'normal' server should be able to handle that, so that mustn't be a problem.
-
It is not true that a snapshot consumes less disk space than a disk backup. It could actually consume more. The space taken by the snapshot database is EXACTLY the same as the original database data files, minus the log file.
If the database has a lot of available space, then the backup will consume less.
-
Snapshot database and Snapshot backups are two things.
Snapshot database is always <= Original database becasue it only contains the changed data.
Snapshot backup is more complex subject not direclty relevant to the database snapshot.
-
A snapshot database is NOT <= Original. It will be = Original minus the log.
See proof below. Commands in black, results in red.
exec sp_helpdb UPSRC -- show size of original database (approx 301 GB)
name db_size owner
--------------- ------------- --------------------
UPSRC 301718.13 MB RAINBIRD\PSAdmin
name fileid filename size
--------------- ------ ----------------------------- -------------
rbprod_data 1 F:\mssql\data\UPSRC_Data.mdf 253744832 KB
rbprod_log 2 F:\mssql\data\UPSRC_Log.ldf 55214528 KB
CREATE DATABASE UPSRC_Snap
ON (NAME = rbprod_data, FILENAME = 'f:\mssql\data\upsrc_snap.ss') AS SNAPSHOT OF UPSRC
exec sp_helpdb UPSRC_Snap -- show size of snapshot database (approx 301GB)
name db_size
------------- --------------
UPSRC_Snap 301718.13 MB
name fileid filename size
--------------- ------ ------------------------------ --------------
rbprod_data 1 F:\mssql\data\UPSRC_Data.mdf 253744832 KB
rbprod_log 2 F:\mssql\data\UPSRC_Log.ldf 55214528 KB
dir f:\mssql\data (show size of database files on disk)
11/27/2006 07:13 PM 259,834,707,968 UPSRC_Data.mdf
11/27/2006 07:13 PM 56,539,676,672 UPSRC_Log.ldf
12/11/2006 04:12 PM 259,834,707,968 upsrc_snap.ss
-
sp_helpdb shows you the maximum size of the file not the size actually used by the data in the files.
To find out the actual size of a sparse file
To learn the number of bytes each sparse file of the snapshot is currently using on disk, you can use the fn_virtualfilestats system table-valued function, which returns the current number of bytes in a file in the BytesOnDisk column. This function takes database_id and file_id as values. The database ID of the snapshot and the file IDs of each of its sparse files are displayed in the sys.master_files catalog view. For more information, see sys.master_files (Transact-SQL) and fn_virtualfilestats (Transact-SQL).
Alternatively, to see the disk space used by a sparse file, you can right-click the file in Microsoft Windows, click Properties, and look at the Size on disk value.
-
Bimal,
Thanks for the clarification. I went so far as to examine the properties of the drive and found that after dropping the snapshot, that available space went up on the drive by exactly the amount shown in the "size on disk" property of the snapshot file.
This was very helpful. I had abandoned use of the snapshot months ago when it looked like it was consuming the same space as the original. We had noticed the performance hit in our testing and together with the perceived hit on disk space gave up.
Dang... I hate it when I am wrong.
Dave
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply