使用SQLdiag合并数据采集----最佳实践

23ee

一个常见的最佳实践是保存多个配置的pssd.cab文件，该文件已配置了各种场景，如高CPU使用率、SQL Server超时、长时间运行的查询等。你需要做的就是把cabinet文件里的内容提取到一个文件夹，并改变PSSDIAG.XML配置文件中的Machine Name、Instance Name和ssver值，修改完成后，你就可以收集所需的诊断数据。
准备好长期的数据采集
有时需要长时间收集数据，因为间歇性的问题发生率很难预测。你被迫夜以继日地进行数据采集，以确保为该问题的下一次发生捕捉到所有的相关数据。这就提出一个问题，即数据采集合适开始或停止。Diag Manager通过另一个定制诊断选项Delete Old Trace Files提供了这样的选项。这个定制诊断删除所有文件，除了N个最近的Profiler（.trc）文件和PerfMon（.blg）文件。当你点击Diag Manager UI上的Save按钮时，会提示你提供保留多少个trc和blg文件，如图11-16所示。这对于长时间运行数据采集提供了弹性，不会消耗完磁盘空间。

除此之外，还有其他选项，使你保留更多trace文件，而不会耗完磁盘空间：

• 减少要捕捉的Profiler事件集；
• 使用/C2命令行参数，对产生的输出文件进行后台NTFS压缩；
• 考虑使用/N、/E和/L命令行参数在每天的某个特定时间重启数据采集，为每次重启使用新的输出文件夹。如果问题没有发生，就删除旧的文件夹；
• 考虑使用SQLdiag的内置NT服务支持。这在你不能长时间保持远程桌面会话时非常有用；
• 添加更多本地磁盘空间。
  

下面是不推荐的方法：

• 尝试追踪网络驱动或UNC路径或从远程机器上使用客户端Profiler UI，这种方法非常不利于SQL Server性能。SQLdiag总是抓取服务器端的Profiler追踪，目标输出文件夹应该总在本地磁盘。
• 尝试远程运行SQLdiag以最小化服务器上的空间使用率，这是无助的，因为Profiler追踪总是捕捉本地数据，即使你远程运行。
  

过滤掉杂音
SQLdiag数据采集的大小不成比例地激增或目标SQL Server性能下降的最主要的原因，是添加到数据收集配置中的冗余的或不相关的Profiler事件的数量过高。下面的列表提供了何时避免某个Profiler事件的指导方针：

• Broker------Broker类别的事件都不需要，除非你解决Database Mail或Service Broker相关的问题。
• Cursors------使用这个类别里的事件仅当解决特定的游标相关的问题。
• Locks------如果你要追踪会话的锁数量，那么最好使用DMV，如sys.dm_tran_locks。然而，这种事件类别有一些有用的事件类：
  Lock: Deadlock和Lock: Deadlock Chain----有助于解决SQL Server实例上的死锁问题；
  Lock: Timeout和Lock: Timeout (timeout > 0)----有助于解决因锁阻塞引起的超时相关的问题；
  Lock: Escalation----这个有争议，它有助于找出锁升级，但是在有大量锁升级的服务器上，这会变成一个高频率事件。
• OLEDB------除非你确定问题与OLEDB相关，否则就不要收集该事件类别的任何事件，除了OLEDB错误。因此，如果OLEDB Provider返回了大量错误，你可以使用该事件类的HRESULT来追踪。
• Query Notifications------同Broker。
• Scans------这或许在测试或开发环境中做排序复制中有用，但切勿在生产数据库中使用。
• Security Audit------当你解决安全或权限相关的问题是，这是一个很好的事件类别。这个类别中真正有用的事件是Audit: Login和Audit: Logout，这有助于追踪会话的连接设置和其他属性，如主机名或用户名。
• Transactions------不要捕捉Transaction Manager (TM:*)事件类，除非你解决长时间完成的与Begin/Commit/Rollback相关的问题。SQLTransation事件有助于解决死锁相关的问题，因为它有助于追踪一个特定会话的事务begin/commit/rollback和嵌套级别。事件DTCTransaction有助于解决DTC事务相关的问题，并追踪一个DTC事务不同的状态。
• Performance Event------这是应用最广的事件类别之一，因为这个事件类别能够抓获查询计划。有些不同的事件类用于抓取查询计划，基于获取的数据可分为两类：
  编译时间细节----Showplan All、Showplan XML和Showplan Text，Showplan XML For Query Compile和Showplan All For Query Compile。这些事件有助于捕捉超时相关问题的查询计划。
  运行时细节------Showplan Statistics Profile和Showplan XML Statistics Profile显示运行时细节的查询计划。
  

下面是一些经常使用的事件类：

• SP:CacheMiss, SP:CacheInsert, SP:CacheRemove和SP:CacheHit------这些事件有助于追踪程序缓存计划的缓存问题；
• TSQL: Prepare SQL, TSQL: Exec Prepared SQL, TSQL: Unprepare SQL------这些事件有助于解决准备好的SQL调用相关的问题。
  

使用SQLdiag警告驱动的数据采集(Alert-Driven Data Collection with SQLdiag)
SQL Server代理可以监视并自动响应高CPU使用率相关的事件，对警报的响应可以是一个Job，该Job通过命令行参数启动SQLdiag数据采集。当数据采集开始时，你可以进一步发送e-mails。