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
- Consider optimizing the Select statements used for dimension processing
- Use SQL Server Profiler and then analyze the trace offline
- Add more physical memory to the computer
- Avoid processing at peak hours to avoid query and processing collisions
- Avoid having, in different batches running in parallel, both dimension and partition processing on the same database
- 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)