AppInsight for SQL component count and best practices?

MoonLight

Hi All,

I've been looking for good explanation on how to count the AppInsight component usage, and best practices on how/when to use them.

AppInsight for SQL is nice with all the metrics that we can get out of it, the problem is the AppInsight component count will scale up with the number of databases in the assigned node/instance. In some cases I've seen a single assigned AppInsight can have more than 10K component counts.

My first question is there a formula to count the component use? I need to estimate if there's suddenly a request to enable appInsight for bunch of servers, then I need to know how much component they will use. This way we can estimate if our poller can have the additional loads or not.

And what's considered best practices in using AppInsight? When/how to use AppInsight for SQL? I've seen a single node with AppInsight can use over 10K components, which basically the limit of the polling engine. It's hard to imagine we need to setup a new poller only for that single appinsight. Even with component based license that has limit of 40K, it's still excessive.

Is there a good article or post that can explain that? I've been searching in KB and forum but I couldn't find a good info for above.

Thank you

jm_sysadmin

If you are licensing by component not node, it counts as 120 components. What it actually uses varies as you likely have seen. One of the servers I just checked had 650 components, and I too once had a shared sql server with a few hundred databases and over 10,000 components. I turned that monitor off quickly. Bascially the more DB's on a server, the haevier the monitoring load. 

If you want an idea of what rolls up into a server's AppInsight for SQL, maybe use the SWQL in a custom query component or use SWQL Studio in the SDK. Replace the servername, and check that templateID 9 is 'AppInsight for SQL' on your system as well. 

SELECT Caption,  N.Applications.ApplicationTemplateID, N.Applications.Components.Name

FROM Orion.Nodes N

Where N.Applications.ApplicationTemplateID  = 9 and N.Caption like ''

That will list each of the components to get a ballpark of the load.  

Since the AppInsight is a real heavy hitter, lots of great information, but also lots of work for the polling engine, I only use it when I need to.

If we have performance issues, it likely gets turned on. Now I have DBA's that also run DPA. We generally don't need the details in AppInsight with the DPA information. They are not the same set of data, but there is some overlap, and typically issues are solved without the AppInsight. We don't have enough DPA for all SQL in the environment.

Then I have to balance out what do I want to know and when. I mostly use the WMI service monitor, and port check for 95% of the SQL servers (even with DPA) The other 5% I might grab specific log events, or performance counters. Out of 220 SQL installs, I have AppInsight on 7 of them right now. Those are typically things that I want to use the data to trigger alerts based on business need. Its also good if I want to find a needle in a haystack for oddball issues.

What AppInsight does very well is give people without SQL knowledge or other SQL tools a great starting point to understand the health and performance of SQL. Many of the counters have explanations and suggestions of fixes. Many of the thresholds are suited to 'normal' SQL use, but you might want to get your SQL performing well and reset the thresholds based on the past week of data. That way as the workloads change, Orion will alert accordingly.