How to monitor performance in application insights of an on prem SQL Server database?

Question

Application Insights has great ways to monitor databases. After some online investigation, I saw an explanation of how to monitor actual queries. Monitoring SQL performance is possible too but what I found is an explanation for Azure SQL databases. I use on prem SQL Server....

What I want is doing such monitoring for on prem databases. However, the data needs to be monitored by application insights.

What I am typically interested in is this:

1. The number of records returned by each select query
2. The number of queries executed per hour/day
3. The performance of individual queries.

How can I do this?

Answer 1

I don't know about application Insight but many things already exists inside of an SQL prem server to allow you to monitor your server. I'll put here what I use in case it can help you.

For the number of queries per hour/day I monitor perfmon counter "Requests batch per second". https://www.brentozar.com/archive/2017/02/what-is-batch-requests-per-second/

You can also turn on the query store which will help you to monitor performances of queries and query plans.

But to get the number of records returned by each select and the performance of individual queries, you would need to run a trace on your server at all time. It seems to me a little too much and seems like it's going to maybe impact performances of your Server.

What I do is, I monitor perfmon counter latches and when it goes higher than my above threshold, I get an alert and go check what queries are running and do performance check on them. It allows me to catch the problems as they happen. https://www.mssqltips.com/sqlservertip/3088/explanation-of-sql-server-io-and-latches

To monitor perfmon we use a monitoring tool, Zabbix.

You can also use Brent Ozar's sp_blitz to help you. https://www.brentozar.com/blitz/ It's a really useful tool that I use also and it's free.

Answer 2

There are three tools built in to SQL Server that will answer the three questions you've asked:

1. Rows returned
2. Execution Count
3. General performance.

These tools, in no particular order are:

• Dynamic Management Views (DMV)
• Query Store
• Extended Events

Each one will answer all three questions, but, they answer them somewhat differently.

The DMVs show you an aggregation of the behavior of the queries that are currently in cache. From that you can see rows returned, the number of executions for a query, and an aggregation average on execution time & resources used. The key limitations here are that this is an aggregate, and that it's completely dependent on what's currently in cache.

The Query Store shows you an hourly aggregate of the behavior of all the queries. There, again, you can see the rows returned, execution count and an average on time & resources. Because the averages are broken down hourly (by default), you can do before & after comparisons. The limitation here is that you don't get this for free. It has to be enabled & managed on a database by database method. Also, it is an aggregate, but, not a complete aggregate. If you wanted say, a count of all executions over 24 hours, you have to aggregate the aggregates.

Extended Events shows you each individual batch, stored procedure, or query, depending on what you're attempting to capture. This gives you rows returned and precise measures of time & resources. To get aggregations, it's on you. The limitation here, well, there really isn't one. Extended Events can do just about anything. However, you do have to enabled them, configure and maintain them. Then, if you want aggregates, you have to do the work to supply the aggregations (although, the SSMS GUI offers some tools to help with this).

One of these three, or a combination, will give you all the data you want to answer those three questions.