SQL Server - Monitoring Magnets

SQL Server - Monitoring Magnets

- Monitor Resource Usage (System Monitor) - System Monitor primarily tracks resource usage, such as the number of buffer manager page requests in use, enabling you to monitor server performance and activity using predefined objects and counters or user-defined counters to monitor events. System Monitor (Performance Monitor in Microsoft Windows NT 4.0) collects counts and rates rather than data about the events (for example, memory usage, number of active transactions, number of blocked locks, or CPU activity). You can set thresholds on specific counters to generate alerts that notify operators.

  System Monitor works on Microsoft Windows Server and Windows operating systems. It can monitor (remotely or locally) an instance of SQL Server on Windows NT 4.0 or later.

  The key difference between SQL Server Profiler and System Monitor is that SQL Server Profiler monitors Database Engine events, whereas System Monitor monitors resource usage associated with server processes.

https://learn.microsoft.com/en-us/sql/relational-databases/performance/performance-monitoring-and-tuning-tools?view=sql-server-ver16

- Activity Monitor - It runs queries on the monitored instance to obtain information for the Activity Monitor display panes. When the refresh interval is set to less than 10 seconds, the time that is used to run these queries can affect server performance. We recommend that you download and install the latest version of SSMS.

https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/open-activity-monitor-sql-server-management-studio?view=sql-server-ver16

- Growth :- It is suggested to monitor Database growth to be able to analyse the overall space need in coming years.

Related Article

https://techcommunity.microsoft.com/t5/azure-observability/monitor-sql-database-size-increase/m-p/364490

- Network interface - Bytes total/sec, We can use Performance Monitor and certain Network related counters to find any network related issues.

- Error Logs (SQL and Agent) and Extended Events :- Extended Events is a light weight performance monitoring system that uses very few performance resources. Extended Events provides three graphical user interfaces (New Session Wizard, New Session and the XE Profiler) to create, modify, display, and analyze your session data.

- Trace Flags (Transact-SQL) - Trace flags display information about a specific activity within the server and are used to diagnose problems or performance issues (for example, deadlock chains).

https://learn.microsoft.com/en-us/sql/relational-databases/performance/performance-monitoring-and-tuning-tools?view=sql-server-ver16

Additionally,

Please also learn about Native's 'General Interfaces'.

- SQL Server Trace/Profiler.

- PerfMon/Performance Monitor

Popular Post

https://amit-sqldbasupport.blogspot.com/2022/12/sql-server-dba-sankalp.html