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 function in SQL Server 2017, to derive the same results with just a few lines of code.

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

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 MASTERGO 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 SQLShackIOStatisticsAS(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))) SizeMBfrom sys.master_files mfjoin sys.dm_io_virtual_file_stats(NULL, NULL) vfson mf.database_id=vfs.database_id and mf.file_id=vfs.file_idGROUP 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 @SQLShackIOStatisticsEXEC sp_executesql @DML --SELECT * FROM @SQLShackIOStatistics  select [DBName],[I/O Rank] =    STUFF((SELECT ',' + cast(s.[I/ORank] as varchar(3))FROM @SQLShackIOStatistics sWHERE s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,''),physicalName=STUFF((SELECT ',' + s.driveLetterFROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,'') ,FileSizeMB=STUFF((SELECT ',' + cast(s.SizeOfFile as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,'') ,total_num_of_writes=STUFF((SELECT ',' + cast(s.[totalNumOfWrites] as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,''),total_num_of_bytes_written=STUFF((SELECT ',' + cast(s.[totalNumOfBytesWritten] as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,''),total_num_of_reads=STUFF((SELECT ',' + cast(s.totalnumofreads as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,''),total_num_of_Bytes_reads=STUFF((SELECT ',' + cast(s.totalNumOfBytesRead as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,''),[Total I/O (MB)]=STUFF((SELECT ',' + cast(s.[TotalI/O(MB)] as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,''),[I/O Percent]=STUFF((SELECT ',' + cast(s.[I/O(%)] as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,'')from @SQLShackIOStatistics tgroup by [DBName] 

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.

在上图中，应该按顺序读取多值单元格。 例如，对于数据库PythonSample，I / O等级3用于f驱动器。 数据文件大小为520 MB，日志文件大小为328 MB，驱动器上的写入总数为22，写入的字节总数为9733096，依此类推。 同样，G驱动器的I / O等级为10。 写入总数为14，写入的字节总数为29696，依此类推。

使用STRING_AGG (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.

在下图中，我们可以看到查询使用STRING_AGG用很少SQL代码行显示了相同的结果。

 select [DBName],STRING_AGG( [I/ORank],',')  [I/O Rank],STRING_AGG(DriveLetter,',') physicalName,STRING_AGG(SizeOfFile,',') FileSizeMB,STRING_AGG(totalnumofwrites,',') total_num_of_writes,STRING_AGG(totalnumofbyteswritten,',') total_num_of_bytes_written,STRING_AGG(totalnumofreads,',') total_num_of_reads,STRING_AGG(totalNumOfBytesRead,',') total_num_of_reads,STRING_AGG([TotalI/O(MB)],',') [Total I/O (MB)],STRING_AGG([I/O(%)],',') WITHIN GROUP (ORDER BY [DBName] ASC) [I/O Percent]from @SQLShackIOStatisticsgroup by [DBName] 

SQL用户连接 (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.

可以使用多种方法来监视SQL Server中的用户连接。 本节查询系统视图以获取每个数据库的用户连接。

 USE MASTERGO DECLARE @DML nvarchar(MAX)DECLARE @SQLShackUserConn TABLE(DBName [nvarchar](128) NULL,No_Of_Connections [int] NULL) SET @DML='SELECT DB_NAME(dbid) DBName,COUNT(*) No_Of_Connections FROM sys.sysprocesses --where kpid>0group by DB_NAME(dbid)ORDER BY DB_NAME(dbid) DESC OPTION (RECOMPILE)' INSERT INTO @SQLShackUserConnEXEC sp_executesql @DML select * from @SQLShackUserConn 

缓冲池内存管理 (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.

有很多系统DMV可用于以很高的级别收集信息。 这个想法是在数据库级别收集缓冲池管理的详细信息。 sys.dm_os_buffer_descriptors DMV对于读入内存并缓存的每个数据页都有一行。 它用于确定缓冲池的组织和使用方式。

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

注意： is_modified 位为1表示从磁盘读取页面后页面已被修改。

 USE MASTERGO  DECLARE @DML nvarchar(MAX) DECLARE @SQLShackCacheMemory TABLE([Database_Name] [nvarchar](128) NULL,BufferPageCnt int,BufferSizeMB [decimal](10, 2) NULL,PageStatus varchar(10))  SET @DML='SELECT DBName = CASE WHEN database_id = 32767 THEN ''RESOURCEDB''				ELSE DB_NAME(database_id) END,	Bufferpage=count_BIG(*),	BufferSizeMB = COUNT(1)/128,	PageStatus = max(CASE WHEN is_modified = 1 THEN ''Dirty'' 				ELSE ''Clean'' END)FROM sys.dm_os_buffer_descriptorsGROUP BY database_idORDER BY 2 DESC' INSERT INTO @SQLShackCacheMemoryEXEC sp_executesql @DML SELECT * FROM @SQLShackCacheMemory 

CPU –确定工作量 (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.

使用动态管理视图确定每个数据库的CPU使用率有些棘手。 确定CPU使用率的一种方法是简单地了解数据库使用率，具体取决于命中数据库的查询数量。 这可能不是最佳方法，但其想法是确定查询使用最多CPU时间执行的数据库。

 DECLARE @DML nvarchar(MAX) DECLARE @SQLShackCPUStats TABLE ([row_num] [bigint] NULL,[DatabaseName] [nvarchar](128) NULL,[CPU_Time_Ms] [bigint] NULL,[CPUPercent] [decimal](5, 2) NULL,[RowsReturned] bigint,ExecutionCount bigint) SET @DML='WITH DBCPUStatsAS(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms],  SUM(execution_count)  AS [ExecutionCount],SUM(total_rows)  AS [RowsReturned]FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]FROM sys.dm_exec_plan_attributes(qs.plan_handle)WHERE attribute = N''dbid'') AS F_DBGROUP BY DatabaseID)SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],DatabaseName, [CPU_Time_Ms],CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent],[RowsReturned],[ExecutionCount]FROM DBCPUStatsWHERE DatabaseID > 4 -- system databasesAND DatabaseID <> 32767 -- ResourceDBORDER BY row_num OPTION (RECOMPILE)' --How many Virtual Log Files or VLFs are present in your log file.INSERT INTO @SQLShackCPUStatsEXEC sp_executesql @DML  SELECT * FROM @SQLShackCPUStats 

管理VLF (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.

在SQL 2017中，新的DMF sys.dm_db_log_info将替换DBCC LOGINFO。 该DMF清晰地显示了具有大量VLF的数据库以及许多其他有用的列。

 WITH DatbaseVLF AS(SELECT DB_ID(dbs.[name]) AS DatabaseID,dbs.[name] AS dbName, CONVERT(DECIMAL(18,2), p2.cntr_value/1024.0) AS [Log Size (MB)],CONVERT(DECIMAL(18,2), p1.cntr_value/1024.0) AS [Log Size Used (MB)]FROM sys.databases AS dbs WITH (NOLOCK)INNER JOIN sys.dm_os_performance_counters AS p1  WITH (NOLOCK) ON dbs.name = p1.instance_nameINNER JOIN sys.dm_os_performance_counters AS p2 WITH (NOLOCK) ON dbs.name = p2.instance_nameWHERE p1.counter_name LIKE N'Log File(s) Used Size (KB)%' AND p2.counter_name LIKE N'Log File(s) Size (KB)%'AND p2.cntr_value > 0 )SELECT	[dbName],		[Log Size (MB)], 		[Log Size Used (MB)], 		[Log Size (MB)]-[Log Size Used (MB)] [Log Free (MB)], 		cast([Log Size Used (MB)]/[Log Size (MB)]*100 as decimal(10,2)) [Log Space Used %],		COUNT(b.database_id) AS [Number of VLFs] ,		sum(case when b.vlf_status = 0 then 1 else 0 end) as Free,		sum(case when b.vlf_status != 0 then 1 else 0 end) as InUse		FROM DatbaseVLF AS vlf  CROSS APPLY sys.dm_db_log_info(vlf.DatabaseID) bGROUP BY dbName, [Log Size (MB)],[Log Size Used (MB)] 

使用DBCC LOGINFO (Using DBCC LOGINFO)

 CREATE TABLE #VLFInfo(	  [RecoveryUnitId] int NULL,      [FileId] [tinyint] NULL,      [FileSize] [bigint] NULL,      [StartOffset] [bigint] NULL,      [FSeqNo] [int] NULL,      [Status] [tinyint] NULL,      [Parity] [tinyint] NULL,      [CreateLSN] [numeric](25, 0) NULL) ON [PRIMARY] CREATE TABLE #VLFCountResults(databasename sysname,fileid int, Free int, InUse int, VLFCount int) EXEC sp_MSforeachdb N'Use [?];INSERT INTO #VLFInfoEXEC sp_executesql N''DBCC LOGINFO([?])'';with vlfUse as(select max(db_name()) databasename,fileid,sum(case when status = 0 then 1 else 0 end) as Free,sum(case when status != 0 then 1 else 0 end) as InUse,count(*) cntfrom #VLFInfogroup by fileid)INSERT INTO #VLFCountResultsselect *  from vlfUseTRUNCATE TABLE #VLFInfo'--select * from #VLFCountResults ;WITH DatbaseVLF AS(SELECT DB_ID(dbs.[name]) AS DatabaseID,dbs.[name] AS dbName, CONVERT(DECIMAL(18,2), p2.cntr_value/1024.0) AS [Log Size (MB)],CONVERT(DECIMAL(18,2), p1.cntr_value/1024.0) AS [Log Size Used (MB)]FROM sys.databases AS dbs WITH (NOLOCK)INNER JOIN sys.dm_os_performance_counters AS p1  WITH (NOLOCK) ON dbs.name = p1.instance_nameINNER JOIN sys.dm_os_performance_counters AS p2 WITH (NOLOCK) ON dbs.name = p2.instance_nameWHERE p1.counter_name LIKE N'Log File(s) Used Size (KB)%' AND p2.counter_name LIKE N'Log File(s) Size (KB)%'AND p2.cntr_value > 0 )SELECT	[dbName],		[Log Size (MB)], 		[Log Size Used (MB)], 		[Log Size (MB)]-[Log Size Used (MB)] [Log Free (MB)], 		cast([Log Size Used (MB)]/[Log Size (MB)]*100 as decimal(10,2)) [Log Space Used %],		max(VLFCount) AS [Number of VLFs] ,		max(Free) Free,		Max(InUse) InUseFROM DatbaseVLF AS vlf  INNER JOIN #VLFCountResults b on vlf.dbName=b.databasenameGROUP BY dbName, [Log Size (MB)],[Log Size Used (MB)] DROP TABLE #VLFInfo;DROP TABLE #VLFCountResults 

T-SQL (T-SQL)

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

下一节将上述所有内容编译为一个SQL语句。 合并声明可在找到

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
  CPU –工作量
• Buffer output
  缓冲输出
• User connections
  用户连接

Second-Part of the output includes:

输出的第二部分包括：

• I/O
  输入/输出
• VLF Info
  VLF信息

结论 (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.

本文概述了一些指标，例如数据库内部，CPU使用率，内存分配，用户连接，I / O使用率和VLF。 这使DBA有了资源密集型数据库的想法，从而使他/她能够计划一个更好的策略来处理最关键的数据库。

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

另请参见

附录A (Appendix A)

T-SQL Code

T-SQL代码

 USE MASTERGO  DECLARE @DML1 nvarchar(MAX),@DML2 nvarchar(MAX),@DML3 nvarchar(MAX),@DML4 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 @DML1='WITH SQLShackIOStatisticsAS(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))) SizeMBfrom sys.master_files mfjoin sys.dm_io_virtual_file_stats(NULL, NULL) vfson mf.database_id=vfs.database_id and mf.file_id=vfs.file_idGROUP 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 @SQLShackIOStatisticsEXEC sp_executesql @DML1  --SQL 2017  --select [Database Name],--STRING_AGG( [I/O Rank],',')  [I/O Rank],--STRING_AGG(physicalName,',') physicalName,--STRING_AGG(total_num_of_writes,',') total_num_of_writes,--STRING_AGG(total_num_of_bytes_written,',') total_num_of_bytes_written,--STRING_AGG(total_num_of_reads,',') total_num_of_reads,--STRING_AGG([Total I/O (MB)],',') [Total I/O (MB)],--STRING_AGG([I/O Percent],',') WITHIN GROUP (ORDER BY [Database Name] ASC) [I/O Percent]--from @Aggregate_IO_Statistics--group by [Database Name]    SELECT * FROM @SQLShackIOStatistics --User Connections DECLARE @SQLShackUserConn TABLE(DBName [nvarchar](128) NULL,No_Of_Connections [int] NULL) SET @DML2='SELECT DB_NAME(dbid) DBName,COUNT(*) No_Of_Connections FROM sys.sysprocesses --where kpid>0group by DB_NAME(dbid)ORDER BY DB_NAME(dbid) DESC OPTION (RECOMPILE)' INSERT INTO @SQLShackUserConnEXEC sp_executesql @DML2 select * from @SQLShackUserConn --Memory DECLARE @SQLShackCacheMemory TABLE([Database_Name] [nvarchar](128) NULL,BufferPageCnt int,BufferSizeMB [decimal](10, 2) NULL,PageStatus varchar(10))  SET @DML3='SELECT DBName = CASE WHEN database_id = 32767 THEN ''RESOURCEDB''				ELSE DB_NAME(database_id) END,	Bufferpage=count_BIG(*),	BufferSizeMB = COUNT(1)/128,	PageStatus = max(CASE WHEN is_modified = 1 THEN ''Dirty'' 				ELSE ''Clean'' END)FROM sys.dm_os_buffer_descriptorsGROUP BY database_idORDER BY 2 DESC' INSERT INTO @SQLShackCacheMemoryEXEC sp_executesql @DML3 SELECT * FROM @SQLShackCacheMemory --SELECT * FROM @CacheMemoryDB --CPU DECLARE @SQLShackCPUStats TABLE ([row_num] [bigint] NULL,[DatabaseName] [nvarchar](128) NULL,[CPU_Time_Ms] [bigint] NULL,[CPUPercent] [decimal](5, 2) NULL,[RowsReturned] bigint,ExecutionCount bigint) SET @DML4='WITH DBCPUStatsAS(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms],  SUM(execution_count)  AS [ExecutionCount],SUM(total_rows)  AS [RowsReturned]FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]FROM sys.dm_exec_plan_attributes(qs.plan_handle)WHERE attribute = N''dbid'') AS F_DBGROUP BY DatabaseID)SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],DatabaseName, [CPU_Time_Ms],CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent],[RowsReturned],[ExecutionCount]FROM DBCPUStatsWHERE DatabaseID > 4 -- system databasesAND DatabaseID <> 32767 -- ResourceDBORDER BY row_num OPTION (RECOMPILE)' --How many Virtual Log Files or VLFs are present in your log file.INSERT INTO @SQLShackCPUStatsEXEC sp_executesql @DML4  SELECT * FROM @SQLShackCPUStats  --VLF CREATE TABLE #VLFInfo(	  [RecoveryUnitId] int NULL,      [FileId] [tinyint] NULL,      [FileSize] [bigint] NULL,      [StartOffset] [bigint] NULL,      [FSeqNo] [int] NULL,      [Status] [tinyint] NULL,      [Parity] [tinyint] NULL,      [CreateLSN] [numeric](25, 0) NULL) ON [PRIMARY] CREATE TABLE #VLFCountResults(databasename sysname,fileid int, Free int, InUse int, VLFCount int) EXEC sp_MSforeachdb N'Use [?];INSERT INTO #VLFInfoEXEC sp_executesql N''DBCC LOGINFO([?])'';with vlfUse as(select max(db_name()) databasename,fileid,sum(case when status = 0 then 1 else 0 end) as Free,sum(case when status != 0 then 1 else 0 end) as InUse,count(*) cntfrom #VLFInfogroup by fileid)INSERT INTO #VLFCountResultsselect *  from vlfUseTRUNCATE TABLE #VLFInfo'-- SQL 2017 --;WITH DatbaseVLF AS(--SELECT --DB_ID(dbs.[name]) AS DatabaseID,--dbs.[name] AS dbName, --CONVERT(DECIMAL(18,2), p2.cntr_value/1024.0) AS [Log Size (MB)],--CONVERT(DECIMAL(18,2), p1.cntr_value/1024.0) AS [Log Size Used (MB)]--FROM sys.databases AS dbs WITH (NOLOCK)--INNER JOIN sys.dm_os_performance_counters AS p1  WITH (NOLOCK) ON dbs.name = p1.instance_name--INNER JOIN sys.dm_os_performance_counters AS p2 WITH (NOLOCK) ON dbs.name = p2.instance_name--WHERE p1.counter_name LIKE N'Log File(s) Used Size (KB)%' --AND p2.counter_name LIKE N'Log File(s) Size (KB)%'--AND p2.cntr_value > 0 --)--SELECT	[dbName],--		[Log Size (MB)], --		[Log Size Used (MB)], --		[Log Size (MB)]-[Log Size Used (MB)] [Log Free (MB)], --		cast([Log Size Used (MB)]/[Log Size (MB)]*100 as decimal(10,2)) [Log Space Used %],--		COUNT(b.database_id) AS [Number of VLFs] ,--		sum(case when b.vlf_status = 0 then 1 else 0 end) as Free,--		sum(case when b.vlf_status != 0 then 1 else 0 end) as InUse		--FROM DatbaseVLF AS vlf  --CROSS APPLY sys.dm_db_log_info(vlf.DatabaseID) b--GROUP BY dbName, [Log Size (MB)],[Log Size Used (MB)] --select * from #VLFCountResults ;WITH DatbaseVLF AS(SELECT DB_ID(dbs.[name]) AS DatabaseID,dbs.[name] AS dbName, CONVERT(DECIMAL(18,2), p2.cntr_value/1024.0) AS [Log Size (MB)],CONVERT(DECIMAL(18,2), p1.cntr_value/1024.0) AS [Log Size Used (MB)]FROM sys.databases AS dbs WITH (NOLOCK)INNER JOIN sys.dm_os_performance_counters AS p1  WITH (NOLOCK) ON dbs.name = p1.instance_nameINNER JOIN sys.dm_os_performance_counters AS p2 WITH (NOLOCK) ON dbs.name = p2.instance_nameWHERE p1.counter_name LIKE N'Log File(s) Used Size (KB)%' AND p2.counter_name LIKE N'Log File(s) Size (KB)%'AND p2.cntr_value > 0 )SELECT		db.Servername,		cs.DatabaseName DatabaseName,		db.Status,		db.DataFiles [DataFile(s)],		db.[Data MB],		db.LogFiles [LogFile(s)],		db.[Log MB],		db.TotalSizeMB [DatabaseSize (MB)],		db.RecoveryModel,		db.Version,		isnull(cs.CPU_Time_Ms,0) CPUTimeMs,		isnull(cs.CPUPercent,0) [CPU (%)],		cs.RowsReturned,		cs.ExecutionCount,		isnull(cm.BufferSizeMB ,0) BufferSizeMB,		cm.BufferPageCnt ,		cm.PageStatus,		isnull(uc.No_Of_connections,0) NumberOfConnections,		AIS.physicalName,		AIS.total_num_of_writes,		AIS.total_num_of_bytes_written,		AIS.total_num_of_reads,		AIS.[Total I/O (MB)],		AIS.[I/O Percent],		VR.[Log Size (MB)], 		VR.[Log Size Used (MB)], 		VR.[Log Free (MB)], 		VR.[Log Space Used %],		VR.[Number of VLFs]  VirtualLogCnt,		VR.Free,		VR.InUseFROM @SQLShackCPUStats csleft join @SQLShackCacheMemory CM on cm.Database_Name=cs.DatabaseNameleft join @SQLShackUserConn uc on uc.dbname=cs.DatabaseNameleft join (SELECT	[dbName],		[Log Size (MB)], 		[Log Size Used (MB)], 		[Log Size (MB)]-[Log Size Used (MB)] [Log Free (MB)], 		cast([Log Size Used (MB)]/[Log Size (MB)]*100 as decimal(10,2)) [Log Space Used %],		max(VLFCount) AS [Number of VLFs] ,		max(Free) Free,		Max(InUse) InUseFROM DatbaseVLF AS vlf  INNER JOIN #VLFCountResults b on vlf.dbName=b.databasenameGROUP BY dbName, [Log Size (MB)],[Log Size Used (MB)])VR on VR.[dbName]=cs.DatabaseNameleft join (  select [DBName],[I/O Rank] =    STUFF((SELECT ',' + cast(s.[I/ORank] as varchar(3))FROM @SQLShackIOStatistics sWHERE s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,''),physicalName=STUFF((SELECT ',' + s.driveLetterFROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,'') ,FileSizeMB=STUFF((SELECT ',' + cast(s.SizeOfFile as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,'') ,total_num_of_writes=STUFF((SELECT ',' + cast(s.[totalNumOfWrites] as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,''),total_num_of_bytes_written=STUFF((SELECT ',' + cast(s.[totalNumOfBytesWritten] as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,''),total_num_of_reads=STUFF((SELECT ',' + cast(s.totalnumofreads as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,''),total_num_of_Bytes_reads=STUFF((SELECT ',' + cast(s.totalNumOfBytesRead as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,''),[Total I/O (MB)]=STUFF((SELECT ',' + cast(s.[TotalI/O(MB)] as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,''),[I/O Percent]=STUFF((SELECT ',' + cast(s.[I/O(%)] as varchar(20))FROM @SQLShackIOStatistics sWHERE  s.[DBName] = t.[DBName]FOR XML PATH('')),1,1,'')from @SQLShackIOStatistics tgroup by [DBName])AIS on AIS.DBName=cs.DatabaseNameinner join(SELECT @@SERVERNAME Servername,CONVERT(VARCHAR(25), DB.name) AS dbName,CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles,(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB],(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles,(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB],(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB,convert(sysname,DatabasePropertyEx(name,'Recovery')) RecoveryModel ,convert(sysname,DatabasePropertyEx(name,'Version')) Version FROM sys.databases DB) DB on DB.dbName=cs.DatabaseName--order by io.[I/O Percent],cs.CPUPercent,cm.[Cached Size (MB)]desc   DROP TABLE #VLFInfo;DROP TABLE #VLFCountResults; 

翻译自: