如何使用T-SQL分析SQL Server数据库性能

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.

数据库的性能是数据库优化中最具挑战性和最关键的部分。 每个DBA面临的挑战是确定最消耗资源的数据库 。 本文讨论了本机可用的功能，这些功能可以完成在粒度级别上获取整个数据库的所需详细信息的任务。

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.

使用直接应用于SQL的技术和方法，可以在实例级别收集汇总信息。 当您想获得更高级别的见解时，实例级别的数据有时可能会有所帮助。 当我们使用关键任务系统时，细粒度的细分以及有关CPU，内存和I / O的详细信息总是很有帮助的。 很少有重要指标，我将在短期内讨论。

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
   内联T-SQL的每个性能指标模块
2. STRING_AGG string function STRING_AGG字符串函数的使用
3. Includes the usage of DMF sys.dm_db_log_info
   包括DMF sys.dm_db_log_info的用法
4. Display consolidated data using T-SQL
   使用T-SQL显示合并数据
5. and more …
   和更多 …

我要监视哪些数据库指标？ (What database metrics do I monitor?)

SQL Server Performance monitoring revolves around many key areas

SQL Server性能监视围绕许多关键领域展开

• CPU
  中央处理器
• Memory
  记忆
• Disk I/O
  磁盘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
  DMV的
• SQL Profiler
  SQL事件探查器
• Counters
  专柜
• Activity Monitor
  活动监控
• Windows Perfmon
  Windows性能
• 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

SQL Server捆绑了各种监视选项，但是在某些情况下，您可能需要使用第三方工具。 本文概述了使用本机SQL技术识别SQL实例中资源最密集的数据库的详细信息。 以下SQL已在SQL 2012-2017上经过测试

磁盘I / O管理 (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.

可以说，磁盘I / O主要是使用内置计数器在OS级别进行跟踪的。 这些指标可以大致了解磁盘级别上发生的情况，例如数据总线中使用的带宽。 查询系统视图sys.master_files和动态管理功能sys.dm_io_virtual_file_stats返回数据和日志文件的I / O统计信息。

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.

现在让我们看看如何将每个数据库的一些统计信息汇总到一行中。 XML和STUFF用于在单行中生成逗号分隔的值。 我们还使用SQL Server 2017中的STRING_AGG函数探索相同的功能，仅需几行代码即可得出相同的结果。

Our performance metrics will include:

我们的绩效指标包括：

• Rank- defines the I/O usage rating of the Disk
  等级-定义磁盘的I / O使用率等级
• 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:

使用以下脚本：

 
USE MASTER
GO
 
DECLARE @DML nvarchar(MAX)
 
DECLARE @SQLShackIOStatistics TABLE
(
[I/ORank] [int] NULL,
[DBName] [nvarchar](128) NULL,
[driveLetter] [nvarchar](1) NULL,
[totalNumOfWrites] [bigint] NULL,
[totalNumOfBytesWritten] [bigint] NULL,
[totalNumOfReads] [bigint] NULL,
totalNumOfBytesRead [bigint] NULL,
[totalI/O(MB)] [decimal](12,2) NULL,
[I/O(%)] [decimal](5, 2) NULL,
[SizeOfFile] [decimal](10,2) NULL
)
SET @DML='
WITH SQLShackIOStatistics
AS
(
select 
db_name(mf.database_id) as dbname, 
left(mf.physical_name, 1) as driveLetter, 
sum(vfs.num_of_writes) [totalNumOfWrites],
sum(vfs.num_of_bytes_written) [totalNumOfBytesWritten],
sum(vfs.num_of_reads) [totalNumOfReads], 
sum(vfs.num_of_bytes_read) [totalNumOfBytesRead], 
cast(SUM(num_of_bytes_read + num_of_bytes_written)/1024 AS DECIMAL(12, 2)) AS [TotIO(MB)],
MAX(cast(vfs.size_on_disk_bytes/1024/1024.00 as decimal(10,2))) SizeMB
from sys.master_files mf
join sys.dm_io_virtual_file_stats(NULL, NULL) vfs
on mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
GROUP BY mf.database_id,left(mf.physical_name, 1))
SELECT 
	ROW_NUMBER() OVER(ORDER BY [TotIO(MB)] DESC) AS [I/ORank],
	[dbname],
	driveLetter,
	[totalNumOfWrites],
	totalNumOfBytesWritten,
	totalNumOfReads,
	totalNumOfBytesRead,
	[TotIO(MB)] AS [I/O(MB)],
	CAST([TotIO(MB)]/ SUM([TotIO(MB)]) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O(%)],
	SizeMB
	FROM SQLShackIOStatistics
	ORDER BY [I/ORank]
OPTION (RECOMPILE)
'
INSERT INTO @SQLShackIOStatistics
EXEC sp_executesql @DML
 
--SELECT * FROM @SQLShackIOStatistics
 
 
select [DBName],
[I/O Rank]