Top 10 FAQs About SQL Server Performance Monitoring

SQL Server performance monitoring is considered by many database administrators to be their most important responsibility. Given that poorly performing servers can negatively impact not only internal operations but also customer-facing applications, tracking SQL performance and mitigating problems quickly are critical to the company’s success and profitability.

As more organizations turn to SQL Server, DBAs are increasingly looking for resources to help them implement a solid performance monitoring strategy. There is an abundance of documentation available to help DBAs jumpstart their SQL Server performance monitoring, but there are a few universal questions almost every DBA wants a fast answer to. Here are answers to 10 of the most frequently asked SQL Server performance monitoring questions.

What is SQL Server performance monitoring?

The main purpose of SQL Server performance monitoring is to ensure consistent performance and high availability of SQL Server instances. Performance monitoring is a two-part process, and both parts are essential to keeping your system up and running optimally:

• Monitoring: Track a wide range of performance metrics against preset thresholds to identify anomalous behaviour and provide a mechanism to inform the DBA with alarms that are specific to the issue and provide enough context to begin the diagnostic process.  

• Diagnostics: The workflow and tools required to establish the source of a performance issue and the data required to remediate the immediate issue. 

• Root Cause Analysis: The insights to look beyond the symptoms and identify underlying issues which triggered the issue and may cause it to re-occur. 

• Trend Analysis: Look at performance over extended periods of time . Use trend analysis to identify problems before they occur, so distinguish between once off and recurring issues or to examine the workload boundaries over time. The latter can be used to predict future infrastructure needs, inform consolidation efforts or right size for planned cloud migration. 

Which database performance monitoring tools are best?

The number of databases a DBA is responsible for makes manual monitoring and diagnostics impractical—maybe even impossible. Fortunately, the latest performance monitoring software solutions are equipped with a ton of features to make performance monitoring manageable, even on a large scale:

• Effective alarm management: Prioritizing alarms means you can tackle the big problems first and ignore false alarms.

• Comprehensive diagnostics: The best performance monitoring tools let you deep-dive into diagnostics. Knowing there is a problem isn’t nearly as helpful as knowing how to fix it.

• Rapid time to value: Each instance collects hundreds of metrics, and storing them on-site will get expensive fast. Consider one of the SaaS performance monitoring solutions for cost savings and scalability.

• Analytics: Robust analytics are a key differentiator between performance monitoring tools. For maximum impact on performance, select a tool that provides extensive metrics for workload, query plan, alarm root cause, and blocking.

• Historical data tracking: Look for a performance monitoring tool that saves historic data. This will speed up troubleshooting because you will know if a problem is recurring and how issues are tracking over time.

• Tuning and query plan optimization: A tool with built-in artificial intelligence will enable you to fine-tune queries and optimize them for a specific instance.

• Health checks: Health checks monitor SQL Server vital signs to let you know you might have a problem—sometimes before the problem even exists. 

If you want a tool with all of the features discussed above, you will need to look to one of the cloud-driven SQL Server performance monitoring solutions. Unmatched in scalability, flexibility, and price, these monitoring tools provide a secure performance monitoring platform to meet every need.

What is the best way to optimize database performance monitoring? 

One of the best ways to optimize performance monitoring is automation. Automating common tasks—such as monitoring disk space, inspecting error logs, and verifying the database backups—will ensure these tasks are completed, which gives the DBA more time to do higher-value work.  

Additionally, you can implement the following to optimize your SQL Server performance monitoring:

• Mobile monitoring: Mobile monitoring is available from your Android and iOS devices. Providing heatmaps, smart alarms, and alarm lists, mobile performance monitoring allows you to diagnose problems and begin triage from anywhere.

• Tuning and query optimization: Performance tuning allows you to visualize, analyze, and optimize your SQL query and execution plans by highlighting the highest-cost operators, offering suggested fixes for issues, and rewriting/optimizing your queries and query plan.

• Health checks: Health checks help you optimize your performance monitoring by pinpointing health issues within each SQL Server instance, including long waits and I/O latency. Health checks also provide historical data, so you can track performance issues over time. 

How should I choose a SQL Server monitoring tool?

There are two main considerations when deciding on a SQL Server performance monitoring tool.

What kind of tool are you looking for?

There are three primary types of performance monitoring tools to consider:

• Custom/built in-house: In this case, keep in mind that just because you can do something doesn’t always mean you should. Yes, you can put together an in-house performance monitoring tool to gather metrics using PerfMon, traces, DMVs, and XEvents. But the cons far outweigh the pros of taking this route. It isn’t scalable, so as your instances increase, your performance monitoring capabilities suffer. Also, it isn’t possible to monitor all the things you need to watch to ensure optimal SQL Server performance. Once you start gathering data, you have to store it somewhere for it to be useful, and that can get expensive. Bottom line - is it more valuable for you to spend your precious time building and maintaining a performance monitoring tool or to spend it fixing and tuning mission critical workloads. 

• Free/open source: This is potentially a better choice than creating your own performance monitoring solution, but there are still limitations. Though you gain a bit of customization, you are still lacking breadth of coverage and pre-configured alerting. You will also still need to provide your own data storage space. It is a complex process to build a tool that can effectively capture large amounts of performance data without impacting the monitored server, set the appropriate thresholds for hundreds of metrics, manage large volumes of data collected and then keep this up to date from one version of SQL server to the next. Focus your time on solving problems and not building performance monitoring systems. If you are depending on community updates support for new versions of a database platform may lag its release. 

• Commercial: Commercial SQL performance monitoring tools offer a range of features and levels of customization, and many are surprisingly affordable. Commercial tools facilitate performance monitoring with options such as health checks, alarm management, performance diagnostics, and mobile monitoring capabilities—features you won’t get from free tools or those built in-house.

What are your needs for your specific environment?

Before selecting a performance monitoring tool, ask yourself these questions to help you pinpoint the tool that meets your specific needs:

• How many instances do you want to monitor?

• Are they collocated or dispersed?

• Are they on-premise or in the cloud (or both)?

• Can you afford a lump sum payment for infrastructure and licensing, or do you need to spread out the cost?

• Do you have infrastructure and database instances available to dedicate to a monitoring tool?

• Do you have time or resources internally to build and maintain monitoring infrastructure?

• Does the tool have the ability to dissect the workload on multiple dimensions instantly, to save you hours of time scripting?

• Is there a smart capability to parse through the data and help you identify specific problems?

• Does the tool provide instant diagnostics, best practices, or diagnosis to the relevant drill down?

What is the best way to monitor the health of database performance?

There are several signs your SQL Server is unhealthy, including:

• General workload slowdown

• Connection/query timeouts

• Excessive CPU usage

• Memory pressure

• Excessive I/O read/write latency

• Excessive transaction log or TempDB size

If you notice any of these symptoms, it’s time to set up health checks to get your system back to peak performance. Some initial steps you can take to identify the cause of performance issues include: 

• Establishing a performance baseline and measuring against it

• Verifying backups

• Checking for missing indexes 

• Determining what percentage of workload is from ad hoc queries

What are the best metrics for SQL Server performance monitoring?

There are hundreds of metrics you can measure to help you track and improve performance, but there are a few standouts that should be part of every SQL Server maintenance plan. Here are a few of the main areas to monitor and the metrics you’ll want to keep an eye on:

• Indexes. Indexes are a frequent source of performance issues because databases are in a constant state of change. Data is added and removed, and indexes are changed and deleted, all contributing to poor performance. These are the metrics to watch:

• Fill factor
• I/O
• Fragmentation

• Buffer cache. It’s important to configure buffer cache to optimize the length of time pages stay there without overdoing it and hogging memory that is needed elsewhere. These are the metrics to watch:

• Page life expectancy
• Buffer cache hit ratio

• T-SQL. Instead of running SQL statements ad hoc, SQL Server batches them, compiles them into an execution plan, and caches them, re-compiling the statements as needed. Excessive compilations and re-compilations can degrade performance. These are the metrics to watch:

• SQL compilations
• SQL re-compilations

• Other metrics to watch closely:

• Locks waits and blocked processes
• CPU usage 

What are common problems in database performance monitoring?

Performance bottlenecks

Performance bottlenecks manifest as slow or unresponsive databases. Bottlenecks can be tricky to locate because they may be memory-, storage-, or processor-related. However, there are a few places you should look for problems first:

• WRITELOG wait-time
• NESTED LOOP JOIN
• TempDB contention

Low page life expectancy (PLE)

Low PLE hurts performance because SQL Server has to go to disk more frequently to find data files, which takes far longer than reading them from the buffer cache.

Fragmentation

Check your indexes for excessive fragmentation. SQL Server performance suffers when it has to search scattered and partially filled pages to find data.

How should I set up my database performance monitoring system?

Once you’ve decided on a SQL Server performance monitoring solution, it’s time to get it up and running. Although you may want to dive in to all the shiny new features right away, taking a step back and approaching the process methodically will help ensure you are getting the most from your system. Here are some tips for onboarding your new performance management tool:

• Decide which instances need your attention most

• Run through the prerequisites 

• Know your system requirements

• Download the installer

• Add connections

What queries can I use for SQL server performance monitoring?

The queries you use for performance monitoring will vary based on the metrics you are tracking. However, detecting memory issues, assessing index defragmentation, and identifying unacceptable wait times are likely at the top of every DBA’s performance monitoring checklist. Microsoft’s SQL Server documentation provides details for a number of queries to help you implement your performance monitoring plan:

• Memory usage 

• Index defragmentation 

• Wait stats 

What are the best practices of database performance monitoring?

Some people disagree with the term “best practices,” but when it comes to SQL Server performance monitoring, there is no arguing that there are certain things a DBA has to do to keep the system healthy and the users happy.

Make index maintenance a priority

Neglecting your indexes is a sure-fire way to generate performance issues. Indexes are the source of common performance killers, including fragmentation and page splits.

Know your workload

It’s hard to know when you have a problem if you don’t know what “normal” looks like. Take some time to get to know your workload by asking questions like: 

• When is this instance the busiest?

• What applications use this instance?

• When was the last backup?

• Does this instance have known performance challenges?

Choose a performance monitoring tool that meets your specific needs

Before you commit to a SQL Server performance monitoring tool, you first need to define your needs. Once you understand what you are trying to achieve with your performance monitoring tool, you can confidently seek out a solution with the features that meet your requirements.

Find and fix performance bottlenecks

Obvious symptoms of performance bottlenecks include using too much memory, long wait times, index fragmentation, and too many or too few indexes. Finding and fixing these bottlenecks quickly is one of a DBA’s primary responsibilities. The best way to keep database performance at peak health is to implement a performance monitoring tool that helps you easily locate and remediate performance bottlenecks and other sources of performance degradation.