SQL Analysis Locks Test

A lock is used to “lock” some data so that only one user/session may update that particular data. Locks exist to prevent two or more users from updating the same exact piece of data at the same exact time.

Though locking helps maintain the integrity of data, it can adversely impact user experience. This can happen when users end up waiting for a long time to access data that is locked by another user/session. Another condition that can significantly degrade SSAS performance is a deadlock. This is a situation in which two or more transactions are waiting for one another to give up locks.

To promptly detect and resolve such lock waits and deadlocks, administrators must closely monitor the locking activity on SSAS. This can be achieved using the SQL Analysis Locks test!

This test monitors the locking activity on an SSAS instance, and promptly captures deadlock situations and abnormal lock waits.

Target of the test : A Microsoft SQL Analysis Services Server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for the SSAS server being monitored

Configurable parameters for the test
Parameters	Description
Test Period	How often should the test be executed.
Host	The host for which the test is to be configured.
Port	The port at which the specified Host listens.
Instance	The name of a specific SQL Analysis Services Server instance to be monitored. The default value of this parameter is “default”. However, if the Microsoft SQL Server hosting SSAS uses named instances, then do the following: Configure the Instance parameter with the name of the SQL Server instance that hosts SSAS. Do not change the default value of the Port parameter
SSAS Version	Specify the SSAS version being monitored. By default, 11 is displayed here, indicating that version 11 of SSAS is monitored by default.

Measurements made by the test
Measurement	Description	Unit	Interpretation
Lock waits	Indicates the rate of lock waits.	Number/Sec	These are lock requests that could not be given immediate lock grants and were put in a wait state. A low value is desired for this measure.
Deadlocks	Indicates the total number of deadlocks detected.	Number	Ideally, the value of this measure should be 0. If this measure reports a high value, you may want to consider using Dynamic Management Views (DMVs) to get a complete list of deadlocks. You must have system administrator permissions on the Analysis Services instance to query a DMV. Consider disabling proactive caching Consider accessing database snapshot on isolation mode Setup extended events trace to monitor deadlocks Use SQL Server Profiler and then analyze the trace offline Avoid processing at peak hours to avoid query and processing collisions.
Current locks	Indicates the current number of locked objects.	Number	A low value is desired for this measure.
Current latch waits	Indicates the current number of threads waiting for a latch.	Number	A latch is a short-term synchronization object that ensures data integrity on objects in SSAS memory. A latch wait is a delay associated with the latch, and is often caused by the I/O system not keeping up with requests so it is taking a long time to get pages from disk into memory. Since latch waits can adversely impact user experience with SSAS, the value of this measure should be 0 ideally.
Current lock waits	Indicates the number of clients currently waiting for a lock.	Number	The value 0 is desired for this measure.