SQL Server 2012 Administration

Hi Experts .
 Database is facing frequent DEADLOCKs which is not good for system performance. I did not take any action (because, I assumed there are some maintenance activity also can produce that kind of error) but I waited for some evidence.
Now tonight I find a DEADLOCK appeared in Database. Its been 4yrs but this dead locki have never seen.
Also I would like to quote
Could you please tell me, how can i check what  "row Versioning" on  MS SQL database is there? If we turn on  row versioning on database ,  can it help to reduce the DEADLOCK issue.
How do i check the isolation level on the database, .Thanks in advance.
SandeepiiiDBAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
You can run the DBCC UserOptions command on any database to get few details about dateformat, datefirst as well isolation level.

DBCC useroptions;

Older links but info still ok to use for SQL 2012 about detecting/fixing deadlocks:
https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx
And one of my favorites:
http://www.brentozar.com/archive/2014/06/capturing-deadlock-information/


And please don't just think by switching isolation level you are "fixing" any deadlock - it may take quite a bit more to actual fix it than hide it, reduce the chance for it to happen, workaround it, etc...

How often you have a Reindex/Refresh Statistics done against that database? Many times this simple regular maintenance helps reducing the chance for deadlocks.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
Database is facing frequent DEADLOCKs which is not good for system performance.
This is a big topic, however, the causality is opposite.  Because of the poor performance code and implemented objects you will have deadlocks.

Most of the time the cause is index related like missing, fragmented and bad designed indexes. Then it could also be due to bad SQL written code like nested loops,  the use of user defined functions where you join multiple tables, which actually translates in the same nested loops problem.

Point is that you have to identify what queries/procedures are executed when the performance issues and deadlock occurs and deal with them one at a time starting with the biggest one.

One thing that you can do immediately is to make sure that all your SELECT statements from tables, joined table in the stored procedures, views, functions code or in the application code if embeded, have the WITH(NOLOCK) hint on them so at least the selects will be fine. You can't use that on tables that are updated/inserted/deleted.

Another thing is to check for the fragmented indexes and make sure you reorganize or rebuild them. For this use Ola Hallegrens's maintenance solution, index optimization part. That is the BEST!

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

When it comes to identify the exact "culprits" for bad performance a starting point is to download and install a FREE monitoring tool that does exactly that: keeps track of your performance bottlenecks looking into the waiting types statistics, which is the most accurate way. It will take a bit to understand how it works but the advantage is that it will pinpoint you th exact and the biggest issue every time. The tool is:

http://www.solarwinds.com/database-monitor.aspx

Don't worry, I don't work for them but from my experience this is the best tool. It actually installs the FULL version and if you don't buy a license after 14 days it will just limit its functions and the history to only 1 hr but still very useful. I am using it for years now since it was called Confio Ignite.

After you install it you look at the bar graph where you hover the mouse on the bottom "bricks", which are the thickest:
ignite_bar_graph.JPGJust that will tell you right away what query and stored procedure is. If you click on the bar you will get into another graph that will list the heaviest queries on the top with the waiting type it generates.:
ignite_bar_graph_2.JPG  
Finally if you click on the bar or on the numbers link next to it you will have the exact query that causes the problem:
ignite_bar_graph_3.JPGGo fix that, be it indexes on the tables or code and then "hunt" for the next. In few days your deadlocks will disappear.

Another thing that you should do is to use in parallel the Adam Mechanic's sp_whoisactive stored procedure that will give you very useful information about current executing processes, with the exact code executed, its query plan and a parameter to show you the deadlocks.. It is one "simple" stored procedure that you create it on your server and then execute it.

Download:
http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

How to use it:
http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

How to use it to log activity:
http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

Getting locking info:
http://sqlblog.com/blogs/adam_machanic/archive/2011/04/19/why-am-i-blocked-a-month-of-activity-monitoring-part-19-of-30.aspx

Good Luck!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.