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:
- The number of records returned by each select query
- The number of queries executed per hour/day
- The performance of individual queries.
How can I do this?
Best Answer
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.