Microsoft SQL Server is still considered as one of the best relational databases. To maintain SQL Server efficiently, it is very important to perform SQL Server monitoring. A good SQL Server monitoring plan can assist you in managing the SQL server database effectively. And by implementing a monitoring solution, it gets easier to diagnose any memory or I/O issues on your SQL Servers. Monitoring helps in knowing the server’s resource limits such as storage space, memory, I/O capacity, and also in monitoring current resource usage. With effective monitoring and reporting, it is easy respond to an emergency before the crisis escalates. Every SQL Server DBA follows a list of checks to ensure that all their systems are running smoothly. And here is that checklist.

Have a Backup for all Your SQL Server Databases

Before starting the monitoring process, it’s necessary to backup all your SQL databases so that you always have access to your databases even if things go wrong. However, if you already have a backup and unable to access it, then use SQL Backup Recovery tool to directly restore SQL backup smoothly.

Download

Look for Errors in SQL Server Error Log

The SQL Server error logs show error messages and informational messages. Check the SQL Server Error Log by making use of the undocumented extended stored procedure, xp_readerrorlog. With the help of this query, it gets easier for you to look at the current log and look for any errors.

Check the Status of SQL Agent Jobs

It’s something that is to be done every day. You can do this using the Job Activity Monitor. It will give you the details of all the jobs.

Check SQL Server Memory

Monitoring SQL memory regularly helps to avoid memory bottlenecks and ensure high performance. To check the current memory allocation, use:

SELECT
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;

Monitor Disk Space on SQL Server

It is very essential for you to look at the space left on your drives. For this, you need to monitor two parameters- the current size, and the auto growths. You can make use of the extended stored procedure xp_fixeddrives to get an idea about free space left on any disk on SQL Server. Also, you can monitor auto-growths using trace files or performance monitor.

Test Your Monitoring Scripts and Queries

Queries are run automatically against all servers and databases, so it’s crucial to understand what impact it will have on your server when you run it. Also, it is normal for queries to become resource-intensive when each of them is run against one hundred databases on a server. So, it is good to verify them before running.

Count the Connections to the SQL Server Instance

You can collect the information on database connections using the performance monitor counters. There are some other ways also for this- one such a method is sys.dm_os_performance_counters DMV.
Some other key areas you require to monitor SQL Server are:

  • Buffer Cache hit ratio: This is the percentage of pages in the cache. The counter can be improved by adding more RAM.
  • Locks – Average Wait Time: This counter displays the average time required to obtain a lock. You got to note that its value must be as minimum as possible. If remarkably high, you got to look for processes blocking other processes. In addition, you would also have to examine your users’ T-SQL statements, and check for any other I/O bottlenecks.

Conclusion

Monitoring SQL Server and managing the databases is a complicated task, so having a complete checklist before starting the monitoring process is vital. In this article, we provided an essential checklist for the SQL server monitoring, however, if you’re an experienced DBA, then your checklist might be a little longer than this.