Creating a performance baseline

Once you have deployed and configured your SQL Server, you can create a performance baseline. This baseline is very important for you for numerous reasons and they are as follows:

  • When you start troubleshooting the server, you need to know how your server will behave to 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

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 extended events

With Windows performance monitor, you can capture many different performance counters that are related not only to the SQL Server, but also to the whole system--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 overwhelmed data collection. Performance monitor can be very useful for log correlation as you can load the performance data to other tools such as SQL Server profiler or Performance Analysis of Logs (PAL) tool.

Some interesting counters worth capturing on the OS level would include the basic subsystems--memory, CPU, and disk, which can be correlated together to have better insights to the system performance. These would include the following:

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

These counters will give you a very basic overview of the system performance and have to be combined with more information to get any conclusion from the values. As a starting OS performance baseline, these will be 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, 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 performance monitor and it's not needed 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

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

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 and filter out all system sessions connected to SQL Server and your query window and display all user requests/sessions with all information stored in these two views. 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 for important parts.

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

  • sys.dm_io_virtual_file_stats
  • sys.dm_db_index_physical_stats
  • sys.dm_db_missing_index_detais
  • sys.dm_os_wait_stats

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/.

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 is changing over time; by combining all of these, you can have a comprehensive view over your system.

上一章目录下一章