locked
Locking Issues RRS feed

  • Question

  • Hi All,

    I have been asked to make a suggestion on reducing locking issues and so would appreciate all the advices and suggestions that you give me.

    The application is kind of a reporting application and so its not an OLTP system. But data is being refreshed from an Oracle Datawarehouse on a daily basis. At first the refresh job was scheduled to run during day times but due to locking issues during the refresh process, it was scheduled to run on a nightly basis. The business is okay even if the data displayed is 1-2 days old. But now an attempt is being made to run the refresh process in the morning and so locking has to be taken care of. The developers came up with a suggestion that the database be altered to turn on Snapshot Isolation.

    I read about this feature and the greatest concern is the tempdb growth because on an average the process updates about 10,000 records. And multiple updates on a single record can also happen at times. And that there is no control on how long the row versions in the temp log files will be retained is the scary part.

    1) Is Snapshot Isolation with READ COMMITTED (READ_COMMITTED_SNAPSHOT) a good idea? If not, could you please suggest some other ways we can reduce or avoid locking?

    2) If Snapshot isolation is turned on, will running a transaction log backup every 20 - 30 minutes during the process help restrict temp log file growth?

    3) Will turning on the option on a transaction-level help? (Turn on before process and Turn off after process)

    4) Wouldn't the process slow down even more now that a new row version is being created each time in the tempdb?

    The application runs on a SQL Server 2005 clustered environment. Thanks in advance for all your suggestions. I get most of my concerns clarified out of these forums.

    Thursday, June 10, 2010 2:02 PM

Answers

  • If that is reporting application on some degree I would not with Snapshot Isolation ...Perhaps it worth to enable read only option to the database and change it if you go to refresh the data?

    What kind of locking do you have? Do you transfer completely all the data all just some part of the data, I mean, perhaps it is possible to have another database for refresh and just reassign the users later on...?

    Yes, for  Snapshot Isolation level  you will have to care of tempdb database, there are plenty of links on internet on how to work with tempdb in SQL Server 2005..

     

     

    Sunday, June 13, 2010 9:37 AM

All replies

  • If that is reporting application on some degree I would not with Snapshot Isolation ...Perhaps it worth to enable read only option to the database and change it if you go to refresh the data?

    What kind of locking do you have? Do you transfer completely all the data all just some part of the data, I mean, perhaps it is possible to have another database for refresh and just reassign the users later on...?

    Yes, for  Snapshot Isolation level  you will have to care of tempdb database, there are plenty of links on internet on how to work with tempdb in SQL Server 2005..

     

     

    Sunday, June 13, 2010 9:37 AM
  • If that is reporting application on some degree I would not with Snapshot Isolation ...Perhaps it worth to enable read only option to the database and change it if you go to refresh the data?

    What kind of locking do you have? Do you transfer completely all the data all just some part of the data, I mean, perhaps it is possible to have another database for refresh and just reassign the users later on...?

    Yes, for  Snapshot Isolation level  you will have to care of tempdb database, there are plenty of links on internet on how to work with tempdb in SQL Server 2005..

     

     

    Sunday, June 13, 2010 9:37 AM