5 Replies Latest reply on Nov 20, 2011 1:33 PM by dmshimself

    Deadlock error in User dropdown

    Apprentice

      Hi All,

       

      We just rolled out the use of Web Access to our analysts.

       

      One of the issues that has occurred is they are sometimes receiving a deadlock error when just clicking on the dropdown for a user.

       

      They haven't selected a user yet, just clicking on the User dropdown in an incident.

       

      Support is telling me it has to do with our database needing to be optimized.

       

      I find this hard to believe since they haven't even selected a user yet.

       

      The shortcut they are using is a template, so I'm guessing this might be an issue.  We needed the template because of the need for separate catefories, impact and urgency based on the Lifcycle.

       

      Since WebAccess ignores filters on the Lifecycle/Title, this is the work around Karen P and support came up with.

       

      Anybody have any ideas about the deadlock ?  Could it be the template ?

        • 1. Re: Deadlock error in User dropdown
          ITSMMVPGroup

          When you click on the drop-down, the app will go out and access the user table. Depending on the database technology you're using, we have found plenty of occasions when the LDSD filters and Database indexes used to need to be improved. This is properly what support are referring to.  If you're getting deadlocks at this point, then I'd suspect a general read through the performance guide is in order, in particular looking at producing some sort of maintenance plan for all of the database tables.  For MS SQL this is pretty easy, but if you're using Oracle you'd need to include your local DBA in this activity.

          • 2. Re: Deadlock error in User dropdown
            Stu McNeill Employee

            Hi Brian,

             

            Do you know if snapshot isolation is enabled on the database?  See this document for information on how to check it: Reducing deadlock errors by enabling Snapshot Isolation in SQL Server.

             

            Thanks

            • 3. Re: Deadlock error in User dropdown
              Apprentice

              Dave and Stu, thank you for replying.

               

              Dave,

               

              I'll take a look at the performance guide again.

               

              I just find it interesting this started to crop up after we rolled out WebAccess.

               

              Stu,

               

              I looked at the snapshot isolation and it is currently OFF.  We are running 7.4.

               

              I need to talk to our group to see if it is what we want to do.

               

              From the doc you posted, it is recommended to have this ON ? 

              • 4. Re: Deadlock error in User dropdown
                Stu McNeill Employee

                Hi Brian,

                 

                Yes snapshot isolation is recommended and comes enabled on the database by default if you have Service Desk implemented on version 7.4 onwards.

                 

                Your issue may need further investigation if snapshot isolation doesn't solve it (or perhaps it just turns into a performance problem) but its always a good start

                • 5. Re: Deadlock error in User dropdown
                  ITSMMVPGroup

                  Hi Brian - IMHO all DBs need a bit of tender loving care from time to time, I've even found the standard SQL out-of-the-box database benefits from additional indexes.  We have found general database optimisation as an initial step and also snapshot isolation will usually fix these sorts of issues with MS SQL databases.