impact on user queries if cube backup is running RRS feed

  • Question

  • Hi.  We have some pretty good reasons for running a particular AS db backup during working hours.  But we arent emotionally attached to that idea if the impact on users is significant.

    Can the community describe worst case how running a backup during normal business hous can affect users running queries on the same db at the same time? 

    The backup is initiated manually in ssms right now. 

    Thursday, June 14, 2012 3:15 PM


  • A backup is going to generate 2 types of load. 

    1. Disk IO to read the data and write it to the abf. Ideally you should write the backup to a different drive, this split's the IO load and means that you don't loose your backups in the event of a drive failure. So you need to monitor your IO sub-system to make sure it can cope with the additional IO load. SSAS does a pretty good job with it's caching, but the IO load also depends on the size of your database and certain feature usage (like distinct counts) - so you need to measure this in your environment.

    2. There will be some CPU load for the compression of the data. Again you need to monitor this, typically I don't do backups during business hours, but I don't expect the load to be significant. However if your system is already CPU constrained it might be noticeable.

    The biggest issue could be if you are also processing during the day. Then you could run into locking issues where the processing commit operation needs an exclusive write lock while the backup is holding a read lock. This could result in the backup or the processing operation rolling back depending on your configuration of either the CommitTimeout or the ForceCommitTimeout settings. The default configuration would be to cancel anything holding a read lock after 30 seconds to allow the processing operation to commit.

    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by db042188 Friday, June 15, 2012 12:27 PM
    Friday, June 15, 2012 1:07 AM