none
Snapshot Question regarding FQN object names

    Pergunta

  • If I have a database called DB1 and in it is a Proc that executes "select Column from DB1.dbo.TableName" and I create a snapshot of that database called DB1_snap and a user executes the Proc against the snapshot, will it hit the table in the snapshot database DB1_snap or will it hit the main databases table that the snapshot was created from. 

    Purpose is we are trying to put a level of abstraction between the main OLTP database and the Data Warehouse load job that uses it for a source.  We are wanting no activity in the DB1 database during this load process



    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz


    quarta-feira, 6 de junho de 2012 18:20

Respostas

  • I'm not sure that removing contention is even a goal. Personally I would say that 2 main goals snapshot provides are:

    1. Have historically consistent copy of the database (reports, historical data, ability to rollback to the snapshot, etc)

    2. Ability to report against secondary server/db when database mirroring is involved.

    Snapshot for sure affects performance - it's expected if SQL need to maintain multiple copies of the data.

    One other thing I forgot to mention - snapshot file does not necessarily empty when you create that. Snapshot needs to be transactionally consistent so SQL Server "rolls back" every active transaction for snapshot (reading transaction log). So pages that affected by active transactions at the moment of snapshot creation would be in snapshot file from the beginning.


    Thank you!

    My blog: http://aboutsqlserver.com

    domingo, 10 de junho de 2012 15:32

Todas as Respostas

  • Read operations on a database snapshot always access the original data pages, regardless of where they reside.
    quinta-feira, 7 de junho de 2012 06:20
  • I dot think that is totally true, a snapshot is empty when created but if pages change in the main database a "before" image of that page is written to the snapshot sparse file so I think it depends on whether you are querying data that has or has not changed


    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz


    quinta-feira, 7 de junho de 2012 18:31
  • If you access the database objects in the snapshot using a 2 part naming convention like select * from dbo.table and you are also connected to the snapshot then you are indeed using the snapshot. If there is a piece of TSQL that has the name of the database specified using a 3 part naming convention like select * from DatabaseName.dbo.table, then you will be accessing the specified database that is burned into the code.  If you execute the SP in the snapshot and inside of that SP the select statement is pointing to the original database, then you will be querying the original database, just like the TSQL specifies. There is not built-in feature that will allow you to "redirect" this type of TSQL. You can build a query that searches for this type of TSQL and programmatically replace all the occurrences. To do so you need to Query sys.sql_dependencies or sys.sql_expression_dependencies DMV. Once you have your code fixed, then you can take the snapshot and work as expected. When using 3 or 4(Using Linked Servers) part name form in TSQL I think is better to use synonyms, because if you need to do this type of changes you can change the synonyms and leave the code alone.
    quinta-feira, 7 de junho de 2012 20:40
  • Alejandro,

    The snapshot file on disk is EMPTY (it shows same size of DB but when you look at actual size on disk it is empty) until data pages change in the DB the snapshot was created from so I would be hitting the underlying data pages from the original database assuming nothing has changed since the snapshot was created, this is whether I am using 2 or 3 part naming conventions.  Only does the sparse file of a snapshot grow when changes occur in the source database and a copy of the page before the change is written to the sparse file.  I believe the answer to my original question,  after much research on this , would be "it depends" on whether the data I am querying had changed since the snapshot had been created



    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz


    domingo, 10 de junho de 2012 04:30
  • Hi Chad,

    Basically, SQL Server has in-memory bitmap for every database file which indicates if page has been changed since snapshot has been created (e.g. if page is already in snapshot). When you query snapshot database, Buffer Pool analyzes those bitmaps and pick correct page either from snapshot or from database itself (again, depend on if page is already in snapshot).

    One of the things to keep in mind - you can have multiple copies of the page in buffer pool so be careful in case if memory is the issue.


    Thank you!

    My blog: http://aboutsqlserver.com

    domingo, 10 de junho de 2012 14:28
  • Hi Dmitri -

    Thanks for the insight.  It sounds like if the goal of having the snapshot in the first place is to remove contention from the source database then that is not how it works as there will still be contention on the underlyiong data pages in the source database



    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    domingo, 10 de junho de 2012 15:18
  • I'm not sure that removing contention is even a goal. Personally I would say that 2 main goals snapshot provides are:

    1. Have historically consistent copy of the database (reports, historical data, ability to rollback to the snapshot, etc)

    2. Ability to report against secondary server/db when database mirroring is involved.

    Snapshot for sure affects performance - it's expected if SQL need to maintain multiple copies of the data.

    One other thing I forgot to mention - snapshot file does not necessarily empty when you create that. Snapshot needs to be transactionally consistent so SQL Server "rolls back" every active transaction for snapshot (reading transaction log). So pages that affected by active transactions at the moment of snapshot creation would be in snapshot file from the beginning.


    Thank you!

    My blog: http://aboutsqlserver.com

    domingo, 10 de junho de 2012 15:32
  • Agreed and I am familiar with those concepts, what they are trying to accomplish is the Data Warehouse team is not allowed to hit the production database directly to source its loads, so I was exploring the option of using a snapshot just for the duration of the warehouse load, but it appears they would be indeede be hitting the production database at the lowest level.  Any other ideas for the goal they are trying to accomplish?

    Thanks

    Chad



    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    domingo, 10 de junho de 2012 15:36
  • Why do you have that restriction? Is this because of performance, security or other reasons? If the reason is performance, snapshot is for sure not the best option - it would introduce bigger overhead than accessing the database directly.

    To be honest, only option I see besides using backup is to load DW from secondaries - either SQL Server ones (servers involved in HA/DR solutions) or through hardware ones (SAN replication, etc).

    At the end I'd say the best bet is to talk to OLTP team and ask them how would they suggest to load the data in case if you don't have access to the data. Perhaps even have it escalated to the management level. That is the case why we need management for :)



    Thank you!

    My blog: http://aboutsqlserver.com

    domingo, 10 de junho de 2012 15:53
  • It was a client requirement, I already pleaded my case to let them access it directly.  It was for performance reasons.  The problem with secondaries is they are running SQL 2008 and mirroring only provides readability if a snapshot is created so that may be an option and log shipping will not work as it disconnects all clients during the log restore.  They are looking at 2012 but have not made any decisions as of yet.

    I want to thank you for your input I believe I have the information I need to help them design a solution



    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    domingo, 10 de junho de 2012 15:57