SQL Server and SQL Server Management Studio come with a number of built-in resources that can help with monitoring. It’s worthwhile looking into them because they will help you understand what you can do out-of-the-box without needing to invest in a paid-for tool. Similarly, it will reveal any limitations and tell you what you should be looking for in a paid-for tool.
SQL Server Agent
Many alerts and administrative tasks, or jobs, in SQL Server can be scheduled and automated using SQL Server Agent, which runs in the background as a Windows service and can significantly reduce the workload of a DBA.
Alerts can be configured for general SQL Server events, such as when a specific error number occurs; for performance events, such as wait statistics exceeding a certain value; and for Windows Management Instrumentation (WMI) events, such as a new file appearing in a specific folder. The response to the alerts can also be configured, which can be to run a SQL Server Agent job, or send a notification to one or more administrators.
SQL Server Agent jobs contain one or more steps and can be set up to run tasks like backing up a database at a specific time with a set frequency, or whenever a new database is created or attached.
SQL Server Error Log
The SQL Server error log contains certain user-defined and system events that can aid with troubleshooting and ensuring processes like backup and restore, batch commands and other scripts have completed successfully.
This can be helpful in detecting current or potential problem areas, including automatic recovery messages (particularly if an instance of SQL Server has been stopped and restarted), kernel messages, or other server-level error messages.
A new error log is created each time an instance of SQL Server is started, and backups of the previous six logs are also retained. The logs are text files and can be viewed with any text editor.
By default, they’re located in the Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ folder, with the current error log having no suffix and the backups having a sequentially numbered suffix.
The logs can also be viewed in SQL Server Management Studio by expanding the Management folder in Object Explorer, and then opening the SQL Server Logs folder.
Performance Monitor, or PerfMon, is a popular tool for general server resource monitoring. It provides a range of counters for monitoring memory, disk I/O, CPU and network usage on a server, and correlates them with the performance counters maintained by SQL Server in a real-time graph.
DBAs typically select the counters and resource metrics they want to track and set up PerfMon to record them at regular intervals. The data can then be imported into Excel or a similar tool for later analysis.
While it’s easy to set up and access, (you simply type perfmon in the Run window from the Start Menu), it has limited reporting options and it can’t access SQL Server internals like Dynamic Management Views (DMVs) and stored procedures.
Dynamic Management Views
Under the covers, SQL Server tracks the performance of database sessions and transactions, and makes the information available using DMVs. DMVs can return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
A common diagnosis for slow query performance, for example, is a need for more CPU. What if, however, it is down to a high number of I/O waits, caused by a resource, queue or external issue? In this case, the sys.dm_os_wait_stats DMV query can be used.
By itself, it will return an unordered table which is hard to understand. Add a little more T-SQL as shown in the following example and the table will be ordered with the highest waits at the beginning and exclude those for which wait times are not required.
SELECT wait_type ,
SUM(wait_time_ms / 1000) AS [wait_time_s]
FROM sys.dm_os_wait_stats DOWS
WHERE wait_type NOT IN ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP' )
GROUP BY wait_type
ORDER BY SUM(wait_time_ms) DESC
This will return a list of the wait types, and the wait times associated with each, so that the real cause can be properly investigated and diagnosed.
DMVs can also be used to check performance counters and see if SQL Server has any memory issues. The top three counters to check are page life expectancy, the number of requests that have to wait for a free page (free list stalls/sec), and the page reads per second. The query to retrieve these values would be:
SELECT object_name, counter_name, cntr_value
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] in ('Page life expectancy','Free list stalls/sec','Page reads/sec')
These are just two examples of how DMVs give instant access to a wealth of information about everything from wait stats to memory issues, through to finding missing indexes and checking on the status of jobs. The full list of DMVs available is on the Microsoft documentation pages. The only downside is that you need to decide the metrics you want to capture, find the correct DMV to use, and then plan how to use it.
System Stored Procedures
Included in the master database when SQL Server is installed are around 1,000 system stored procedures to help with admin tasks and provide added metrics out-of-the-box. Just as the names of DMVs are prefixed with sys.dm, so the T-SQL for system-stored procedures always begins with
sp_ , which denotes “special.”
Three of the most useful are
The first returns a list of databases that exist on the server, along with their name, size, owner, date created, and current status; the second shows the name, size, and unallocated space for the currently selected database; and the third lists the active SQL processes, along with any blocking activity, users and current sessions.
While useful, the information relates to a single server, and is only available as a point-in-time snapshot. Microsoft does, however, provide a handy list of Database Engine Stored Procedures, which shows those of most use when monitoring SQL Server.