Page 3 of 3 FirstFirst 123
Results 81 to 114 of 114

Thread: transaction (process id nn) was deadlocked...

  1. #81

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    I am playing around in the dev database while we discuss this and I created the two indexes and ran with the execution plan, and now on the first query it is suggesting:
    CREATE NONCLUSTERED INDEX x ON Trips (Trip_Type, EmployeeID) INCLUDE (Control, TripStatus)
    Do I just keep going with the suggestions?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #82
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    Quote Originally Posted by MMock View Post
    Oh, right. Sorry! I was focused on no scans.

    First query suggested index:
    CREATE NONCLUSTERED INDEX x ON Trips (EmployeeID, Trip_Type, TripStatus) INCLUDE (Control)

    2nd query suggested index:
    CREATE NONCLUSTERED INDEX y ON Trips (ScheduledTime, Trip_Type, TripStatus) INCLUDE (Control)
    Even though those are the suggested indexes it seems that Trip_Type and TripStatus are enough in that you got 2600 rows only when doing that WHERE select.

    But - let's get to that third query. What INDEX did it use?

    Quote Originally Posted by MMock View Post
    I am playing around in the dev database while we discuss this and I created the two indexes and ran with the execution plan, and now on the first query it is suggesting:
    CREATE NONCLUSTERED INDEX x ON Trips (Trip_Type, EmployeeID) INCLUDE (Control, TripStatus)
    Do I just keep going with the suggestions?
    I wouldn't start making indexes yet...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #83

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    Quote Originally Posted by szlamany View Post
    Even though those are the suggested indexes it seems that Trip_Type and TripStatus are enough in that you got 2600 rows only when doing that WHERE select
    And that index already exists:
    CREATE NONCLUSTERED INDEX [ix_Trip_Type_and_Status] ON [dbo].[Trips]
    (
    [Trip_Type] ASC,
    [TripStatus] DESC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
    GO

    Quote Originally Posted by szlamany View Post
    But - let's get to that third query. What INDEX did it use?
    I find that out by looking at the plan? If yes, ix_TripStatus and PK_TripTypes

    Quote Originally Posted by szlamany View Post
    I wouldn't start making indexes yet...
    Uh, ok, that's why I'm in dev! I'll back them off.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #84
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    Quote Originally Posted by MMock View Post
    I find that out by looking at the plan? If yes, ix_TripStatus and PK_TripTypes
    How many rows did it need to process on that panel where you read that...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #85

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    That's not from a panel. That is from you saying "it seems that Trip_Type and TripStatus are enough" and me showing you such an index exists, but no I don't see the queries using it.

    Am I understanding correctly that you want to see each query and what index is actually getting used?

    Assuming yes...
    Query #1
    Code:
    SELECT [Control]
    FROM         dbo.Trips JOIN dbo.TripTypes ON dbo.Trips.Trip_Type = dbo.TripTypes.Trip_Type
    WHERE    (
                            (dbo.Trips.TripStatus <> N'CP') 
    			AND (dbo.Trips.TripStatus <> N'PP') 
    			AND (dbo.Trips.EmployeeID IS NULL OR dbo.Trips.EmployeeID = N'')
    			AND (dbo.Trips.Trip_Type <> N'Rental') 
    			AND (dbo.TripTypes.ShopItem = 0) 
    			AND (dbo.Trips.Trip_Type <> 'SDII') 
    				)
    Indexes it used: ixTripStatus Actual # rows = 2629 and ix_TripEmpID Actual # rows = 302,026

    Query #2
    Code:
    SELECT [Control]
    FROM         dbo.Trips JOIN dbo.TripTypes ON dbo.Trips.Trip_Type = dbo.TripTypes.Trip_Type
    WHERE 
    				(      (dbo.Trips.TripStatus <> N'CP') 
    				AND (dbo.Trips.TripStatus <> N'PP')
    				AND (dbo.Trips.Trip_Type <> N'Rental')
    				AND (dbo.TripTypes.ShopItem = 0)
    				AND (dbo.Trips.Trip_Type <> 'SDII')
    				AND (dbo.Trips.ScheduledTime IS NULL)
    				)
    ix_UnassignedTasks, actual # rows = 3

    Query #3
    Code:
    SELECT [Control]
    FROM         dbo.Trips JOIN dbo.TripTypes ON dbo.Trips.Trip_Type = dbo.TripTypes.Trip_Type
    WHERE 
    				(	(dbo.Trips.TripStatus = N'NE')
    				AND (dbo.Trips.Trip_Type <> 'SDII')
    				AND (dbo.TripTypes.ShopItem = 0)
    				)
    ixTripStatus #rows = 2 and PK_TripTypes # rows = 2

    I hope that's what you were asking...
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  6. #86
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    So - with that said it seems the first query is evil by that 300,000 count...

    Let's run it with the EMPLOYEE business commented out

    Code:
    SELECT [Control]
    FROM         dbo.Trips JOIN dbo.TripTypes ON dbo.Trips.Trip_Type = dbo.TripTypes.Trip_Type
    WHERE    (
                            (dbo.Trips.TripStatus <> N'CP') 
    			AND (dbo.Trips.TripStatus <> N'PP') 
    			--AND (dbo.Trips.EmployeeID IS NULL OR dbo.Trips.EmployeeID = N'')
    			AND (dbo.Trips.Trip_Type <> N'Rental') 
    			AND (dbo.TripTypes.ShopItem = 0) 
    			AND (dbo.Trips.Trip_Type <> 'SDII') 
    				)
    What did that change it to?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #87

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    Now the index it is using is ix_Trip_Type_and_Status, 2221 rows
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  8. #88
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    test - test - having a hard time posting - forum is ugly

    Ok - so this might be your solution

    [code]
    Select XYZ.*
    From
    (SELECT Distinct [Control]
    FROM dbo.Trips JOIN dbo.TripTypes ON dbo.Trips.Trip_Type = dbo.TripTypes.Trip_Type
    WHERE (
    (dbo.Trips.TripStatus <> N'CP')
    AND (dbo.Trips.TripStatus <> N'PP')
    --AND (dbo.Trips.EmployeeID IS NULL OR dbo.Trips.EmployeeID = N'')
    AND (dbo.Trips.Trip_Type <> N'Rental')
    AND (dbo.TripTypes.ShopItem = 0)
    AND (dbo.Trips.Trip_Type <> 'SDII')
    )
    Union

    SELECT Distinct [Control]
    FROM dbo.Trips JOIN dbo.TripTypes ON dbo.Trips.Trip_Type = dbo.TripTypes.Trip_Type
    WHERE
    ( (dbo.Trips.TripStatus <> N'CP')
    AND (dbo.Trips.TripStatus <> N'PP')
    AND (dbo.Trips.Trip_Type <> N'Rental')
    AND (dbo.Tr

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #89
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    .ShopItem = 0)
    AND (dbo.Trips.Trip_Type <> 'SDII')
    AND (dbo.Trips.ScheduledTime IS NULL)
    )

    Union

    SELECT Distinct [Control]
    FROM dbo.Trips JOIN dbo.TripTypes ON dbo.Trips.Trip_Type = dbo.TripTypes.Trip_Type
    WHERE
    ( (dbo.Trips.TripStatus = N'NE')
    AND (dbo.Trips.Trip_Type <> 'SDII')
    AND (dbo.TripTypes.ShopItem = 0)
    ) as XYZ

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #90
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    Ok - maybe you can make that out

    Select XYZ.* From ({1st where clause} UNION {2nd where clause} UNION {3rd where clause}) as XYZ

    UNION must be used - UNION ALL would be evil. I put SELECT DISTINCT in each one - probably not needed - might want to remove after testing.

    Can you assemble this query and get it past syntax checks and get it to run?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #91

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    Yes it ran but it's returning employees who are assigned to a task already. Can we do something about the criterion you commented out?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  12. #92
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    Give me a second - I'm not done - hopefully it will allow a full post in the next thread!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #93
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    So - this is initially very ugly - but in actuality it's VERY safe.

    Your original view

    Code:
    ALTER VIEW [dbo].[qryAllUnassignedTasks]
    AS
    SELECT     TOP (100) PERCENT dbo.Trips.CustomerName, dbo.Trips.ScheduledTime, dbo.Trips.relJobControl, dbo.Trips.TripStatus, dbo.Trips.EmployeeID, dbo.Trips.SONumber, 
                          dbo.Trips.Control, dbo.Trips.Trip_Type, dbo.Trips.Priority, dbo.Trips.Ack, dbo.Jobs.Complaint, ISNULL(dbo.Trips.ScheduledTime, dbo.Trips.EnteredOn) AS SortDate, 
                          dbo.Trips.LaborLocation
    FROM         dbo.Trips LEFT OUTER JOIN
                          dbo.Jobs ON dbo.Trips.relJobControl = dbo.Jobs.Control RIGHT OUTER JOIN
                          dbo.TripTypes ON dbo.Trips.Trip_Type = dbo.TripTypes.Trip_Type
    WHERE     (dbo.Trips.TripStatus <> N'CP') AND (dbo.Trips.TripStatus <> N'PP') AND (dbo.Trips.EmployeeID IS NULL OR
                          dbo.Trips.EmployeeID = N'') AND (dbo.Trips.Trip_Type <> N'Rental') AND (dbo.TripTypes.ShopItem = 0) AND (dbo.Trips.Trip_Type <> 'SDII') OR
                          (dbo.Trips.TripStatus <> N'CP') AND (dbo.Trips.TripStatus <> N'PP') AND (dbo.Trips.Trip_Type <> N'Rental') AND (dbo.TripTypes.ShopItem = 0) AND 
                          (dbo.Trips.Trip_Type <> 'SDII') AND (dbo.Trips.ScheduledTime IS NULL) OR
                          (dbo.Trips.TripStatus = N'NE') AND (dbo.Trips.Trip_Type <> 'SDII') AND (dbo.TripTypes.ShopItem = 0)
    ORDER BY dbo.Trips.Priority, SortDate
    Change it to this (note I put the ()'s around each of the WHERE segments at the bottom of this query)

    Code:
    ALTER VIEW [dbo].[qryAllUnassignedTasks]
    AS
    SELECT     TOP (100) PERCENT dbo.Trips.CustomerName, dbo.Trips.ScheduledTime, dbo.Trips.relJobControl, dbo.Trips.TripStatus, dbo.Trips.EmployeeID, dbo.Trips.SONumber, 
                          dbo.Trips.Control, dbo.Trips.Trip_Type, dbo.Trips.Priority, dbo.Trips.Ack, dbo.Jobs.Complaint, ISNULL(dbo.Trips.ScheduledTime, dbo.Trips.EnteredOn) AS SortDate, 
                          dbo.Trips.LaborLocation
    FROM         ( {the 3 query think we built} ) as XYZ
                          Left Join dbo.Trips on dbo.Trips.Control=XYZ.Control LEFT OUTER JOIN
                          dbo.Jobs ON dbo.Trips.relJobControl = dbo.Jobs.Control RIGHT OUTER JOIN
                          dbo.TripTypes ON dbo.Trips.Trip_Type = dbo.TripTypes.Trip_Type
    WHERE     ((dbo.Trips.TripStatus <> N'CP') AND (dbo.Trips.TripStatus <> N'PP') AND (dbo.Trips.EmployeeID IS NULL OR
                          dbo.Trips.EmployeeID = N'') AND (dbo.Trips.Trip_Type <> N'Rental') AND (dbo.TripTypes.ShopItem = 0) AND (dbo.Trips.Trip_Type <> 'SDII')) OR
                          ((dbo.Trips.TripStatus <> N'CP') AND (dbo.Trips.TripStatus <> N'PP') AND (dbo.Trips.Trip_Type <> N'Rental') AND (dbo.TripTypes.ShopItem = 0) AND 
                          (dbo.Trips.Trip_Type <> 'SDII') AND (dbo.Trips.ScheduledTime IS NULL) ) OR
                        (  (dbo.Trips.TripStatus = N'NE') AND (dbo.Trips.Trip_Type <> 'SDII') AND (dbo.TripTypes.ShopItem = 0) )
    ORDER BY dbo.Trips.Priority, SortDate

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #94

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    Um, I don't know what to say but I'll try
    So the employee criteria is commented out in our XYZ queries but comes in again in the outer WHERE clause? That is, I was supposed to leave the conditions in the inner 3 queries which no longer has the employee criteria, even though most of them are repeated again and with the employee criteria...
    You can tell I don't quite understand but once I know I have the final answer I will try to analyze it and walk through it.
    I can take out the 100 % and ORDER BY because I remember you had a slight objection to them at the beginning. I don't think they're needed.
    How do I know this is an improvement since me running it in SSMS is hardly equivalent to my 10-20 users refreshing regularly and I see 0 seconds there but I saw that with the original view as well?
    Finally, thank you! even though we're not quite done...
    P.S. I am leaving until mid-afternoon. I have to take my daughter back to school to take her last exam and I am hanging out there.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  15. #95
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    I went with an INITIAL safe-for-production change.

    Left the entire VIEW as it was - except for the ()'s around the 3 WHERE groups that were missing.

    Change the FROM clause to be a sub-query that returns at most a few thousand rows to be processed. It gets these via indexes so it's wicked fast.

    Left the original WHERE clauses in place - again to be less of a change to production.

    You will know it's better by evaluating the EXECUTION PLAN, I would imagine.

    And if you feel it's safe enough for production you will also see the deadlock issue either go away or be reduced substantially.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  16. #96
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: transaction (process id nn) was deadlocked...

    test - test - having a hard time posting - forum is ugly
    Yeah, I had that yesterday.

    @Steve, just a suggestion but that first one could be broken into two unions as well. Would be something like :-
    Code:
    WHERE (
    (dbo.Trips.TripStatus <> N'CP') 
    AND (dbo.Trips.TripStatus <> N'PP') 
    AND (dbo.Trips.EmployeeID IS NULL)
    AND (dbo.Trips.Trip_Type <> N'Rental') 
    AND (dbo.TripTypes.ShopItem = 0) 
    AND (dbo.Trips.Trip_Type <> 'SDII') 
    )
    
    Union
    
    ...
    WHERE (
    (dbo.Trips.TripStatus <> N'CP') 
    AND (dbo.Trips.TripStatus <> N'PP') 
    AND (dbo.Trips.EmployeeID = N'')
    AND (dbo.Trips.Trip_Type <> N'Rental') 
    AND (dbo.TripTypes.ShopItem = 0) 
    AND (dbo.Trips.Trip_Type <> 'SDII') 
    )
    That would give it a better chance of using an appropriate index for that section. I thought TrapStatus, TripType and ShopId would be selective enough but, from MMock's feedback in post 85, it looks like employeeId is the most selective field.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  17. #97
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    @FD - that should be explored...

    I kind of see this as two main tasks.

    One task is to break down the complex WHERE GROUP's that were being OR'ed - using the EXECUTION PLAN to figure out if we are seeking or scanning.

    The other task is to change the query format to pull-PK-values in a sub-query and JOIN all other tables back to this sub-query.

    I was just moving into the second task so that MMOCK could see where this was headed. Just in case the idea of changing the query to something like that was just too much of a crazy change.

    From the looks of things this table has a bunch of indexes already - I'm very leery about suggesting anything new in ways of indexes.

    And I'm unclear if the "data situation" we have today that leads to certain indexes being better than others is constant - or a sea of change comes every day!

    With all that said once we have the pull-PK-values sub-query running on indexes only - and returning 2000-or-so-rows - we can almost claim victory on that alone. 20 concurrent people calling this new query compared to 20 concurrent people calling a query that might be reading 300,000 rows...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  18. #98

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    I will be back on this full time in the office tomorrow where I can concentrate better.
    Trying now to fix a small issue elsewhere in the app that someone emailed me about while I was out.
    Thanks again!!! You will hear more from me tomorrow.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  19. #99

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    I'm back. I was saving the best (of the things I needed to do today) for last...
    I was just emailing OC explaining your change when he cross-emailed me saying deadlocks still happening and did I tune yet? So good timing. (I said I would not "tune" without his review and blessing first).
    I don't quite understand what you are saying about where we go from here. "MMOCK could see where this was headed". Is it not there yet? Because what you explained you wanted to do looks like we're doing it.
    Thanks.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  20. #100
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    Yes - you have all the parts now - I fully explained where we are headed and you should have all you need to construct a query.

    I was just explaining to FD why I left the "query optimization work" and headed into the final pk-subquery.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  21. #101

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    Well, I didn't really need to construct the query...didn't you do all that for me already? I just had to plug in "the 3 query that we built" .

    Is this a significant improvement? New, then old:

    SQL Server Execution Times:
    CPU time = 78 ms, elapsed time = 97 ms.

    SQL Server Execution Times:
    CPU time = 204 ms, elapsed time = 121 ms.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  22. #102

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    OK, so that was from simply "set statistics time on".
    He said he "did the client side statistics which lets you measure run over run" and it shows the original performing better ?
    New
    Client processing time: 62.3333
    Total execution time: 286.0000
    Wait time on server replies: 223.6667

    Original
    Client processing time: 41.6667
    Total execution time: 153.3333
    Wait time on server replies: 111.6667

    Should I ask him if he's sure he didn't swap them?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  23. #103
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    The CPU time was cut in half - that is great. The wall-clock time never follows suit - there is setup time and front-end/back-end processing that isn't part of what we can affect.

    Curious how the ELAPSED of 121 can possibly contain the 204 CPU clicks...

    You could scrutinize the EXECUTION PLAN between both queries to prove to yourself that row counts getting touched are much lower.

    Or wait to see how the deadlock issue behaves in the coming days...

    Saw your second post just now - that makes little sense compared to the first results...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  24. #104
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    Keep in mind though I was not trying to make the query run faster - I was trying to make it run with less touching of the full table. The deadlock is born in that I/O trashing, I am assuming. The new query might be taking more time to merge and group the results - that memory time would be after I/O and not be deadlock-forming at that point.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  25. #105

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    OK, I asked for his blessing and am waiting for a reply.
    Thanks to @fd as well, and I will keep you both posted.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  26. #106

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    Bombs away!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  27. #107
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: transaction (process id nn) was deadlocked...

    Good Luck
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  28. #108

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    I just deadlocked. I'm glad is it was me, and not one of my users. I don't know if because I am catching the exception, I get less information, but it doesn't tell me the query. Only Process ID 167. I will look at the log and see if it's there.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  29. #109

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    Crap, I had forgotten I did something which may be masking everyone else getting the error. In RefreshLoadedData() my catch goes:
    Code:
                catch (Exception ex)
                {
                    if (Globals.Variables.CurID == "MMOCK")
                        MessageBox.Show(String.Format("An error occurred, however, you can continue on your way just please notify Marlene of this message: {0}", ex.Message), "Refresh Loaded Data", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    I will take that out and see if people are getting the deadlock.

    Also, I don't know how to read the log very well so I didn't find out much. I will look at it more closely.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  30. #110
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: transaction (process id nn) was deadlocked...

    That is a shame. I would spend more time looking at the execution plan to see if the separate where-queries can be made to work more efficiently.

    My next suggestion would be to make a materialized view - I think they call them indexed views now.

    https://docs.microsoft.com/en-us/sql...ql-server-2017

    This would take the place of that other INDEX (if it's primary purpose was to feed this update query).

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  31. #111
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: transaction (process id nn) was deadlocked...

    I just deadlocked
    How's the performance on the query when run in isolation now? Are we under, say, half a second yet? If it is it might be worth spending a bit of time looking at the deadlock graph to make sure we're attacking the right thing.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  32. #112

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    Hi. I am not ignoring you guys but today we have an off-site company meeting and it's also Employee Appreciation Day so tomorrow the company is shut down for the day so we can have the day off and be appreciated. I'll be back on Monday. Thanks and have a good weekend!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  33. #113
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: transaction (process id nn) was deadlocked...

    How is the performance now? How long does it take to run the whole query and how long does it take to run the isolated parts?
    Last edited by FunkyDexter; May 21st, 2018 at 09:25 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  34. #114

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: transaction (process id nn) was deadlocked...

    Quote Originally Posted by FunkyDexter View Post
    How is the performance now? How long does it take to run the whole query and how long does it take to run the isolated parts?
    All I know is I had the deadlock this morning, the VP had the deadlock this morning (I can count on her to tell me), and I don't know about anyone else who maybe isn't reporting it. I haven't had time to check the log.

    I am working on a couple other things so I don't know if I have to put this on hold and concede to a NO LOCK for the time being. (I will not concede for good. I owe it to you guys to push forward because you did so much work on my behalf).
    Last edited by FunkyDexter; May 21st, 2018 at 09:25 AM.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Page 3 of 3 FirstFirst 123

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width