How to analyze SQL Server database performance using T-SQL

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 a 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 use 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 of 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

Further reading…

Happy Learning!!


About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: jayaram/ Connect Me: Twitter @prashantjayaram GMAIL The articles are published in:
This entry was posted in SQL Server 2017, sysfiles, T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s