This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

AppInsight for SQL Deadlocks/sec on Solarwinds DB

Hi All

we have SQL 2016 for our backend and we have appinsight for SQL monitoring it.  All seems to be good, except the deadlocks/sec is always above 0, usually 0.10

Are there anything we should be looking at in order to sort out this issue? Everything else seems to be OK.

I'm wondering if there are some standard processes we should be going through to rectify this as obviously appinsight is showing as critical on the Monitoring DB server

pastedImage_0.png

This is all on the monitoring DB, no other SQL DBs seem to have this issue

  • What are the specs of your system?

    All roads lead to the hardware requirements spec page for DB issues.

    Do you have the Orion application SAM template loaded?

    Any issues there with jobs queued or jobs lost?

  • SQL 2016

    pastedImage_0.png

    135GB RAM Assigned to SQL

    Haven't got the template, can't find it on thwack only the 2017.3 seems to be there

  • Apply the Orion template asap. Disable a few of the components that are no longer valid.

    This is the most important template for all SolarWiinds admins to deploy. It should be second nature to deploy it against your main polling engine for every production environment. I couldn’t live without it.

  • The issue looks to be caused by SQL server. Please have a look at below (probably you know that):

    SQL Performance Counter - Deadlocks_sec.png

    Try operations on SQL server configuration (see parallelism) and ask SQL admin to shrink and fix indexes on DB.

  • OK, if its the case then why isn't there more info from solarwinds about settings that should be applied to the Solarwinds DB in order to stop this,  These deadlocks are occurring on the SW DB

  • So, you do realize that historically, all of the AppInsight templates have a tendency to show themselves to be an occasional a big red critical mess, but are more often than not, are red herrings.

    Do you have a DBA that has followed the SQL server recommendations?

    Are you using RAID 10?

    How many nodes? What are your retention times and polling times?

    Have you deployed the Orion template to see if you have any Orion app layer issues?

    What issues are you seeing other than a deadlock performance dashboard anomaly?

    Look at the SolarWinds SQL AppInsight demo site below and click around - its a red mess.

    My point is, just because you see a performance counter red, doesn't mean you have a critical issue going on, especially if you haven't followed recommended hw specs (seems like you MAY have a disk performance issue, you have a ton of RAM, 2 CPU seems low), and not tracking Orion app level performance with that template I keep blabbering about. emoticons_happy.png

    Click around here and see that those performance counters are not the Ten Commandments. emoticons_grin.png

    https://oriondemo.solarwinds.com/Orion/APM/Summary.aspx?viewid=96

    pastedImage_1.png

  • The server has 2 CPUs, but 32 cores in total.

    There doesn't seem to be any other issues in the DB, its performing a lot better since we moved to the new hardware at the end of the year emoticons_happy.png

  • So you have a decent chunk of hardware on the system, have you been able to ask a DBA to look through and find what queries are getting deadlocked?

    Deadlocks are 100% of the time a problem.   Something is wrong that you see consistent deadlocks, and since it doesn't seem immediately likely that it's a hardware deficiency you need to find what tables are involved to zero in on what's going on.   The DPA product is geared toward that detailed info, but with just SAM you should be able to see error event logs indicating a deadlock happened  and gives the PID associated with the query that was blocking and you could look up what that PID was doing.