Azure SQL Lock Waits Test

Lock wait event occurs when a user requests for a resource that is already locked by another user, forcing the former to wait until the latter releases the lock. Lock wait events on a database need to be minimal. If a lock is held on a resource for too long a time, all other requests will be denied access to that resource, thereby causing critical operations to fail. Moreover, if the number of lock waits grows over time, it will consequently increase the length of the pending requests queue; a long request queue may not only cause the unnecessary erosion of valuable resources, it may also choke the database, thereby significantly impacting the quality of the user experience with the Azure SQL service. It is therefore imperative that the lock wait events are monitored, and issues related to such events immediately brought to the attention of administrators.

This test monitors the lock wait events for each lock type, and promptly alerts administrators to a sudden/steady increase in the number and duration of such events. The lock types which will form the descriptors of this test are discussed below:

Descriptor	Lock Type	Description
S	Shared locks	Shared locks are held on data being read under the pessimistic concurrency model. While a shared lock is being held other transactions can read but cannot modify locked data. After the locked data has been read the shared lock is released, unless the transaction is being run with the locking hint (READCOMMITTED, READCOMMITTEDLOCK) or under the isolation level equal or more restrictive than Repeatable Read.
U	Update locks	Update locks are a mix of shared and exclusive locks. When a DML statement is executed SQL Server has to find the data it wants to modify first, so to avoid lock conversion deadlocks an update lock is used. Only one update lock can be held on the data at one time, similar to an exclusive lock. But the difference here is that the update lock itself can’t modify the underlying data. It has to be converted to an exclusive lock before the modification takes place.
X	Exclusive locks	Exclusive locks are used to lock data being modified by one transaction thus preventing modifications by other concurrent transactions. You can read data held by exclusive lock only by specifying a NOLOCK hint or using a read uncommitted isolation level. Because DML statements first need to read the data they want to modify you’ll always find Exclusive locks accompanied by shared locks on that same data.
I	Intent locks	Intent locks are a means in which a transaction notifies other transaction that it is intending to lock the data. Thus the name. Their purpose is to assure proper data modification by preventing other transactions to acquire a lock on the object higher in lock hierarchy. What this means is that before you obtain a lock on the page or the row level an intent lock is set on the table. This prevents other transactions from putting exclusive locks on the table that would try to cancel the row/page lock.
Sch	Schema locks	There are two types of schema locks: Schema stability lock (Sch-S): Used while generating execution plans. These locks don’t block access to the object data. Schema modification lock (Sch-M): Used while executing a DDL statement. Blocks access to the object data since its structure is being changed.
SIX	Shared with Intent Exclusive	A transaction that holds a Shared lock also has some pages/rows locked with an Exclusive lock
SIU	Shared with Intent Update	A transaction that holds a Shared lock also has some pages/rows locked with an Update lock
UIX	Update with Intent Exclusive	A transaction that holds an Update lock also has some pages/rows locked with an Exclusive lock

Target of the test : A Microsoft Azure SQL database

Agent deploying the test : A remote agent

Outputs of the test : One set of results for each lock wait type on the Azure SQL database that is configured for monitoring

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.
Database Name	Specify the name of the Azure SQL database that is to be monitored.
User Name and Password	Against the User Name and Password parameters, specify the credentials of the user who is vested with DBOWNER rights to the configured Database Name.
Confirm Password	Confirm the specified Password by retyping it here.
SSL	If the Azure SQL database service being monitored is SSL-enabled, then set the SSL flag to Yes. If not, then set the SSL flag to No.
Domain	By default, none is displayed in this text box. If the ‘SQL server and Windows’ authentication has been enabled for the Azure SQL database being monitored, then the Domain parameter can continue to be none. On the other hand, if ‘Windows only’ authentication has been enabled, then, in the Domain text box, specify the Windows domain in which the monitored database exists. Also, in such a case, the User Name and Password that you provide should be that of a 'domain user' with DBOWNER rights to the configured Database Name.
IS NTLMv2	In some Windows networks, NTLM (NT LAN Manager) may be enabled. NTLM is a suite of Microsoft security protocols that provides authentication, integrity, and confidentiality to users. NTLM version 2 (“NTLMv2”) was concocted to address the security issues present in NTLM. By default, this flag is set to No, indicating that NTLMv2 is not enabled by default for the target Microsoft Azure SQL database. Set this flag to Yes if NTLMv2 is enabled for the target database.
Detailed Diagnosis	To make diagnosis more efficient and accurate, the eG Enterprise embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option. The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled: The eG manager license should allow the detailed diagnosis capability Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.

Measurements made by the test
Measurement	Description	Measurement Unit	Interpretation
Current lock waits	Indicates the current number of lock wait events for this lock type.	Number	Ideally, this value should be very low. A high value or a consistent increase in the value may choke the database and severely hamper its overall performance. Therefore, if the value of this measure is high, you might first need to identify what is causing the lock waits. For this purpose, you can use the detailed diagnosis of this measure. The detailed diagnosis leads you to the exact object the lock events are waiting on, the user who holds a lock on that object, and the query that initiated the lock. This way, inefficient queries can be identified and fine-tuned. Given below are some tips for minimizing lock waits: Keep all Transact-SQL transactions as short as possible. To reduce the amount of time that tables are locked, which hurts concurrency and performance, avoid interleaving reads and database changes within the same transaction. Instead, try to do all your reads first, then perform all of the database changes (UPDATES, INSERTS, DELETES) near the end of the transaction. This helps to minimize the amount of time that exclusive locks are held. Use clustered indexes on heavily used tables. Make appropriate use of non-clustered indexes Try to avoid Transact-SQL statements that affect large numbers of rows at once, especially the INSERT and UPDATE statements. Try to have your UPDATE and DELETE statements use an index. When using nested transactions, avoidcommit and rollback conflicts.
Avg. wait time for locks	Indicates the duration of the lock wait events for this lock type.	Milliseconds	Ideally, this value should be very low. A high value or a consistent increase in the value may choke the database and severely hamper its overall performance. Therefore, if the value of this measure is high, you might first need to identify what is causing the lock waits. For this purpose, you can use the detailed diagnosis of the Current lock waits measure. The detailed diagnosis leads you to the exact object the lock events are waiting on, the user who holds a lock on that object, and the query that initiated the lock. This way, inefficient queries can be identified and fine-tuned.