BI performance counter: Current locks

Applies to

SSAS

Description

This performance counter measures current total number of locked objects.

Using ProcessUpdate option generates a lock on all partitions used by the cubes that include the dimension you are processing. The more partitions the cubes have, the more memory pressure they will generate during processing. Consider using a Dynamic Management View to find the SPID associated to long-running Analysis Services query.

Resolved by

DBAs, Server administrators, BI developers

Suggested solutions

  1. Consider optimizing the Select statements used for dimension processing
  2. Use SQL Server Profiler and then analyze the trace offline
  3. Add more physical memory to the computer
  4. Avoid processing at peak hours to avoid query and processing collisions
  5. Avoid having, in different batches running in parallel, both dimension and partition processing on the same database
  6. Reduce memory pressure by reducing the number of dimensions being processed in a single batch, since this will result in a smaller transaction size

Additional research

Why doesn’t SSAS cache the entire cube?
SQL Server Monitoring Counters Important
SSAS The Operation Was Cancelled Because of Locking Conflicts
Performance Counters (SSAS)

 

To track BI performance metrics – consider using ApexSQL BI Monitor, a web application that monitors system, SSAS, SSIS and SSRS performance in real time.

⇐ Back to index