Prashanth Jayaram

How to analyze SQL Server database performance using T-SQL

July 10, 2017 by

The performance of a database is the most challenging and critical part of database optimization. The challenge every DBA faces is to identify the most resource-intensive databases. This article talks about the natively available features that can accomplish the task of getting the required details of the entire database at a granular level.

Using the techniques and methods baked right into SQL, one can collect the aggregate information at the instance level. The instance level data sometimes might be helpful when you want to get an insight at a higher level. When we work with mission-critical systems, the breakdown at granular level and detailed information on CPU, Memory, and I/O is always helpful. There are few metrics that are important, and I’m going to discuss those shortly.

This article describes on how to measure the various performance characteristics of databases. Learn about how:

  1. In-lines T-SQL’s module for each performance metrics
  2. Details the use of SQL 2017 STRING_AGG string function
  3. Includes the usage of DMF sys.dm_db_log_info
  4. Display consolidated data using T-SQL
  5. and more …

What database metrics do I monitor?

SQL Server Performance monitoring revolves around many key areas

  • CPU
  • Memory
  • Disk I/O
  • Also, the factors such as user connections, database transaction rate, and data and log file settings

These factors give an overview of its impact on the performance of the application. This article is an effort to understand the general characteristics of databases; it gives an overview about the key factors used to classify the databases as critical, medium and low usage databases.

There are many tools available to track the database usage details, which are listed below:

  • DMV’s
  • SQL Profiler
  • Counters
  • Activity Monitor
  • Windows Perfmon
  • Third party tools

SQL Server bundles various monitoring options but there will be cases when you would want to turn to third party tools. The article outlines the details the using native SQL techniques to identify the most resource-intensive databases of an SQL instance. The following SQL’s are tested on SQL 2012-2017

Disk I/O Management

In a manner of speaking, disk I/O is primarily tracked at the OS level, using built-in counters. These metrics give a gist of what is happening at the disk level, such as the bandwidth used in the data bus. Querying system view sys.master_files and dynamic management function sys.dm_io_virtual_file_stats returns I/O statistics for the data and log files.

Let us now see how to aggregate some statistics into a single row for each database. XML and STUFF are used to generate the comma separated values in a single row. We also explore the same functionality using the STRING_AGG function in SQL Server 2017, to derive the same results with just a few lines of code.

Our performance metrics will include:

  • Rank- defines the I/O usage rating of the Disk
  • Num of Reads – This tells how many number of reads issued on the file
  • Number of Writes – This talks about the number of writes made on the file
  • Number of Bytes Reads – This is number of bytes read on this file
  • Number of Bytes Written – This gives a value of total number of byes written to a file

Using the following script:

In the figure above, the multi-valued cells are supposed to be read in sequence. For instance, for the database, PythonSample, the I/O rank 3 is for the f drive. The data file size is 520 MB, Log file size is 328 MB, the total number of writes on the drive is 22, and the total number of bytes written is 9733096 and so on. Similarly, the G drive has an I/O rank of 10; total number of writes as 14, Total number of bytes written is 29696 etc.

Using STRING_AGG

In the below figure, we can see that the query has displayed the same result with very few lines of SQL code using STRING_AGG.

SQL user connections

Monitoring user connections in SQL Server can be done using several methods. This section queries the system view to get the user connections for each database.

Buffer Pool Memory Management

There are a lot more system DMVs available to gather the information at very high level. The idea is to gather the details of the buffer pool management at the database level. The sys.dm_os_buffer_descriptors DMV has a row for each data page read into memory and cached. It is used to determine how the buffer pool is organized and used.

Note: the is_modified bit being 1 indicates that the page has been modified after it was read from the disk.

CPU – Identify workload

The use of dynamic management views to determine the CPU usage per database is a little tricky. One way to determine the CPU usage is to simply get an insight of the database usage depending on the number of queries that hit the database. It might not be the best way, but the idea is to identify the database where queries are using the most CPU time for execution.

Manage VLFs

The right configuration of the transaction log is critical to database performance. The log writes all the transactions prior to committing them into the data file. In many cases, the transaction logs grow significantly. Managing and understanding how the transaction log is growing gives a good indication about system performance.

In SQL 2017, a new DMF, sys.dm_db_log_info is going to be replacing DBCC LOGINFO. This DMF gives a clear picture of the databases having high number of VLF’s along with many other useful columns.

Using DBCC LOGINFO

T-SQL

The following section deals with compiling all the above pieces into a single SQL statement. The consolidated statement can be found in Appendix A

Due to the large number of columns, the output is divided into two figures which is shown below

First-Part of the output consists of:

  • Database details
  • CPU – Workload
  • Buffer output
  • User connections

Second-Part of the output includes:

  • I/O
  • VLF Info

Conclusion

This article provides an outline of metrics such as DB internals, CPU usage, Memory allocation, User connections, I/O usage and VLF’s. This gives a DBA an idea of the resource-intensive database to enable him/her to plan a better strategy to handle the most critical databases.

It also describes how transaction logs are being used, and what is being used so that one can start making decisions on how large the transaction log can or should be.

These scripts are not meant to be the ultimate ways to measure the performance of a system. Instead, they provide ways to quickly understand the general performance characteristics of your system, at a glance.

See also Top string functions in SQL Server 2017.

See more

To get 3 free licenses to a SQL Server monitoring tool, download ApexSQL Monitor and fill out this simple survey

References

Appendix A

T-SQL Code



Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
620 Views