Avoiding deadlocks

The following are some of the techniques you can use to avoid a deadlock:

Access resources in the same physical order => the first transaction will successfully acquire locks on the resources without being blocked by the second transaction.

Decrease the locking
• Convert a nonclustered index to a clustered index.
• Use a covering index for a SELECT statement.

Minimize lock contention
• Implement row versioning through the READ_COMMITTED_SNAPSHOT isolation level or through the SNAPSHOT isolation level.
• Decrease the isolation level.
• Use locking hints: NOLOCK or READUNCOMMITTED.

Tips to avoid database blocking

In a multiuser database application, you must minimize blocking among concurrent transactions.

Keep transactions short
• Perform the minimum steps/logic within a transaction.
• Do not perform costly external activity within a transaction, such as sending acknowledgment email or performing activities driven by the end user.

Optimize queries using indexes
• Create indexes as required to ensure optimal performance of the queries within the system.
• Avoid a clustered index on frequently updated columns. Updates to clustered index key columns require locks on the clustered index and all nonclustered indexes (since their row locator contains the clustered index key).
• Consider using a covering index to serve the blocked SELECT statements.

Consider partitioning a contended table

Use query timeouts or a resource governor to control runaway queries

Avoid losing control over the scope of the transactions because of poor error-handling routines or application logic
• Use SET XACTABORT ON to avoid a transaction being left open on an error condition within the transaction.
• Execute the following SQL statement from a client error handler (TRY/CATCH) after executing a SQL batch or stored procedure containing a transaction:


Use the lowest isolation level required
• Use the default isolation level (Read Committed).
• Consider using row versioning to help reduce contention.

Blocking resolutions

Once you’ve analyzed the cause of a block, the next step is to determine any possible resolutions. Here are a few techniques you can use to do this:

Use a covering index on the contended data If the query of one of the processes can be satisfied using a covering index, then it will prevent the process from requesting locks on the contended resource.
Optimize the queries executed by blocking and blocked SPIDs E.g. break UPDATE/DELETE statements into multiple batches using the appropriate WHERE clauses. If the individual statements of the batch are executed in separate transactions, then fewer locks will be held on the resource within one transaction, and for shorter time periods.
Decrease the isolation level Use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED or WITH (NOLOCK) locking hint.
Partition the contended data This way, exclusive locks will be acquired at partition level, so it will increase concurrency.

Using Indexes Effectively in SQL Server

Adding indexes on a specific table doesn't guarantee you the queries will use these indexes effectively. These are some of the query design rules you should follow to improve the use of indexes:

Avoid nonsargable search conditions
A sargable predicate in a query is one in which an index can be used.

Sargable =, >, >=, <, <=
LIKE 'text%'
Nonsargable <>, !=
LIKE '%text'

Avoid arithmetic operators on the WHERE clause column
For example, assuming an index has been created on NoWeeks column, the use of the multiplication operator column in the first query prevents the optimizer from using (choosing) the index on the column:

-- the index will not be used
WHERE NoWeeks * 7 > @NoDays

-- the index will be used
WHERE NoWeeks > @NoDays / 7

Avoid functions on the WHERE clause column

-- the index will not be taken into account
WHERE SUBSTRING(CustomerName, 1, 1) = 'F' ;

-- the index will be taken into account
WHERE CustomerName LIKE 'F%' ;

DMVs used to analyze the execution plan cache

You can obtain a lot of information about the execution plans in the procedure cache by accessing various dynamic management objects.

sys.dm_exec_cached_plans: obtain information about the execution plans in the plan cache.

sys.dm_exec_query_plan(plan_handle): retrieve the XML representation of the plan.

sys.dm_exec_sql_text(plan_handle): retrieve the original query text.

sys.dm_exec_query_stats: get aggregate performance metrics about the cached plan.

sys.dm_exec_requests: see execution plans for queries that are currently executing.

Index fragmentation resolutions in SQL Server

After you detected fragmentation of an index, you can "resolve" fragmentation in an index by rearranging the index rows and pages so that their physical and logical orders match.

You achieve this through the following techniques:
1. Dropping and re-creating the index
If the index being dropped is a clustered index, then all the nonclustered indexes on the table have to be rebuilt after the cluster is dropped.

2. Creating the index with the DROP_EXISTING clause

3. Executing the ALTER INDEX REBUILD statement on the index
To be performed in case the fragmentation is over 30%.

ALTER INDEX <IX_IndexName> ON <TableName>

or rebuild all indexes of a specified table:


4. Executing the ALTER INDEX REORGANIZE statement on the index
To be performed in case the index fragmentation is between 5% and 30%

ALTER INDEX <IX_IndexName> ON <TableName>

Characteristics of Four Defragmentation Techniques

Blocking High High Medium Low
Defragment index with constraints Highly complex Moderately Easy Easy
Defragment multiple indexes together No No Yes Yes
Degree of defragmentation High High High Moderate to low
Statistics are updated Yes Yes Yes No
Apply new fill factor Yes Yes Yes No

Factors affecting index performance in SQL Server

Indexes and query performance are strongly related. It’s essential to ensure that the factors that affect the efficiency of an index are optimal:

  • Fill Factor
  • Statistics
  • Fragmentation
  • I/O Sub-System
  • Compression

Performance Monitor counters to analyze network bottleneck

Object Counter Description Expected Value
Network Interface (Network card) Bytes Total/sec Rate at which bytes are transferred on the network adapter. Compare this value with that reported by the Network Interface\Current Bandwidth performance counter, which reflects each adapter’s bandwidth. Average value < 50% of NIC capacity
Network Segment % Net Utilization Percentage of network bandwidth in use on a network segment Average value < 80% of network bandwidth

Performance Monitor counters to analyze processor bottleneck

Counter Description Expected Value
Processor (_Total)%
Processor Time Percentage of time processor was busy Average value < 80%
% Privileged Time percentage of processor time spent in privileged mode Average value < 10%
Processor Queue Length Number of requests outstanding on the processor Average value < 2
Context Switches/sec Rate at which processor is switched per processor from one thread to another Average value < 2,000
SQL Server:SQL Statistics
Batch Requests/sec SQL command batches received per second Based on your standard workload
SQL Compilations/sec Number of times SQL is compiled Based on your standard workload
SQL Recompilations/sec Number of Recompiles

Performance Monitor counters to analyze disk bottleneck

Counter Description Expected Value
% Disk Time Percentage of time disk was busy with read/write activities. Average value < 85%
Current Disk Queue Length Number of outstanding disk requests at the time performance data is collected Average value < 2 per disk
Avg. Disk Queue Length Average number of queued disk requests during the sample interval Average value < 2 per disk
Disk Transfers/sec Rate of read/write operations on disk Maximum value < 400 per disk
Disk Bytes/sec The rate at which bytes are transferred to or from the disk during read or write operations.
If the amount of data transfer exceeds the capacity of the disk subsystem, then a backlog starts developing on the disk subsystem, as reflected by the Disk Queue Length counter.
Maximum value < 800MB per second
Avg. Disk Sec/Read Average time in ms to read from disk Average value < 10 ms
Avg. Disk Sec/Write Average time in ms to write to disk Average value < 10 ms


Subscribe to RSS - sql-server