BI performance counter: Current lock waits

Applies to

SSAS

Description

This performance counter measures current total number of client connections waiting for a lock to be released by the Analysis Services engine.

To get a complete list of current lock-waits then make use of Dynamic Management Views (DMVs). DMV queries are an alternative to running XML/A Discover commands.

You must have system administrator permissions on the Analysis Services instance to query a DMV.

Resolved by

DBAs, Server administrators, BI developers

Suggested solutions

  1. Use Dynamic Management Views (DMVs) to Monitor Analysis Services current lock wait stats
  2. Consider optimizing the Select statements used for dimension processing
  3. Use SQL Server Profiler and then analyze the trace offline
  4. Add more physical memory to the computer
  5. Avoid processing at peak hours to avoid query and processing collisions

Additional research

CommitTimeout and ForceCommitTimeout in SSAS
Performance Counters (SSAS)
General monitoring for SQL Server Analysis Services using Performance Monitor
Why doesn’t SSAS cache the entire cube?

 

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