Performance is a common reason to monitor SQL Server. The work day of a database administrator is often interrupted with unexpected calls about slowness in applications or reports. But, how does the performance today compare to the performance last week or last month? Can the root cause of the issue be traced to the database or is there something else in the environment causing the problem? Has the performance changed after a recent deployment? Performance is not the most important aspect of managing a SQL Server, but it is often what end users care most about. Slow applications can cause companies to lose customers or make workers less productive. Either way, it costs the organisation money when database performance is not optimal for critical systems.
Windows Server and SQL Server have several built-in tools that can help DBAs figure out what is going on in real time. They can use extended events, perfmon, database management views (DMVs), traces, Query Store, and tools from the community such as sp_whoisactive to troubleshoot performance. If the issue continues after the DBA is notified of the problem, they may be able to find the root cause using these tools. Often, by the time the issue is reported, it’s too late, and they must wait until it occurs again. Wouldn’t it be better to know about an issue before the phone rings?
A DBA’s trusted script library will grow over time as different types of issues come up, but analysing that data presents another challenge. How does this data help the DBA respond quickly to determine the root cause and solve the problem? A dashboard would helpful to tie information from many sources together so that correlations can be made. Building a useful dashboard takes time and effort that, once again, takes away from other important duties.
While it is a difficult job to create a tool to monitor the performance of SQL Server, fortunately, there are monitoring tools available that can be used instead. By using a packaged monitoring tool, the DBA will spend less time figuring out which metrics to collect and writing those scripts, and more quickly getting to the root causes. While it is possible for a DBA to build a monitoring system, it will eventually end up being more expensive than purchasing a monitoring product.
A monitoring tool should:
- Connect server stress conditions, such as high CPU, memory, and I/O utilization, directly to long-running queries
- Compare current performance to baselines
- Analyse server waits
- Connect long-running queries to waits
- Detect blocking and provide easy visualization of the blocking chain
- Alert on significant errors or warnings
- Be easy to install, configure, and use
- Allow the collection of custom metrics
- Feature a dashboard that is intuitive and useful
- Provide built-in and customizable reports
- Pull together information from large estates, including Azure SQL Database, into one view
The right monitoring solution will go a long way towards helping the DBA be proactive and spending less time “fighting fires.” Performance of critical systems directly impacts the bottom line, affecting customer loyalty and employee efficiency.
