In the world of SQL Server performance tuning, intuition is helpful—but data is essential. Without a solid performance baseline, DBAs are left guessing whether a system slowdown is an anomaly or just another busy working day. Creating a baseline gives you a point of comparison to understand what "normal" looks like for your SQL Server environment. This article will guide you through what to capture, how to capture it, and how to turn that information into actionable insight— so the next time someone says, "the database is slow", you will have data to prove or disprove it.
Figure-1: Baseline creation
Why Create a SQL Server Baseline
- Know What "Normal Looks Like - Without a baseline, you are guessing whether CPU at 70% is good or bad. A baseline gives context—what is normal for your workload, your business hours, your month-end load.
- Detect Performance Degradation Early - Baselines let you see when performance metrics deviate from usual patterns—helping you spot issues before users start complaining.
- Simplify Troubleshooting - When an alert fires or someone reports slowness, you can compare current stats to your baseline to isolate anomalies quickly.
- Data-Driven Decision Making - Need to justify a hardware upgrade? Your baseline shows resource consumption trends and helps you present a clear, objective case.
- Measure the Impact of Changes - Whether you have updated an index, deployed a new query, or moved to a new VM—baselines let you validate whether it helped or hurt.
How to Build the Baseline
You can build a baseline in couple of ways like:
- DMVs - SQL Servers exposed different performance metrics through DMVs. You can periodically collect the metrics using Agent Jobs and analyze the trends. Key DMVs are:
- sys.dm_os_performance_counters
- sys.dm_exec_requests
- sys.dm_os_wait_stats
- sys.dm_db_index_usage_stats
- First Responder Kits - Brent Ozar's superb tool First Responder Kits is my number one choice for troubleshooting SQL Server. You can log the snapshot of sp_BlitzFirst / sp_BlitzCache / sp_Blitz into tables, monitor the trends and make the baselines.
- DBA Dash - David Wiseman's free and opensource tool is my number one choice for monitoring SQL Servers. It collects and displays different metrics in its beautiful dashboard. You can see the counters in tabular format and charts which will help you to take proactive actions. Simply, you can use this as your baseline.
- Third Party Paid Tools - There are different paid tools for monitoring and benchmarking SQL Server performances. Like SentryOne, Redgate SQL Monitor, SolarWinds DPA, Idera SQL Diagnostic Manager etc.
- Perfmon Counters - Build-in Windows tool to track OS and SQL counters. You can setup Data Collector sets to log metrics like CPU, memory, disk, and SQL Server stats, Capture data periodically and analyze them. You will walk through more on this in upcoming sections.
Perfmon Counters
You can capture perfmon counters for creating a baseline of your workload. Focus on key performance areas. Start with:
Figure-2: Perfmon Counters
- Processor - % Processor Time, % Privileged Time
- Memory - Available MBytes, Pages/sec
- Physical Disk - % Disk Time, Current Disk Queue Length, Disk Transfers/sec, Disk Bytes/sec
- System - Processor Queue Length, Context Switches/sec
- Network Interface - Bytes Total/sec
- SQLServer:Access Methods - FreeSpace Scans/sec, Full Scans/sec
- SQLServer:Buffer Manager - Buffer cache hit ratio
- SQLServer:Latches - Total Latch Wait Time (ms)
- SQLServer:Locks - Lock Timeouts/sec, Lock Wait Time (ms), Number of Deadlocks/sec
- SQLServer:Memory Manager - Memory Grants Pending, Target Server Memory (KB), Total Server Memory (KB)
- SQLServer:SQL Statistics - Batch Requests/sec, SQL Re-Compilations/sec
- SQLServer:General Statistics - User Connections
Creating Baseline using Perfmon Counters
You can build your baseline using perfmon counters. Open Run window and type "perfmon" which will open the perfmon window.
- Right Click on "User Defined" and choose New -> Data Collector Set. Type a name of the collector set and pick "Create maually (Advanced)" then click Next as shown in figure-3.
Figure-3: New Collector set
- Choose "Performance counter" and click Next as shown in figure-4.
Figure-4: Pick Performance counter
- Add all the metrics depicted in Figure-2 of "Perfmon Counter" section as shown in Figure-5. Click Next and press finish.
Figure-5: Add Appropriate counters
- Now right click on Collector Set's properties and schedule the activity from Schedule window as shown Figure-6.
Figure-6: Schedule the Collector Set
- Right click on the Collector Set's name from Reports section and you will view the report as shown in Figure-7
Figure-7: View the report
Performance Improvement of Perfmon Counters
- Monitoring many performance counters too frequently can add overhead to your system. Most of this overhead comes from tracking too many performance objects, not from the number of counters per object. So, it’s important to select only the essential objects based on your monitoring goals.
- Use counter logs instead of real-time Performance Monitor graphs to reduce system overhead. Real-time graphing is more resource-intensive and should be used only for short-term monitoring or troubleshooting. Counter logs collect data at intervals, making them a more efficient option for performance tracking.
- To minimize overhead, run Performance Monitor remotely from another machine instead of directly on the SQL Server. Connect to the SQL Server by specifying its computer name or IP. Even then, avoid live graph viewing—prefer using counter logs to analyze collected data efficiently and with less impact on server performance.
- Using counter logs avoids the overhead of live graphing, making it more efficient to collect data locally on the SQL Server (but on a different disk than SQL data/log files). Once collected, copy the log to another machine for analysis, ensuring no extra I/O load is placed on the production system.
- For baseline monitoring, use a longer sampling interval (e.g., 60 seconds) to reduce log file size and disk I/O. Shorter intervals are useful for diagnosing timing issues but generate more granular and heavier data. Choose the interval based on your monitoring goal—balance detail vs. system impact.
Important Notes about Baseline
A database application's behavior evolves over time due to changes like
- data growth
- user increase
- software updates, or
- hardware changes.
So your old baselines lose relevance. To stay accurate, update your baselines regularly and archive old ones for identifying long-term trends and patterns.
Final Words
Creating and maintaining a SQL Server baseline is not a one-time task—it is a continuous commitment to understanding your server’s behavior. A well-established and regularly updated baseline empowers DBAs to detect anomalies early, troubleshoot efficiently, and plan proactively. In the ever-changing landscape of workloads and infrastructure, your baseline is the compass that helps you navigate performance confidently.
References
Going Further
If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too!
Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.
Let’s make your SQL Server to take your business Challenge!
For any queries, mail to mamehedi.hasan[at]gmail.com.