Database Snapshots in SQL Server 2005

  • Comments posted here are about the content posted at temp

  • 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

  • Hi Marino van der Heijden,

    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