Microsoft SQL Server databases power many applications that are the lifeblood of businesses in today’s data-driven world. Whether your servers are on-premises or hosted in Azure, your company relies on the availability of your SQL Server instances to power your applications. However, poorly performing databases can have a negative impact on user experience and revenue generation and slow productivity throughout your business.
SQL Server performance monitoring is an ongoing process of measuring, analyzing, and tuning the performance of your SQL Server databases. Monitoring key performance indicators such as CPU usage, query performance, and resource utilization can help DBAs identify bottlenecks, troubleshoot performance issues, and ensure that the database is operating at peak performance before they become more serious and more expensive issues.
Today’s applications require high availability and near-instantaneous response times. When your SQL Server database lags, the entire infrastructure feels it. Customers wait and become frustrated, transactions time out, and business comes to a screeching halt. Industry reports have put the price tag of unexpected database downtime at thousands of dollars a minute for enterprises.
Performance monitoring helps you track server health, resource utilization, and performance trends. It allows you to spot and fix performance issues before they affect end-users, find resource-intensive queries that use too much CPU or memory, and tune query performance with better execution plans and indexing. It also helps you with planning for capacity by analyzing workload patterns and capacity trends and saving on infrastructure costs by optimizing current resources.
DBAs can focus on being proactive in order to minimize performance glitches and downtimes by keeping SQL Server databases at optimal performance.
Ready to take control of your database performance? Paessler PRTG offers comprehensive SQL Server performance monitoring with preconfigured sensors that track all critical metrics out of the box. Monitor query execution times, CPU usage, buffer cache, and much more from a single dashboard.
👉 Download your free trial of PRTG and start optimizing your SQL Server instances in minutes.
It is as important to know what to monitor as it is to have the tools to monitor it. SQL Server provides a number of performance data metrics but honing in on the metrics that are going to help you fix problems as well as optimize database performance are the most important.
When users say the app is running slowly, performance of the SQL queries is usually where most people start troubleshooting. Monitoring SQL can help you to locate badly-written queries, missing indexes, or queries that are running slow because of a change in an execution plan.
⚡ Query execution time: Measures how long queries take to complete
⚡ Batch requests per second: Shows the number of SQL statements processed
⚡ SQL compilations per second: Tracks how often query plans are compiled
⚡ Stored procedure performance: Monitors execution speed of database functions
Query Store is a feature available in recent releases of SQL Server to provide more performance detail and an easier way to find regressions in query plans.
SQL Server performance depends on hardware resources. Tracking CPU, memory, and disk I/O usage can help you identify if your performance issues are due to resource bottlenecks.
💠 Monitoring CPU utilization can help you identify if high CPU usage is caused by poorly written queries or insufficient CPU resources.
💠 Memory utilization monitoring should focus on the buffer cache hit ratio and page life expectancy, which are two key metrics for memory health.
💠 Disk I/O monitoring should track read/write throughput and disk queue length. TempDB usage should also be monitored for excessive use of temporary storage.
Microsoft recommends the buffer cache hit ratio should always be at or above 95%. Lower than this indicates a memory pressure and can lead to performance issues.
SQL Server has different properties that need to be tracked for effective monitoring. Deadlocks are tracked to locate locking issues that lead to uncompleted transactions. Wait time can be analyzed to identify what a query is waiting for. Index fragmentation can be tracked to keep queries from slowing down due to inefficient indexes. Transaction log size can be tracked to ensure that there is space for the database to operate.
Wait statistics is useful information for tuning performance. Typical wait types will show if your database is being affected by CPU pressure, I/O bottlenecks, or lock contention.
DBAs have access to both built-in Microsoft tools and third-party monitoring solutions. Each serves different purposes in a comprehensive monitoring strategy.
SQL Server Monitoring can be accomplished using many native tools from Microsoft that are available via SQL Server Management Studio (SSMS). Activity Monitor provides real-time information about processes, resource waits, and expensive queries. The Performance Dashboard provides built-in reports on the server configuration and performance metrics.
Dynamic Management Views (DMVs) are special views that can be queried to retrieve detailed performance information about the system. Extended Events is a lightweight tracing system that can be used to capture specific database events. SQL Server Profiler can capture and analyze workload for troubleshooting. This tool is now deprecated, but still included in current versions.
Activity Monitor is a tool that many DBAs go to first when trying to troubleshoot a performance issue. It provides immediate visibility into what expensive queries are currently active, what resource waits are occurring, and I/O activity at the data file level.
Performance counters provide numerical data about SQL Server operations. The operating system and SQL Server expose hundreds of counters tracking everything from batch requests to lock wait time. Key DMVs that every DBA should know include sys.dm_exec_query_stats for performance statistics on cached queries, sys.dm_os_wait_stats for cumulative wait statistics across the instance, sys.dm_db_index_physical_stats for index fragmentation and size information, and sys.dm_io_virtual_file_stats for I/O statistics on data and log files.
These DMVs provide the foundation for many third-party monitoring tools and custom T-SQL scripts.
While built-in tools are helpful, they’re often reliant on manual checks with no automated alerting. Professional monitoring services like PRTG can continuously monitor your databases with automated alerting, and historical performance trends.
💎 Real-time dashboards: Keep an eye on all critical metrics in one centralized location
💎 Customizable thresholds: Set alerts and notifications based on your unique requirements
💎 Infrastructure integration: Monitor your databases along with servers, networks, and applications
💎 Automated reporting: Automatically generate reports and scale to multiple SQL Server instances
💎 Historical baselines: Track performance trends and capacity planning needs
PRTG Network Monitor comes with dedicated sensors for Microsoft SQL Server to help you monitor database performance, query response times, and resource usage. Our monitoring tool integrates with on-premises as well as Azure SQL databases.
Monitoring gives you the data, performance tuning lets you take action with that data. Below are some battle-tested tips for tuning SQL Server performance.
Badly written queries are the number one cause of performance problems. Use execution plans to find expensive operations such as table scans or nested loops. Review and rewrite T-SQL code to simplify and remove unnecessary logic. Add missing indexes based on real query patterns, not hunches. Update statistics regularly for the query optimizer to work with. Avoid functions in WHERE clauses that disallow index usage, and use stored procedures to eliminate compilation cost.
The Database Engine Tuning Advisor can analyze a workload and suggest index enhancements.
SQL Server is designed to use memory aggressively, primarily for caching data pages in memory. Adequate memory allocation is crucial for performance. Ensure SQL Server has enough memory to keep the buffer cache hit ratio high, ideally above 95%. Monitor page life expectancy, with Microsoft suggesting a minimum of 300 seconds per 4GB of RAM. Watch for memory pressure in performance counters and set appropriate max server memory to avoid starving the OS of resources.
Lock contention is a common bottleneck caused by multiple queries attempting to access the same resources simultaneously. Monitor the number of lock waits per second and the average lock wait time to detect blocking issues. Use the right isolation levels and optimize transaction lengths to reduce locking contention. For read-heavy workloads, consider using snapshot isolation to minimize blocking.
Indexes help with queries speed, however they should be also maintained. Use indexes fragmentation percentage and rebuild or reorganize indexes when fragmentation is over the acceptable limit, about 30%. Don't overindex though, which could slow down inserts and updates. DMVs also provide information about missing indexes which could improve queries performance, but take each recommendation carefully.
If you want full-stack monitoring that can cover your entire IT infrastructure, you can count on PRTG Network Monitor. PRTG supports Microsoft SQL Server, MySQL, PostgreSQL, Oracle SQL, and many more so you can monitor your databases right alongside your servers, network devices, applications, and more.
With PRTG you can use sensors that are preconfigured for SQL Server performance monitoring. The WMI Microsoft SQL Server sensor monitors performance counters via Windows Management Instrumentation. The Microsoft SQL v2 sensor runs custom queries and parses the returned values. The ADO SQL v2 sensor connects to any database that supports ODBC or OLE DB.
Define your own threshold values for CPU, query response time, or anything else you’re monitoring. If a value crosses your threshold, you’ll get an email, SMS, or push notification. Automate your monitoring and save time for more important DBA work.
Get a centralized view of all your SQL Server instances with PRTG. Whether they are running on physical servers, virtual machines, or Azure, you can keep an eye on your performance metrics and database performance from one dashboard.
Monitoring and Tuning SQL Server Performance Monitoring SQL Server performance is critical, which is why we need to keep an eye on the health of our databases. By tracking the right metrics, using the proper monitoring tools, and following best practices, we can tune our databases to perform at their best, ensuring fast and reliable application responses.
From a single database or instance to an enterprise with multiple databases, there are significant gains to be had by adding comprehensive monitoring capabilities. You’ll be able to proactively manage performance before your users notice, optimize the use of your resources, and have the insight to plan your capacity accordingly.
Don't wait for performance problems to disrupt your business!
👉 Download PRTG Network Monitor today and experience comprehensive SQL Server monitoring that scales with your infrastructure. With over 250 preconfigured sensors and automated alerting, PRTG makes it easy to keep your databases running at peak performance.