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:
- In-lines T-SQL’s module for each performance metrics
- Details the use of SQL 2017 STRING_AGG string function
- Includes the use of DMF sys.dm_db_log_info
- Display consolidated data using T-SQL
- 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…
https://www.sqlshack.com/analyze-sql-server-database-performance-using-t-sql/
Happy Learning!!
