April 20, 2016 at 8:36 am
Hi, I am looking for some help on solving deadlock issues. Please see below scenario..
There are frequent SELECT, UPDATE, BULK INSERTS happening in DatabaseA- Tables and Heavy blocking is happening and every time BULK INSERT is chosen as deadlock victim and we are loosing some data because of BULK INSERT is failing.
SELECT is Blocking BULK INSERT or UPDATE is blocking BULK INSERT every time or vice versa and all these Statements(queries) are coming from Application, NOT any STORED PROCEDURES( unfortunately we cannot use Stored procedures)
Database Snapshot is READ Committed, Simple recovery model, Properly indexed and No fragmentation found, Statistics are up to date, MAXDOP is properly set,optimize for ad hoc workloads=1,AUTO_UPDATE_STATISTICS_ASYNC ON,Auto-growth is 500mb for mdf and ldf, tempdb auto growth is 500mb for mdf and ldf ..btw, all databases are sitting on SAME DISK, Memory is 6GB, Total databases Size if 60GB only, SQL Server 2012 Std edition, also PageFile.Sys is 9.8GB... Wait Stats is LCK_M_IX(59%),PAGEIOLATCH_SH(25%)
I was wondering , if there is a way to solve this issue?
1.Is there a way to force the Code coming from application (query plan) to use NO LOCK on SELECT? or any way to Force any query or any login coming xyz application to use NO LOCK?
2.Does Changing ISOLATION LEVEL to READ UNCOMMITTED help?
3.Set deadlock priority LOW?
Can someone please help me is solving this issue..
Thanks and any tip is appreciated....
April 20, 2016 at 9:59 am
nari.koud (4/20/2016)
Hi, I am looking for some help on solving deadlock issues. Please see below scenario..There are frequent SELECT, UPDATE, BULK INSERTS happening in DatabaseA- Tables and Heavy blocking is happening and every time BULK INSERT is chosen as deadlock victim and we are loosing some data because of BULK INSERT is failing.
SELECT is Blocking BULK INSERT or UPDATE is blocking BULK INSERT every time or vice versa and all these Statements(queries) are coming from Application, NOT any STORED PROCEDURES( unfortunately we cannot use Stored procedures)
Database Snapshot is READ Committed, Simple recovery model, Properly indexed and No fragmentation found, Statistics are up to date, MAXDOP is properly set,optimize for ad hoc workloads=1,AUTO_UPDATE_STATISTICS_ASYNC ON,Auto-growth is 500mb for mdf and ldf, tempdb auto growth is 500mb for mdf and ldf ..btw, all databases are sitting on SAME DISK, Memory is 6GB, Total databases Size if 60GB only, SQL Server 2012 Std edition, also PageFile.Sys is 9.8GB... Wait Stats is LCK_M_IX(59%),PAGEIOLATCH_SH(25%)
I was wondering , if there is a way to solve this issue?
1.Is there a way to force the Code coming from application (query plan) to use NO LOCK on SELECT? or any way to Force any query or any login coming xyz application to use NO LOCK?
2.Does Changing ISOLATION LEVEL to READ UNCOMMITTED help?
3.Set deadlock priority LOW?
Can someone please help me is solving this issue..
Thanks and any tip is appreciated....
A) Running a 60GB database on 6GB is silly. Buy/provision more RAM. Single best performance increase you can get.
B) Long locks could be due to poor IO (everything on one disk, although we have no idea what makes up that disk).
C) If you are "properly indexed" you would be the first client I have ever come across in 20 years of consulting on SQL Server. Most are HORRIBLY out of whack with an efficient indexing strategy.
D) To answer your questions:
1.Not that I know of
2.It would help you get bad data. It could improve concurrency.
3.Could help get the deadlock detection system to kill off what you want.
E) The absolute first step to fixing deadlocks is to capture deadlock information and then fix the root causes. These can be due to bad code in a bunch of ways and also often due to poor indexing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2016 at 10:43 am
Hey Kevin..Thanks for the tips.
A. we have a plan to add more memory soon
C. I said properly indexed only because , i made sure -No duplicate, No unused, no missing Indexes exists
E. I Captured the deadlock info and explained in my post before( with statements causing), the root cause is Application code doing SELECT, BULK INSERT , UPDATE Heavily on tables at a time
April 20, 2016 at 11:02 am
nari.koud (4/20/2016)
Hey Kevin..Thanks for the tips.A. we have a plan to add more memory soon
C. I said properly indexed only because , i made sure -No duplicate, No unused, no missing Indexes exists
E. I Captured the deadlock info and explained in my post before( with statements causing), the root cause is Application code doing SELECT, BULK INSERT , UPDATE Heavily on tables at a time
No dupe indexes is definitely good, but not necessarily a deadlock issue.
The only time I have seen servers without any missing indexes at clients is just after the server was bounced. When was yours last restarted?
Since this is a third party system, do you have ANY ability to affect the code? If not then you are pretty much stuck with addressing indexing flaws (although that can "void" your warranty - but that is manageable by having things scripted to drop/create as necessary such as before patching or calling for tech support). Plan guides could be a tool, but likely of little use to mitigate locking/deadlock issues.
Have you presented this to the 3rd party ISV (assuming this is not your code)? Do they have other clients with such issues?
If this is your own code, LOTS of questions come to mind, but chief among them is why no sprocs?
Oh, and has this just recently started happening? If so, what changed?!? 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2016 at 11:29 am
Hey Kevin,
Last server restart is 2016-01-04 23:32:30.253
I have no ability to Edit any code, ( i wish i was using Sp's)
I Tried creating dropping modifying indexes - no luck
this is happening since long time may be 9 months but came to my plate this week
April 20, 2016 at 12:21 pm
nari.koud (4/20/2016)
Hey Kevin,Last server restart is 2016-01-04 23:32:30.253
I have no ability to Edit any code, ( i wish i was using Sp's)
I Tried creating dropping modifying indexes - no luck
this is happening since long time may be 9 months but came to my plate this week
So, you are down to improving hardware to reduce the length of locks being held or tuning the queries from what I can see. Your RAM is low so that could really help. But if your IO sucks badly (I bet it does if your company could only put 6GB RAM on this server and put everything on one disk) RAM won't help that much, and not at all with all the WRITES you will be doing for the ETL process.
Given the server config and that this type of thing has been going on for pushing a year, it is likely that your entire environment is suffering from a BUNCH of problems. I strongly recommend getting a professional on board to give your systems a performance review.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2016 at 12:26 pm
Thanks Kevin.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply