Minimizing Deadlocks

Deadlocks cannot be completely avoided, following certain coding conventions can minimize the chance of generating a deadlock. Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

• Rolled back, undoing all the work performed by the transaction.
• Resubmitted by applications because they were rolled back when deadlocked.

To help minimize deadlocks try to follow the below steps:

• Access objects in the same order. If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. Using stored procedures for all data modifications can standardize the order of accessing objects.

• Avoid user interaction in transactions. Avoid writing transactions that include user interaction, because the speed of batches running without user intervention is much faster than the speed at which a user must manually respond to queries, such as replying to a prompt for a parameter requested by an application. This degrades system throughput because any locks held by the transaction are released only when the transaction is committed or rolled back. Even if a deadlock situation does not arise, other transactions accessing the same resources are blocked while waiting for the transaction to complete.

• Keep transactions short and in one batch. A deadlock typically occurs when several long-running transactions execute concurrently in the same database. The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.  Keeping transactions in one batch minimizes network roundtrips during a transaction, reducing possible delays in completing the transaction and releasing locks.

• Use a lower isolation level.  Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level, such as serializable. This reduces locking contention.

• Use a row versioning-based isolation level. Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning. Use snapshot isolation. Snapshot isolation also uses row versioning, which does not use shared locks during read operations. Before a transaction can run under snapshot isolation, the ALLOW_SNAPSHOT_ISOLATION database option must be set ON. Implement these isolation levels to minimize deadlocks that can occur between read and write operations.

• Use bound connections. Using bound connections, two or more connections opened by the same application can cooperate with each other. Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa. Therefore they do not block each other.

 

How SQL Server selects deadlock victim ?

Deadlocks arise when two sessions are both waiting for access to resources locked by the other. When an instance of SQL Server detects that two sessions are deadlocked, it resolves the deadlock by choosing one of the sessions as a deadlock victim. The current transaction of the victim is rolled back and deadlock error message 1205 is returned to the client.

SQL Server selects the deadlock victim based on the following criteria:

1. Deadlock priority – the assigned DEADLOCK_PRIORITY of a given session determines the relative importance of it completing its transactions, if that session is involved in a deadlock. The session with the lowest priority will always be chosen as the deadlock victim.
2. Rollback cost – if two or more sessions involved in a deadlock have the same deadlock priority, then SQL Server will choose as the deadlock victim the session that has lowest estimated cost to roll back.

In simple words the lock monitor picks the deadlock victim based, firstly, on the setting of DEADLOCK_PRIORITY for each session and, secondly (in the event of a tie) on the amount of work that it will take to roll back each of the open transactions.

DEADLOCK_PRIORITY in SQL Server

Locks are unavoidable but we can manage deadlocks. The deadlock_priority tells SQL Server how to react in case of a deadlock.

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH |  | @deadlock_var | @deadlock_intvar }

 ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }

LOW
Specifies that the current session will be the deadlock victim if it is involved in a deadlock and other sessions involved in the deadlock chain have deadlock priority set to either NORMAL or HIGH or to an integer value greater than -5. The current session will not be the deadlock victim if the other sessions have deadlock priority set to an integer value less than -5. It also specifies that the current session is eligible to be the deadlock victim if another session has set deadlock priority set to LOW or to an integer value equal to -5.

NORMAL
Specifies that the current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to HIGH or to an integer value greater than 0, but will not be the deadlock victim if the other sessions have deadlock priority set to LOW or to an integer value less than 0. It also specifies that the current session is eligible to be the deadlock victim if another other session has set deadlock priority to NORMAL or to an integer value equal to 0. NORMAL is the default priority.

HIGH
Specifies that the current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to an integer value greater than 5, or is eligible to be the deadlock victim if another session has also set deadlock priority to HIGH or to an integer value equal to 5.

<numeric-priority>
Is an integer value range (-10 to 10) to provide 21 levels of deadlock priority. It specifies that the current session will be the deadlock victim if other sessions in the deadlock chain are running at a higher deadlock priority value, but will not be the deadlock victim if the other sessions are running at a deadlock priority value lower than the value of the current session.

@ deadlock_var
Is a character variable specifying the deadlock priority. The variable must be set to a value of ‘LOW’, ‘NORMAL’ or ‘HIGH’. The variable must be large enough to hold the entire string.

@ deadlock_intvar
Is an integer variable specifying the deadlock priority. The variable must be set to an integer value in the range (-10 to 10).

Deadlocks in SQL Server

A common issue with SQL Server is deadlocks.  A deadlock occurs when two or more processes are waiting on the same resource and each process is waiting on the other process to complete before moving forward.  When this situation occurs and there is no way for these processes to resolve the conflict, SQL Server will choose one of processes as the deadlock victim and rollback that process, so the other process or processes can move forward.

By default when this occurs, your application may see or handle the error, but there is nothing that is captured in the SQL Server Error Log or the Windows Event Log to let you know this occurred.  The error message that SQL Server sends back to the client is similar to the following:

Msg 1205, Level 13, State 51, Line 3
Transaction (Process ID xx) was deadlocked on {xxx} resources with another process 
and has been chosen as the deadlock victim. Rerun the transaction.

How do we monitor deadlocks?

Process	Comments
Windows Performance Monitor Object: SQLServer:Locks Counter: Number of Deadlocks/sec Instance: _Total	This provides all the deadlocks that have happened on your server since the last restart.  We can look at this counter using the following SQL Statement: SELECT cntr_value AS NumOfDeadLocks FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Locks' AND counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total'
Trace Flags1204 and 1222	Trace flag 1204 has existed since at least SQL Server 2000.  And Trace Flag 1222 was introduced in SQL Server 2005.Both output Deadlock Information to the SQL Server ERRORLOG.
SQL Server Profiler/Server Side TraceEvent Class: LocksEvent Name: Deadlock Graph	Gives an XML Graph like the example above.  Very easy to read and figure out what is going on.
Extended Events	The new way to do monitoring in SQL Server.  Extended Events are eventually going to replace the SQL Server Profiler all together (i.e. SQL Server Profiler is on the Deprecated Feature list).  It produces the same XML graph as SQL Server Profiler, and is lighter in performance impact
System Health	This is a “new” default trace, but it’s unlike the default trace that had limited the amount of information being tracked and could not be modified.  We can modify a system health definition, which is built on a Extended Events.  But unlike the default trace, the System Health tracks the Deadlocks that have occurred recently.  So we can pull this information from the System Health for analysis instead of implementing our own Extended Events monitoring.

What is SQL Azure ?

SQL Azure is Microsoft’s cloud database service. Based on SQL Server database technology and built on Microsoft’s Windows Azure cloud computing platform, SQL Azure enables organizations to store relational data in the cloud and quickly scale the size of their databases up or down as business needs change.

It’s the only database as a service that scales on-the-fly without downtime and helps you efficiently deliver multitenant apps ultimately giving you more time to innovate and accelerating your time to market. SQL Database’s built-in intelligence quickly learns your app’s unique characteristics and dynamically adapts to maximize performance, reliability, and data protection. You can build secure apps and connect to SQL Database using the languages and platforms you prefer.

• No upfront cost
• No termination fees
• Pay only for what you use
• Per hour billing

You can try out Azure for free https://azure.microsoft.com/en-us/free/

List SQL Logins with expiration ON

To list disabled Logins, Policy check and expiration for SQL Logins use the below queries.

SELECT name, is_disabled, is_policy_checked
FROM sys.sql_logins
WHERE is_policy_checked = 0
ORDER BY name;

select name from sys.sql_logins 
where is_expiration_checked = 1

Search for string in SSIS Package

The below query tells us how to search for a string stored in the MSDB database.  Limitation would be if the package is stored in the file system. It does not search if the package is stored in the file system.

To read the complete content you might need to increase the  XML output size from the default 2 MB to unlimited.

SELECT [name] AS SSISPackageName
, CONVERT(XML, CONVERT(VARBINARY(MAX), packagedata)) AS SSISPackageXML
FROM msdb.dbo.sysdtspackages90
WHERE CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), packagedata)) LIKE '%@gmail.com%'

msdb Database

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail. The sizes of msdb files may vary slightly for different editions of SQL Server Database Engine.

The following operations cannot be performed on the msdb database:

• Changing collation. The default collation is the server collation.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Setting the database to OFFLINE.
• Setting the primary filegroup to READ_ONLY.

To know more about other System Databases Click Here

Last Execution time of Stored Procedure

How can I know when was my Stored Procedure last executed?

This information is stored in a DMV in #SQL Server. But this information goes as far as the last server restart. i.e, after a SQL Server restart, this information is cleared.

USE DBName
GO
SELECT 
 O.name,
 PS.last_execution_time
FROM 
 sys.dm_exec_procedure_stats PS 
INNER JOIN sys.objects O 
 ON O.[object_id] = PS.[object_id] 
GO

P.S. Please replace the DBName with the actual name of the Database.

Wait Stats Cont..

For more information on the wait conditions firstly please refer this post

The below mentioned are basic explanations of each of the major wait types. Appearance of any of these wait types high up will certainly help direct your subsequent investigations.

CXPACKET

Often indicates nothing more than that certain queries are executing with parallelism; CXPACKET waits in the server are not an immediate sign of problems, although they may be the symptom of another problem, associated with one of the other high value wait types in the instance

SOS_SCHEDULER_YIELD

The tasks executing in the system are yielding the scheduler, having exceeded their quantum, and are having to wait in the runnable queue for other tasks to execute. This may indicate that the server is under CPU pressure

THREADPOOL

A task had to wait to have a worker bound to it, in order to execute. This could be a sign of worker thread starvation, requiring an increase in the number of CPUs in the server, to handle a highly concurrent workload, or it can be a sign of blocking, resulting in a large number of parallel tasks consuming the worker threads for long periods.

LCK_*

These wait types signify that blocking is occurring in the system and that sessions have had to wait to acquire a lock of a specific type, which was being held by another database session. This problem can be investigated further using the information in the sys.dm_db_index_operational_stats

PAGEIOLATCH_*, IO_COMPLETION, WRITELOG

These waits are commonly associated with disk I/O bottlenecks, though the root cause of the problem may be, and commonly is, a poorly performing query that is consuming excessive amounts of memory in the server. PAGEIOLATCH_* waits are specifically associated with delays in being able to read or write data from the database files. WRITELOG waits are related to issues with writing to log files. These waits should be evaluated in conjunction with the virtual file statistics as well as Physical Disk performance counters, to determine if the problem is specific to a single database, file, or disk, or is instance wide.

PAGELATCH_*

Non-I/O waits for latches on data pages in the buffer pool. A lot of times PAGELATCH_* waits are associated with allocation contention issues. One of the best-known allocations issues associated with PAGELATCH_* waits occurs in tempdb when the a large number of objects are being created and destroyed in tempdb and the system experiences contention on the Shared Global Allocation Map (SGAM), Global Allocation Map (GAM), and Page Free Space (PFS) pages in the tempdb database.

LATCH_*

These waits are associated with lightweight short-term synchronization objects that are used to protect access to internal caches, but not the buffer cache. These waits can indicate a range of problems, depending on the latch type. Determining the specific latch class that has the most accumulated wait time associated with it can be found by querying the sys.dm_os_latch_stats DMV.

ASYNC_NETWORK_IO

This wait is often incorrectly attributed to a network bottleneck. In fact, the most common cause of this wait is a client application that is performing row-by-row processing of the data being streamed from SQL Server as a result set (client accepts one row, processes, accepts next row, and so on). Correcting this wait type generally requires changing the client-side code so that it reads the result set as fast as possible, and then performs processing.

Good article on Performance Tuning SQL Database Performance Tuning for Developers

Another good article for Developers  MetaDapper: Data Mapping and Conversion Made Easy With the Right Tools