I am not a SQL expert which is why I leave what to monitor in SQL up to my DBA team. However, my DBA team experienced a blocking problem on one of their servers and now needs me to monitor said server for blocking. It has come to my attention through another post (SQL server blocking and long running sessions alert on SAM) that I can achieve this by alerting on the Average lock wait time component in Appinsight:SQL. This seems to work, at least partially, let me explain...
We have a lab environment for SAM where I was able to simulate blocking per this link (How to create a blocking transaction in SQL Server – T-SQL (exclusive lock) ) in a monitored lab SQL server. I overrode the default polling of the appinsight template to 60 seconds for testing and I can see in the database that it does in fact appear to be "polling" every minute. However, when I run the simulated lock for 30 minutes, (which is more than enough to send the component into critical status) I only see "critical" recorded in the componentavailablity table TWICE - once after about 15 minutes and then again after another 30 minutes despite the actual blocking condition persisting for more than 30 polls. The alert I build which evaluates every 60 seconds and triggers immediately also alerts on that first poll that returns critical but then of course immediately resets because the next 60 second poll returns UP. To make matters worse when you query the actual database for locks, it is very obvious that a lock still exists!
I realize there may be hidden things going on in the appinsight template that solarwinds doesn't want you know about but are these counters worthless? Are they the actual performance counters or something else the template is running? Are they broken? Why is it only "collecting" data seemingly every 15 minutes when the SAM template is obviously polling every 60 seconds? Are there SAM polling settings? Am I completely off base here and need to buy DPA?!
