Important SQL event IDs to monitor in SQL server deployed in Azure VM

Question

I deployed SQL server in Azure VM, I want to monitor the following,

1. Important Event IDs with respect to SQL
2. Find query execution time in windows event logs if possible
3. find query wait statistics in windows event logs if possible
4. SQL Errors in Windows Event logs
5. Export query execution time, wait time, SQL Errors, Replication errors etc., to Windows event logs
6. Important SQL performance counters tat can be sent to Azure log Analytics workspace.

Answer 1

Hello @Prakashraaj V ,

Thank you for reaching out to the Microsoft Q&A platform. Happy to answer your question.

1#Important Event IDs with respect to SQL
-Event ID would be based on what kind of events you would like to monitor, like for example login failures, perf related etc., and something like event id 18456 which is the most common one.

2#Find query execution time in windows event logs if possible.
-This doesn't seem to be possible from windows event logs

For the Point#3 and #5, You can enable Monitoring Performance By Using the Query Store following this document monitoring-performance-by-using-the-query-store

4#SQL Errors in Windows Event logs

• Yes, you can use the windows event logs to fetch all the SQL errors by using the filters

For Point#6 Important SQL performance counters tat can be sent to Azure log Analytics workspace. Below are some common counters :

• Memory – Available MBytes.
• Physical Disk – Avg. Disk sec/Read.
• Physical Disk – Avg. Disk sec/Write. - Physical Disk – Disk Reads/sec.
• Physical Disk – Disk Writes/sec.
• Processor – % Processor Time.
• SQLServer: General Statistics – User Connections.
• SQLServer: Memory Manager – Memory Grants Pending.
• SQLServer: SQL Statistics – Batch Requests/sec.

You can also enable the diagnostic settings for SQL database as stand alone and get below information.
However if you want to do anything custom, you might have to write some automation to read SQL tables and make them as custom logs so that agent can pick it up or send it to Log analytics workspace as customlogs using the data collector api.

Collect custom logs with Log Analytics agent in Azure Monitor - Azure Monitor
Azure Monitor can collect events from text files on both Windows and Linux computers. This article describes how to define a new custom log and details of the records they create in Azure Monitor.

Please "Accept as Answer" and Upvote if any of the above helped to help others in the community looking for remediation for similar issues.