Performance Monitor (PerfMon) is an inbuilt Windows Tool for monitoring all aspects of Windows and hosted applications such as SQL Server. Performance Metrics in PerfMon are referred to as counters. PerfMon counters can be visualized in real time. A Data Collector Set is used to organize and schedule performance counter data as a single collection to be analyzed later. When you install SQL Server, windows adds counters relevant to SQL Server in Perf Mon.
Back in the day when budget was low and not many people were interested in SQL Server (at least in my environment), we had to develop creative ways of taking care of those aspects of database management that are taken for granted these days. Monitoring and troubleshooting are critically important and several SQL Server specific tools for achieving this are out there - Redgate SQL Monitor, Devart dbForge Studio, SentryOne SQL Sentry etc. Today’s article describes how to use Windows Performance Monitor objects and counters to monitor SQL Server.
Windows Performance Monitor is a native windows tool that allows admins to monitor windows and applications running on windows. While it may not be the most efficient way to monitor your instances, you could find the approach useful when you want to investigate a specific issue leveraging the flexibility of performance counters. Think of it as something similar to using SQL Profiler or Extended Events which come built-in with SQL Server. SQL Server specific Performance Counters are viewable through a catalog view known as sys.dm_os_performance_counters within SQL Server. About 2336 rows are returned by the following query in SQL Server 2022:
SELECT * FROM sys.dm_os_performance_counters;
The instance level permission VIEW SERVER STATE is required to see the result set of this query.
How to Use Performance Monitor
Performance monitor can be used to display real-time information or collect performance data (Data Collector Sets). To view the the real-time statistics displayed by performance monitor take the following steps:
There are a number of ways to generate key values in SQL Server tables including the IDENTITY column property, the NEWID() function and more recently, SEQUENCES. The IDENTITY column property is the earliest of these methods. It was introduced very early in the history of SQL Server and it is arguably the simplest approach. Though old, IDENTITY is still maintained in modern versions of SQL Server and is still relevant for simple use cases.
In this article, we show how we used Database Snapshots as a rollbackup plan for a database migration from one data centre to another. Database Snapshots proved to be the best route since we sould not afford the time a backup/restore approach would take.