April 9, 2014 at 4:37 am
Hi,
Do we have any specific mechanism or tools that can predict the dead lock that's will be going to occur in database.
Thanks
April 9, 2014 at 5:07 am
No.
Detecting that they have occurred is easy. Predicting that they may would require massive, in-depth analysis of every piece of code, how it runs, how often it runs and even then would likely to be little more than an educated guess.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2014 at 5:59 am
Prediction? No. But following coding best practices to write queries such that they access objects in the same order, that they don't have major performance issues, that they use indexes in place, that you have the right indexes so that performance is high, all this can prevent deadlocks. Remember, at their core, deadlocks are performance problems. Tuning your system is one of the best things you can do to prevent deadlocks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 10, 2014 at 8:49 pm
Thanks
April 10, 2014 at 10:46 pm
Grant Fritchey (4/9/2014)
Prediction? No. But following coding best practices to write queries such that they access objects in the same order
How does this come about in real life? I ask because I've never seen anywhere do it. How does it get coordinated over a group of programmers?
Is it because they are all using the same set of web service APIs to pull things in a certain order, and the database developers have some kind of documentation on the ordering... or something else?
Most of the places I've worked, things just became so large, you couldn't organize something like this without serious redevelopment and no place or tools to start. I can't imagine how others do it and sustain it.
April 11, 2014 at 1:30 am
Cody K (4/10/2014)
How does it get coordinated over a group of programmers?
You put it in the coding standard, along with all your other good, required coding practices and require people to follow it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2014 at 2:04 am
GilaMonster (4/11/2014)
You put it in the coding standard, along with all your other good, required coding practices and require people to follow it.
So you have a document with your 1000-odd tables in it, and a list of categories. Need Table A? Get it in the order of Table B, C, D, then A. Need Table E? Get it in the order of Table C D E.
Are you serious?
April 11, 2014 at 2:26 am
Huh???
If you need Table A, you access table A. If you need table E, then access table E. Why would you ever access tables B, C and D if all you want is Table A?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2014 at 3:52 am
Cody K (4/10/2014)
Grant Fritchey (4/9/2014)
Prediction? No. But following coding best practices to write queries such that they access objects in the same orderHow does this come about in real life? I ask because I've never seen anywhere do it. How does it get coordinated over a group of programmers?
Is it because they are all using the same set of web service APIs to pull things in a certain order, and the database developers have some kind of documentation on the ordering... or something else?
Most of the places I've worked, things just became so large, you couldn't organize something like this without serious redevelopment and no place or tools to start. I can't imagine how others do it and sustain it.
I've seen it work and I've seen it break down. The main thing that makes it work in real life is something that certain programmers hate, stored procedures. A common access point to the data makes a huge difference. But, you have to have communication and coordination across teams. When that goes out the window, this isn't the only problem you'll see. Also, generated code from ORM tools can be especially painful. Solutions go back to communication and coordination as well as using stored procedures where appropriate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 11, 2014 at 4:13 am
Actually we have 1000 or more procs and it is not an easy task to debug all and find out where person has not used proper coding guidelines
I was thinking if we collect all running database objects and their related objects and check whether related database objects are moving from idle to running and what are things that are common on both objects which can block each other? Like below
Keep all running objects on table and related object on another table
1. When running object move to idle, remove it all entries from both the table
2. When related objects move from idle to running, check what are things which can block on both running objects.
It will have generic solution which we can work for all objects.. just thinking what would be your feedback?
April 11, 2014 at 4:55 am
You want to do this live against your database? In short, you're building an additional locking and blocking mechanism on top of SQL Server (assuming I understand what you're suggestion). No. I think that's a horrifically bad idea. You could gather query metrics after the fact (extended events) and then build out relationship mappings that way, but I wouldn't audit the queries live to try to track locking.
The real question is, fine, you have 1000s of procedures. How many are deadlocking? 10, 50, 300? Instead of trying to focus on the 1000 procedures and do something useless (and probably harmful) for 700 of them, I'd just focus on the 300.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 11, 2014 at 3:56 pm
First properly tune the indexes, in particular getting the correct clustering index (hint: very often this means not clustering on an identity column). That is usually the single biggest overall performance factor.
Only after that does it make sense to go into more detail on the remaining problem areas.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply