How to monitor performance in application insights of an on prem SQL Server database?
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 &quot;Requests batch per second&quot;. 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.