Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend Resolving Blocking Issues

Resolving Blocking Issues

MSAS is predominantly used for reading summarized data, so blocking issues shouldn’t be nearly as common as they are within relational database management systems. However, we do use transactions for certain MSAS operations—for example, processing, database restore and synchronization—so blocking can indeed occur on busy servers. MSAS provides the <cancel> XMLA command that you can submit using Activity Viewer or through XMLA to kill the offending connections.

Sadly, the cancel command is not very reliable; even if you manage to kill the connection, often the corresponding session remains “alive” and continues to use server resources. You can detect blocking using the Activity Viewer sample tool or through the $system.discover_locks DMV (LOCK_STATUS of 0 within this DMV indicates blocking), but the only sure way to stop blocking is to restart the service or wait until the offending session finishes its work.

To avoid blocking, try to separate querying and processing activity and schedule backup, restore, and synchronization operations during off peak hours.

  • + Share This
  • 🔖 Save To Your Account