August 30, 2011 at 8:21 pm
I have a scheduled job that runs every 30 minutes that all of a sudden is resulting in a deadlock?!
This job has been in place for over a year and - as of Tuesday - everytime it runs I am experiencing a deadlock. I have been scouring all over the internet to try and figure this out - basically everything that I find involves turning on tracing and running the sql profiler - but I am not really sure what I am looking for. And furthermore, I am not sure why this is happening all of a sudden - is this common?
What can I do to track this down?
Thanks in advance
sb
August 31, 2011 at 1:31 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
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
August 31, 2011 at 8:42 am
File is attached - I renamed it to .txt from .xdl.
I was able to open the graph in SSMS - but I am not sure what it is telling me.
Thanks,
sb
August 31, 2011 at 12:50 pm
You'll need to look at the performance of each query. It seems like both can be optimized.
What does the fnOrderDetailsXML() function actually do? Can you post the code of it? If it's not a good performing inline table-valued function then you might have a performance bottleneck there and the other query might need a rewrite to get the RemainingInventory value as well as a closer look to what seems to be a view (vAvgGasSalesFromLastInventory).
Could you please post the execution plan for each of the two individual statements (SELECT ... INTO #PendingRunningInventory FROM.. and UPDATE OrderDetails ....)?
August 31, 2011 at 1:11 pm
Thank you for the reply..
The fnOrderDetailsXML takes order detail information as XML and returns this information as a table.
SELECT t.c.value('@GasSiteID','varchar(100)') GasSiteID,
t.c.value('@OrderStatus','int') OrderStatus,
t.c.value('@OrderDetailsID','varchar(100)') OrderDetailsID,
t.c.value('@NetGallons','varchar(100)') NetGallons,
t.c.value('@GrossGallons','varchar(100)') GrossGallons,
t.c.value('@PulledProduct','varchar(100)') PulledProduct,
t.c.value('@DroppedProduct','varchar(100)') DroppedProduct,
t.c.value('@Verified','varchar(100)') Verified,
t.c.value('@VerifiedBy','varchar(100)') VerifiedBy,
--t.c.value('@Instructions','varchar(100)') Instructions,
t.c.value('@ModifiedBy','varchar(100)') ModifiedBy,
t.c.value('@BillOfLadenNumber','varchar(100)') BillOfLadenNumber,
t.c.value('@PulledDateTime','varchar(100)') PulledDateTime,
t.c.value('@DroppedDateTime','varchar(100)') DroppedDateTime,
t.c.value('@SupplierID','varchar(100)') SupplierID,
t.c.value('@TerminalID','varchar(100)') TerminalID,
t.c.value('@BusinessDate','varchar(100)') BusinessDate,
t.c.value('@OrderID','int') OrderID
from @x.nodes('/OrderDetails/OrderDetail') t(c)
Attached are the execution plans. I notice in the PendingInventory exec plan there is a missing index?!?
Thanks again
sb
August 31, 2011 at 1:32 pm
It seems like the function is called 14 times (once for each SELECT value). It might help to shred the data using the function and insert it into an indexed temp table (indexed on OrderDetailsId). The previous query to update dbo.HoursLeftOfInventory could benefit from that, too.
Of course, this is all based on the fact that these are estimated execution plans, not actual. Actual execution plans might be totally different...
There also seems to be a missing indexes, e.g. on
[dbo].[OrderDetails] ([DroppedProd]) INCLUDE ([GasSiteID],[GrossGallons],[Verified],[SupplierID],[TerminalID])
and
[dbo].[OrderDetails] ([Verified])
INCLUDE ([GasSiteID],[GrossGallons],[DroppedProd],[SupplierID],[TerminalID])
But those tow have to be evaluated if the additional cost to store and maintain the index can be tolerated.
Could you also please post the definition of the view in addition to the actual execution plans?
It seems like the view is referencing several tables outside the current db.
August 31, 2011 at 4:09 pm
You might try just adding the columns GasSiteID and DroppedProd to the orderDetails.OrderStatus index.
I agree though, it looks like there's a lot of improvement that could be made in those queries with a bit of work.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
September 1, 2011 at 8:53 am
Attached are the definitions of the view(s). When I went and looked at the one, it seems that it pulls from 2 others, that at least one of which, pulls data from another database.
As far as the XML goes - I am not sure why it would run 14 times. Essentially what happens is - from the front end, we create an XML string of N number of order details. We originally wrote this application using SQL 2005 (before we could pass tables as parameters to stored procs) - so XML became the answer. We send the xml string into the fn, it gives us back a table of the xml data. from there we update the order details table with the information in the xml table. Is there a better way to accomplish this?
The update order details is run by the user (any number of times during the day) while the original job in the post is run every 30 minutes on a schedule. I am guessing that these are colliding.
Thanks again for all of this - it is an eye opening experience.
sb
September 1, 2011 at 9:22 am
I also wanted to mention that - I rebuilt the indexes on the database yesterday - and it seemed to run successfully from 3pm until this morning - does that mean that this issue is showing up because of an inefficient process? I think it is safe to say that the order details table is the largest in the database...
September 2, 2011 at 7:55 am
Additional guidance can be found here (http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx)
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply