Creating a performance baseline

Baseline refers to the normal or typical state of your SQL Server and environment performance. This baseline is very important to you for numerous reasons, and these are as follows:

• When you start troubleshooting the server, you need to know how your server will behave toward something odd.
• You can proactively tune the system if you find a weak spot in the baseline.
• When you plan to upgrade your server, you know how the load was increasing over time, so you can plan properly.

As a matter of fact, you won't have just one single baseline, but you can create multiple baselines depending on the variable workload. In such a case, you will have a baseline for the following:

• Business hours
• Peak usage
• End of week/month/quarter due to closures; reporting
• Weekend

Creating a performance baseline and capturing performance information for your server is, hence, a very crucial task and should be deployed to each of your servers. There are numerous sources that you can use to collect useful information about your SQL Server, and these include the following:

• Windows Performance Monitor
• SQL Server Dynamic Management Views
• SQL Server Catalog Views
• SQL Server Extended Events

With the Windows Performance Monitor, you can capture many different performance counters that are related not only to the SQL Server but also to the operating system counters and hardware (HW) resource counters such as CPU, disk, network, and so on. The list of counters can be quite large, but you should select only those counters that are important to you and keep yourself from overwhelming your data collection. The Performance Monitor can be very useful for log correlation as you can load the performance data to other tools such as the SQL Server Profiler or the Performance Analysis of Logs (PAL) tool.

Some interesting counters worth capturing at the operating system level would include the basic subsystems—memory, CPU, and disk, which can be correlated together to have a better overview of the system's performance. These include the following:

• Processor: % processor time
• System: Processor queue length
• Memory: Available MB
• Memory: Pages/sec
• Physical Disk: Disk reads/sec
• Physical Disk: Disk writes/sec

Although there are numerous counters available, don't get overwhelmed by choosing too many of them. One of the possible suggestions for a list of counters is Jimmy May's list, available at https://docs.microsoft.com/en-us/archive/blogs/jimmymay/perfmon-objects-counters-thresholds-utilities-for-sql-server. Although this list is more than 10 years old, it still provides a good baseline target for your SQL Server.

These counters will give you a very basic overview of the system's performance and must be combined with more information to get any conclusion from the values. As a starting operating system performance baseline, these are very useful and can be tracked and stored for historical overview and troubleshooting. Of course, you need to consider many factors such as change in the system load during business hours, after business hours, and weekends. There may be some peaks in the values in the mornings, and during some maintenance, backup, and so on. So, understanding what your system is doing over time is an essential part in reading the performance baseline.

There are numerous SQL Server counters available in the Performance Monitor and it's not necessary to include them all, so we'll again see some basic counters worth monitoring over time to have a baseline that we can use for troubleshooting. These would include the following:

• SQL Server: Buffer manager—buffer cache hit ratio
• SQL Server: Buffer manager—page life expectancy
• SQL Server: Memory manager—total server memory (KB)
• SQL Server: Memory manager—target server memory (KB)
• SQL Server: Memory manager—memory grants pending
• SQL Server: Access methods—full scans/sec
• SQL Server: Access methods—index searches/sec
• SQL Server: Access methods—forwarded records/sec
• SQL Server: SQL statistics—SQL compilations/sec
• SQL Server: SQL statistics—batch requests/sec
• SQL Server: General statistics—user connections
• SQL Server: Locks—Lock Waits/sec
• SQL Server: Locks—Number of Deadlocks/sec

Another tool that you can use is SQL Server Dynamic Management Views (DMVs), which can return the state of SQL Server and its objects and components. You can query the DMVs with the SQL language as with any other table, and, most of the time, you'll combine several of the views to have better information, as illustrated in the following code snippet:

SELECT * FROM sys.dm_exec_requests er

JOIN sys.dm_exec_sessions es

ON er.session_id = es.session_id

-- remove all system sessions and yourself

WHERE es.session_id > 50 and es.session_id != @@SPID

This simple query as an example will combine two DMV views together to display all user requests/sessions with all information stored in these two views, excluding all system sessions connected to SQL Server and your query window. For a baseline, you shouldn't use all the columns as you will store quite a lot of data, and you should limit your queries only to important parts.

Some important DMVs worth investigating and capturing for a baseline include the following:

• sys.dm_io_virtual_file_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_missing_index_details
• sys.dm_os_wait_stats
• sys.dm_os_sys_memory
• sys.dm_os_process_memory

  Note

  You can find many ready-to-use DMV queries online. An awesome source is a list of queries compiled by Glenn Berry, which are available on his blog, https://www.sqlskills.com/blogs/glenn/category/dmv-queries/. Another great tool that is available for free is WhoIsActive by Adam Machanic, available at http://whoisactive.com/, which queries multiple DMVs at the same time to provide a current view of system performance.

If you schedule a data collection of these values to some monitoring database with a reasonable schedule, you can see how the performance of the system changes over time; by combining all of these, you can have a comprehensive overview of your system.