对于在我们关注下的所有数据库，在日志维护方面，我们的首要目标是最优化写性能，为了支持SQL Server写入日志的所有活动，包括数据修改，数据读取，索引维护等等。但是，留意下可能的日志碎片也是重要的，如前面文章介绍的，它会影响需要读取日志的过程性能，例如日志备份和故障恢复过程。
已经配置了合适的物理日志架构和日志文件大小，如第8篇所讲的，这篇文章会介绍一些可以监控事务日志大小、增长和活动的方法，为了即时收到异常警告或日志暴涨，日志碎片等等。
注意，我们把这篇文章作为全面覆盖日志监控的解决方案。例如，我们没有提供使用扩展事件（Extend Events）进行日志、SQL Server数据控制器/管理数据库仓库（自SQL Server 2008以后的版本即有）的监控。可以查看下这篇文章最后的扩展阅读部分来看下这些话题的详细信息。
不管怎样，我们会讨论最常见的监控工具和技术，包括：
- 监控工具——简单介绍Windows Performance Monitor和Red Gate SQL Monitor
- 使用动态管理对象——在服务器、文件、数据库等级别上查看日志活动
- 使用T-SQL或PowerShell脚本——在所有的服务器上统计日志特性和使用率
监控工具
有一些可用的工具，除了别的之外，允许我们在我们的数据库文件监控活动，包括日志文件。这里，我们只简单介绍两个，内建的工具（Windows性能监视器）和第三方工具（Red Gate SQL Monitor）。
Windows性能监视器（Windows Perfmon）
对于监控SQL Server活动一个流行的“内建”工具是Windows性能监视器（Perfmon）。它是一个系统监控工具，对服务器上的内存、硬盘I/O、网络使用的监控提供不同计数器，也包括SQL Server的计数器。通常，DBA或系统管理员会从不同的计数器里配置性能监视器，定期记录统计信息，把数据存入文件，然后导入Excel或类似工具，进行分析。
在多个计数器中，它提供一个数字来衡量磁盘读写性能，也有对日志监控的特定计数器。
在使用性能监视器上，有很多可用的教程，我们不会在这里重复这些细节。另外在TechNet上有个文档，我们建议这个工具的新手来阅读下列文章：
- Brent Ozar写的《SQL Server Permon最佳实践》，一篇这个工具使用的综合教程，对于SQL Server的一些推荐计数器，如何分析和以Excel保存。
- BradMcGehee写的《SQL Server Profile与性能监视器的结合》。性能监视器使用的一个快速上手教程，还有如何用监控数据结合Profile来跟踪数据。
- Brent Ozar的《基线化和基准测试》。一个提供如何使用性能监视器、Profiler概括介绍的视频教程，包括为什么和如何进行基准测试。
谈到如何对保存日志（和数据）文件磁盘的常规测试，我们可以监控下列这些成对计数器：
- Physical Disk\Disk Reads/sec 和 Physical Disk\Disk Writes/sec——我们需要知道这些计数器的值，先建立基线，来看看哪些显著上升，并调查原因。
- Physical Disk\Avg. Disk sec/Read 和 Physical Disk\Avg. Disk sec/Write——读写磁盘的平均时间（毫秒），这些计数器提供磁盘延迟统计信息，可以用来确定潜在的I/O瓶颈。
通常来说磁盘延迟计数器建议：低于10毫秒是优秀，20-30毫秒是好，如果超过50毫秒可以认为是个可能的I/O瓶颈。当然，这些指标取决于你环境里磁盘阵列的规格和配置。
我们简单演示下Perfmon工具的使用，对一个正在频繁写的数据库，设置下Physical Disk\Avg.Disk sec/Read 和 Physical Disk\Avg. Disk sec/Write counters 作为监控的计数器。
例如，我们对第8篇里，重新创建新版本的Persons数据库和表的操作进行监控。
点击开始，运行，输入Perfmon。在【数据收集器集】【用户定义】，右击【新建】【数据收集器集】，选择【手动创建（高级）】，点击【下一步】，选择【创建数据日志】，勾选【性能计数器】，点击【下一步】，点击【添加】，选择刚才说的计数器（选择【PhysicalDisk】），注意定位自己数据库文件的所在硬盘。
插图9.1：配置性能监视器
点击【确定】后，回到向导界面，点击【完成】。
现在我们可以计划执行这个新的数据采集集。当然，这里我右击，点击【开始】（一会后，新建的数据数据收集器开始位置会有绿色播放标志出现）。回到SSMS，我们运行如下所示的代码9.1，创建一个在我们的Persons数据库频繁写。
1 USE Persons 2 GO 3 DECLARE @cnt INT; 4 5 SET @cnt = 1; 6 -- may take several minutes; reduce the number of loops, if required 7 WHILE @cnt < 6 8 BEGIN; 9 SET @cnt = @cnt + 1; 10 UPDATE dbo.Persons 11 SET Email = LEFT(Email + Email, 7000) 12 END;
代码9.1：更新Person表
一旦代码执行完成，回到性能监视器，右键刚才的数据库收集集，点击【结束】，回到【报告】【用户定义】【新的数据收集器集】，定为到刚才对应的报告，如插图9.2所示：
插图9.2：硬盘读写活动快照，使用PerfMon。
你可以选择和取消选择要显示你要想要显示的计数器，通过双击其中任何一代，在图表下的计数器列表里，你可以在图上修改它们的样子，拉伸等等。你可以通过点击图表放大图表区域，拖拽到需要高亮显示的区域，然后点击顶部菜单的放大镜工具（使用底部的滑条回到刚才的区域）。
在图上最活跃的部分是在D盘上的写，那里有我们的日志文件。我们可以看到那个期间，延迟有近40毫秒，有频繁的峰值。我们可以使用在顶部菜单的【更改图形类型】来修改为【报表】方式，报表显示在D盘上的平均延迟为55毫秒，这是一个需要考虑是时间段。当然，很多其他PhysicalDisk的计数器，可以提供你磁盘内部性能的内幕信息，在我们下结论前要好好仔细分析下。
另外，同样的方式，我们也可以收集其他相关的计数器，例如在SQL Server:Databases。这个对象提供日志活动的各种计数器，也包括其他。
- Log File(s) Size (KB) ——数据库中所有事务日志文件的累计大小 (KB)。
- Log File(s) Used Size (KB)——数据库中所有日志文件的累计已用大小。
Red Gate SQL Monitor
如果你使用第三方SQL Server监控工具，很有可能它会对计数器的很多值，收集、存储并分析。插图9.3在Red Gate SQL Monitor里展示了日志文件大小值，对于Persons数据库的日志文件在快速增长，因为不正确的大小和配置的日志。
插图9.3：SQL Monitor报告的快速日志增长
SQL Monitor的一个不错的功能是，与Perfmon相比它更加简单、容易，在不同期间之间比较同类型的活动。在下拉的时间段（Time range）里，我们可以修改时间段，设置自定义的段，从今天（或这个星期）与昨天（或上个星期）进行比较等等。
动态管理视图和函数（DMV、DMF）
很多DMV（动态管理视图或函数的缩写（Dynamic Management Views and Functions））提供SQL Server引擎内部如何使用磁盘I/O子系统，子系统与I/O输出能力及系统性能要求的工作量。例如：
- sys.dm_io_virtual_file_stats——提供所有数据库数据和日志文件的使用率统计信息。它是发现热点的好资源，标识出在不同频道传播的机会。
- sys.dm_io_pending_io_requests——提供SQL Server所有等待完成的I/O操作的列表。
到操作系统级别，DMV的“sys.dm_os_”类型提供在SQL Server和操作系统之间交互的大量不同数据。这个提供了提交请求到操作系统里实际工作的具体工作量呈现。注意，sys.dm_os_wait_stats记录了每次一个在完成它工作需要等待时间的长短，请求的资源。它是用来找出什么引起回话等待的实用DMV，当然包括I/O等待。
DMV的“sys.dm_os_”类型也提供sys.dm_os_performance_counters，它展示了性能计数器，还有在我们系统里的队列。通过不同资源衡量，例如每秒磁盘读写，处理器队列长度，可用内存等等。它帮助我们找出请求给定资源的地方，还有过多要求的理由。
到数据库级别，SQL Sever 2012添加了sys.dm_db_log_space_usage的DMV，提供了一个获得基本事务日志大小和空间使用率数据的非常简单的方式，和通过DBCC SQLPERF(LOGSPACE)返回的类似。
这里，我们只演示3个例子，首先是sys.dm_db_log_space_usage，然后是sys.dm_io_virtual_file_stats，最后是ys.dm_os_performance_counters，显示日志活动和增长的详细信息。
使用sys.dm_db_log_space_usage（仅SQL Server 2012）
如果你已经使用SQL Server 2012，那么获取基本日志大小和空间信息非常简单，如代码9.2所示。我们在Persons2012数据库上运行这个代码，和Persons数据库一样，除了名称。
1 SELECT DB_NAME(database_id) AS DatabaseName , 2 database_id , 3 CAST(( total_log_size_in_bytes / 1048576.0 ) AS DECIMAL(10, 1)) 4 AS TotalLogSizeMB , 5 CAST(( used_log_space_in_bytes / 1048576.0 ) AS DECIMAL(10, 1)) 6 AS LogSpaceUsedMB , 7 CAST(used_log_space_in_percent AS DECIMAL(10, 1)) AS LogSpaceUsedPercent 8 FROM sys.dm_db_log_space_usage;
代码9.2：日志大小和空间使用
使用sys.dm_io_virtual_file_stats
对于每个SQL Server使用的数据库文件，数据文件和日志文件，sys.dm_io_virtual_file_stats 提供了累计的物理I/O统计信息，表明自上一次服务器重启后，用作被数据库读写的文件的使用频率。它也提供了在"I/O停滞"时间形式里一个非常有用的维度，表明用户处理需要等待完成的I/O总时间，在文件问题里。注意这个DMV只衡量物理I/O。从缓存读取的逻辑I/O操作不会在这里显示。这个函数接受数据库ID（database_id）和文件ID（file_id），我们可以用来调查特定文件或数据库，或者我们可以直接返回服务器上的所有数据库。
为了从一张白纸开始，重新运行Persons脚本来删除和重建Persons数据库和表，并插入100万条记录，然后运行代码9.3忘临时表里插入服务器的一些基线数据。
1 SELECT DB_NAME(mf.database_id) AS databaseName , 2 mf.physical_name , 3 divfs.num_of_reads , 4 divfs.num_of_bytes_read , 5 divfs.io_stall_read_ms , 6 divfs.num_of_writes , 7 divfs.num_of_bytes_written , 8 divfs.io_stall_write_ms , 9 divfs.io_stall , 10 size_on_disk_bytes , 11 GETDATE() AS baselineDate 12 INTO #baseline 13 FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs 14 JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id 15 AND mf.file_id = divfs.file_id
代码9.3：在临时表里从sys.dm_io_virtual_file_stats捕获磁盘I/O基线
代码9.4展示对 #baseline表的查询，对Person数据库返回一些读写统计信息。
1 SELECT physical_name , 2 num_of_reads , 3 num_of_bytes_read , 4 io_stall_read_ms , 5 num_of_writes , 6 num_of_bytes_written , 7 io_stall_write_ms 8 FROM #baseline 9 WHERE databaseName = 'Persons'
代码9.4：查询#baseline临时表。
已经提过，这个函数提供的数据是从服务器上一次重启后累积的，换句话说，数据列的值是不断增长的，从上一次服务器重启的时间点。这样的话，数据的单个“快照”并无用处，就本身来说。我们要做的是，建立一个“基线”标准，等待一定的时间，或许当一系列操作完成后，然后建立第二个“基线”并减掉，这样的话，你会看到哪里的I/O是增长的。
重新运行代码9.1来更新我们的Persons表，并运行代码9.5来收集第2批数据，减掉基线数据值（这里我们从输出忽略了一些列，为了可以更直观的看结果）。
1 WITH currentLine 2 AS ( SELECT DB_NAME(mf.database_id) AS databaseName , 3 mf.physical_name , 4 num_of_reads , 5 num_of_bytes_read , 6 io_stall_read_ms , 7 num_of_writes , 8 num_of_bytes_written , 9 io_stall_write_ms , 10 io_stall , 11 size_on_disk_bytes , 12 GETDATE() AS currentlineDate 13 FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs 14 JOIN sys.master_files AS mf 15 ON mf.database_id = divfs.database_id 16 AND mf.file_id = divfs.file_id 17 ) 18 SELECT currentLine.databaseName , 19 LEFT(currentLine.physical_name, 1) AS drive , 20 currentLine.physical_name , 21 DATEDIFF(millisecond,baseLineDate,currentLineDate) AS elapsed_ms, 22 currentLine.io_stall - #baseline.io_stall AS io_stall_ms , 23 currentLine.io_stall_read_ms - #baseline.io_stall_read_ms 24 AS io_stall_read_ms , 25 currentLine.io_stall_write_ms - #baseline.io_stall_write_ms 26 AS io_stall_write_ms , 27 currentLine.num_of_reads - #baseline.num_of_reads AS num_of_reads , 28 currentLine.num_of_bytes_read - #baseline.num_of_bytes_read 29 AS num_of_bytes_read , 30 currentLine.num_of_writes - #baseline.num_of_writes AS num_of_writes , 31 currentLine.num_of_bytes_written - #baseline.num_of_bytes_written 32 AS num_of_bytes_written 33 FROM currentLine 34 INNER JOIN #baseline ON #baseLine.databaseName = currentLine.databaseName 35 AND #baseLine.physical_name = currentLine.physical_name 36 WHERE #baseline.databaseName = 'Persons' ;
代码9.5：自基线衡量后，捕获磁盘I/O统计信息。
在这个例子里，显然我们强制了一些在日志文件上，非常频繁的写活动！通常，找出高I/O积累的原因，并解决问题，是一个比较复杂的过程。如果你怀疑I/O积累率引起的问题，那么行动的第一步是尝试减少全局的I/O到可接受的级别。例如，我们可以从执行的或索引相关的DMV来使用数据，通过调优和索引优化来降低全局I/O。我们也可以增加内存量，这样的话更多的数据库会被缓存，这样的话会减少物理文件都的并发。使用I/O停滞率和数据读写量，我们也可以使用分区，或者把表放到不同的文件组。
最终，不管怎样，高的I/O停滞率直接意味着硬盘I/O子系统不足以处理需要的I/O输出。如果不能把全局的I/O加载降到接受的级别，那么只能尝试增加更多或更快的硬盘，更多或更快的I/O路径，或者调查下I/O子系统配置的潜在问题。
最后，记住这个DMV反馈的数据只是硬盘I/O的SQL Server角度的数据。如果磁盘子系统是共享的，在服务器级别，有其他的应用，另一个应用可能会是拖垮硬盘性能的原因，而不是SQL Server。进一步说，SAN的使用，虚拟软件等等，在SQL Server和具体的硬盘存储之间常会有很多“中介”层。
简答来说，在做出行动前，仔细分析从这个DMV获得的数据，结合考虑下从系统计数器、Profile和其他DMV获得的数据。
使用sys.dm_os_performance_counters
一般来说，如刚才谈到的，使用性能监视器（PerfMon）收集性能计数器最简单。然而，如果你更喜欢在数据库表里保存统计信息，并用SQL在查询的话，sys.dm_os_performance_counters这个DMV是个非常有用的工具。只要写从这个DMV获取数据的查询，增加INSERT INTO CounterTrendingTableName...你就有了初步的监控系统！另外，你总不能直接访问PerfMon，从不同的机器访问它会很慢。
遗憾的时候，使用这个DMV并不一帆风顺，它繁琐的详细介绍不是我们的讨论范围。不过，你可以看下这本可以免费下载电子书《使用DMV进行性能调优》来做参考。
代码9.6简单提供了如何在日志增长或收缩事件上做报表的一个例子。
1 SET @object_name = CASE WHEN @@servicename = 'MSSQLSERVER' THEN 'SQLServer' 2 ELSE 'MSSQL$' + @@serviceName 3 END + ':Databases' 4 5 DECLARE @PERF_COUNTER_LARGE_RAWCOUNT INT 6 SELECT @PERF_COUNTER_LARGE_RAWCOUNT = 65792 7 8 SELECT object_name , 9 counter_name , 10 instance_name , 11 cntr_value 12 FROM sys.dm_os_performance_counters 13 WHERE cntr_type = @PERF_COUNTER_LARGE_RAWCOUNT 14 AND object_name = @object_name 15 AND counter_name IN ( 'Log Growths', 'Log Shrinks' ) 16 AND cntr_value > 0 17 ORDER BY object_name , 18 counter_name , 19 instance_name
代码9.6：捕获日志增长和收缩事件
输出结果表明Persons数据库（初始日志2MB日志大小，自动增长率2MB）正遭受大量日志增长事件，由于100万条记录的插入和在代码9.2里的更新操作。这显然是引起关注的原因，DBA需要调查下日志大小和增长设置，很可能进行一次性的收缩，并调整为合适的大小，如第8篇所介绍的。
T-SQL和PowerShell脚本
对于SQL Server实例，你数据库文件的大小和属性，包括其他东西有很多脚本的方法来监控。这篇文章不会覆盖所有的方法来收集这些信息，我们只谈谈我们需要知道的最好的。
T-SQL和SSIS
在Rodney Landrum的《SQL Server的钓鱼盒》里，他提供了收集服务器行为和数据库信息的各种T-SQL脚本，包括日志和数据文件增长。
- 服务器信息——SQL Server名称，SQL Server版本，排序信息等等。
- 数据库管理——主要做到数据和日志文件增长监控
- 数据库备份——备份运行成功了么？哪些数据库在完整、简单和大容量日志模式？我们在进行完整恢复数据库的日志常规备份么？
- 安全性——谁访问了并做了什么？
- SQL代理作业——哪个会包含这些运行你数据库备份
然后他演示了如何自动化收集这些信息，在所有的服务器间，使用SSIS，把它们保存到DBA专属的数据库里，用来检查和分析。
如果这听起来像你需要的方法，下载这个电子书，连同代码，并使用它。
PowerShell
PowerShell，和服务器管理对象，构成了一个管理和文档化SQL Server数据库的强大的自动化工具。对于DBA来说，学习T-SQL和可视化界面的管理工具，学习梯度很陡峭，但几个短的脚本就可以在你的所有的服务器和所有的数据库版本间收集数据的所有行为。
接下来的2个脚本来自Phil Factor的《PowerShell服务器管理对象：只写一次》。
代码9.7的PowerShell会列出SQL Server实例清单，在这些服务器实例里检查所有的数据库，通过SMO（服务器管理对象）列出：
- 日志文件和路径名称
- 自动增长设置（KB或百分比）
- 当前文件大小（MB），已使用空间量（MB）和最大文件大小（MB）
- 硬盘读数，硬盘写数
- 从磁盘读的字节数，写到磁盘的字节数
直接修改脚本为你的服务器实例名，从系统里的PowerShell里运行它。
1 #Load SMO assemblies 2 $MS='Microsoft.SQLServer' 3 @('.SMO') | 4 foreach-object { 5 if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) 6 {"missing SMO component $MS$_"} 7 } 8 set-psdebug -strict 9 $ErrorActionPreference = "stop" # you can opt to stagger on, bleeding 10 # if an error occurs 11 12 $My="$MS.Management.Smo" 13 @("YourServerPath\InstanceName","MySecondServer") | 14 foreach-object {new-object ("$My.Server") $_ } | # create an SMO server object 15 Where-Object {$_.ServerType -ne $null} | # did you positively get the server? 16 Foreach-object {$_.Databases } | #for every server successfully reached 17 Where-Object {$_.IsSystemObject -ne $true} | #not the system objects 18 foreach-object{$_.Logfiles} | 19 Select-object @{Name="Server"; Expression={$_.parent.parent.name}}, 20 @{Name="Database"; Expression={$_.parent.name}}, 21 Name, Filename, 22 @{Name="Growth"; Expression={"$($_.Growth) 23 $($_.GrowthType)"}}, 24 @{Name="size(mb)"; Expression={"{0:n2}" –f 25 ($_.size/1MB)}}, 26 @{Name="MaxSize(mb)"; Expression={"{0:n2}" –f 27 ($_.MaxSize/1MB)}}, 28 NumberOfDiskReads,NumberOfDiskWrites, 29 BytesReadFromDisk,BytesWrittenToDisk | 30 Out-GridView
代码9.7：使用PowerShell和SMO调查日志文件大小，位置和活动。
我们在网格视图里显示我们的输出，并过滤了只显示“persons”。如果你想输出到Excel，把Out-GridView替换为：
1 Convertto-csv –useculture > Export.csv
如果你在SSMS里运行这个脚本，右击服务器或数据库选择【启动PowerShell】。如果你使用的服务器版本不止2012，那么首先你要下载，导入并安装sqlps模块，这样可以访问Out-GridView和Convertto-csv cmdlets。可以参考下Michael Sorens的文章 ，2个选择1个，替换掉最后行的FormatTable。
代码9.8展示了第2个脚本，调查日志文件碎片。同样，在所有指定的数据库实例上使用SMO来查询每个数据库。它通过T-SQL查询了DBCC LogInfo来获得每个日志文件的VLF数。对于每个数据库，它分组了虚拟日志文件的结果个数，最大的VLF大小单位是MB，最小的VLF单位是MB，所有VLF的平均和总大小。这次我们使用FormatTable来作为输出格式。
1 #Load SMO assemblies 2 $MS='Microsoft.SQLServer' 3 @('.SMO') | 4 foreach-object { 5 if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) 6 {"missing SMO component $MS$_"} 7 } 8 set-psdebug -strict 9 $ErrorActionPreference = "stop" # 10 11 $My="$MS.Management.Smo" # 12 @("YourServerPath\InstanceName","MySecondServer") | 13 foreach-object {new-object ("$My.Server") $_ } | # create an SMO server object 14 Where-Object {$_.ServerType -ne $null} | # did you positively get the server? 15 Foreach-object {$_.Databases } | #for every server successfully reached 16 Foreach-object { #at this point you have reached the database 17 $Db=$_ 18 $_.ExecuteWithResults(‘dbcc loginfo’).Tables[0] | #make the DBCC query 19 Measure-Object -minimum -maximum -average -sum FileSize | 20 #group the results by filesize 21 Select-object @{Name="Server"; Expression={$Db.parent.name}}, 22 @{Name="Database"; Expression={$Db.name}}, 23 @{Name="No.VLFs"; Expression={$_.Count}}, 24 @{Name="MaxVLFSize(mb)"; Expression={"{0:n2}" –f 25 ($_.Maximum/1MB)}}, 26 @{Name="MinVLFSize(mb)"; Expression={"{0:n2}" –f 27 ($_.Minimum/1MB)}}, 28 @{Name="AverageVLF(mb)"; Expression={"{0:n2}" –f 29 ($_.Average/1MB)}}, 30 @{Name="SumVLF(mb)"; Expression={"{0:n2}" –f 31 ($_.Sum/1MB)}} 32 } | Format-Table * -AutoSize
代码9.8：使用PowerShell和SMO调查日志碎片（从输出已经忽略服务器名称）
小结
这个系列的最后一篇文章就回顾了对于日志增长和性能监控，DBA可用的几个工具，包括Windows的性能监视器，第三方工具，动态管理视图（DMV），PowerShell和T-SQL脚本。我们努力提供了每个工具可用做的合理感觉，这样的话，如果这个工具符合你需要的话，你可用进一步深入研究。
维持一个健康的事务日志是每个DBA的基本职责。理想的，这会包括单个日志文件，在特定的RAID 1+0的阵列（或接近你能获得的最理想状态），为了支持最大的写性能和吞吐量。我们必须捕获在典型工作负荷下，描述日志写性能的“基线”统计信息，然后多次监控这些数据，检查不正常的活动，或性能里的突然恶化。
同样，我们也要按当前和预见的数据量定义大小，而不是让SQL Server”通过自动增长事件来管理日志增长。我们应该启用SQL Server的自动增长的便利性，但只作为一个保护措施，当日志增长时，DBA应该收到一个警告，并去调查。通过仔细监控日志增长，我们可以避免日志满的情形，或大量日志碎片，它会降低日志读取的操作性能，例如日志备份和故障恢复过程。
本文转自Woodytu博客园博客，原文链接：http://www.cnblogs.com/woodytu/p/5122492.html，如需转载请自行联系原作者
