How to resolve Locks and Deadlocks….?(4/4)

By our last post, we have addressed well the common pain of lots o DBA , DB analyst “Heavy locks ” and per this blog , we are going to
give a deep insight on the other 2 pains below:

1- How to address deadlocks …?
2- What to do with heavy locks /deadlocks resulted by Ad-hoc queries …?

1- How to resolve deadlocks …….?
Fundamentally most of resolutions explained for Heavy locks apply by the same for deadlocks , but here we have additional resolutions such as:

• You can determine the deadlock priority of some transactions/queries rather than others for the wisdom of boosting the priority of some transactions /queries rather than others using the command

Set Deadlock_priority low ;
Set Deadlock_priority normal;
Set Deadlock_priority high;

Or you can base on numerical scale from -10 to 10 like below

Set Deadlock_priority -10

Where the value -5 is equivalent to Low , the value 0 is equivalent to Normal and the value 5 is equivalent to High
But bear in mind this isn’t going to remove deadlock circumstance but mintage its impact by boosting the priority of critical mission transactions and neglecting others not important like for example DWH transactions ,scheduled business jobs…..etc

• Reduce concurrency between OLTP transactions as much as you can as for example , you can specify different time slots of DWH ( Data warehousing solutions ) out of rush hours not to reflow with other OLTP transactions of end users .

• When contentions are increasing much between OLTP transactions , you have to look for a root resolution such as Performance optimizations of all of OLTP transactions contributing in deadlock circumstances or implementation of a special transaction isolation level on the entire of DB like transaction isolation level read committed using row versioning (Much recommended)

2- What to do with heavy locks /deadlocks resulted by Ad-hoc queries …?

This is also another frequent question that most of DBA , DB Analysts are still wondering about it since no enough control to do anything with these Ad-hoc queries themselves as they are out of DB scheme and thus out of DB control almost.

Hence, we have no much options but you can try the following approaches by the same sequence :

• Optimizing the performance Ad-hoc queries through index optimizations only since you haven’t
(as DBA ) fair chance to alter T-SQL codes of these Ad-hoc queries.

• If Still persist , you have to seek a comprehensive solution on DB level by applying read committed snapshot isolation level using row versioning which will be beneficial for selects queries

• If still persist , you can work out that solution of remote intercept query execution plan to use some Nolock hint or index hint as follows

Create query execution plan for a certain Query with Index hint

 
 
 
EXEC sp_create_plan_guide 
    @name = N'Select_Guide1', 
    @stmt = N'SELECT TOP (1) 
[Extent1].[AttachmentId] AS [AttachmentId], 
[Extent1].[strImageName] AS [strImageName], 
[Extent1].[imgImageData] AS [imgImageData], 
[Extent1].[ROWGUID] AS [ROWGUID], 
[Extent1].[OperationHijriYear] AS [OperationHijriYear], 
[Extent1].[UserID] AS [UserID]
FROM [dbo].[ArchivedImages] AS [Extent1]
WHERE [Extent1].[AttachmentId] = @p__linq__0',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = N'@p__linq__0 int ', 
    @hints = N'OPTION (TABLE HINT ( [Extent1], INDEX(PK_alchemyImages) ))';
GO

Create query execution plan for a certain Query with Index hints

 
EXEC sp_create_plan_guide 
    @name = N'Query Plan_Guide1', 
    @stmt = N'SELECT TOP (1) 
[Extent1].[AttachmentId] AS [AttachmentId], 
[Extent1].[strImageName] AS [strImageName], 
[Extent1].[imgImageData] AS [imgImageData], 
[Extent1].[ROWGUID] AS [ROWGUID], 
[Extent1].[OperationHijriYear] AS [OperationHijriYear], 
[Extent1].[UserID] AS [UserID]
FROM [dbo].[ArchivedImages] AS [Extent1]
WHERE [Extent1].[AttachmentId] = @p__linq__0',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = N'@p__linq__0 int ', 
    @hints = N'OPTION (TABLE HINT ( [Extent1], nolock))';
GO

It worth much thinking about using sp_create_plan_guide SP which is responsible about creating a specific query execution plans for a certain query , thereby I do recommend to learn more about it at http://msdn.microsoft.com/en-us/library/ms179880.aspx

Moreover , if you want to select all of currently customized query execution plans, you can use the below query :
Search Query Execution plan

select * from sys.plan_guides

And f to alter a certain query execution plan , you have to drop it first and create it again and in order to drop it , you have just to exec the below SP with specifying N’Query Plan_Guide1′ for its parameter

Drop a Query Execution plan

 
EXEC sp_control_plan_guide N'DROP', N'Query Plan_Guide1'

And if needed to do the same for all query execution plans , you can just exe the below one

Drop all Query Execution plans

EXEC sp_control_plan_guide N'DROP all'

Finally , it is good to say heavy locks and deadlocks are just a matter of how far your T-SQL transactions /Queries are powerful to afford stress workload or in other words “Stress Powers”, thereby it worth much thinking about Performance optimization not for just a single run of your queries but also for multiple runs of your queries under stress of some fair number of concurrent users ..

You can learn more for this regard at my series of T-SQL Performance optimizations starting from here

http://www.sqlserverpath.org/2012/01/09/towards-t-sql-queries-of-0-sec_second-part/