SQL SERVER System Information Collection and Analysis

1.      Windows Event Log
a.      System log – sql server service start and close, operating system events, hardware changes etc.
b.      Application log
c.      Security log – for authentication errors
2.      SQL Server Errorlog
a.      Where? –one way is to check the –e in startup parameters in configuration manager
b.      7 files by default
c.      What are not reported in errorlog?
                                                    i.     Blocking
                                                   ii.     Performance problem due to poor table and statement design
                                                  iii.     Errors at the OS level
3.      Performance Monitor
a.      Perfmon.exe
b.      Live counters
c.      Limited time window at one time (1m4 40s by default), but you can set start and end time
d.      Typical use case: start it just before the problems, then stop.
e.      The default 15s interval is generally fine
4.      SQL Trace
a.      Many event classes
                                                    i.     RPC:Starting and RPC:Completed – an application such as ADO runs a sp, we will see an RPC event.
                                                   ii.     SP:Starting and SP:Completed – if use a sp in SQL batch
                                                  iii.     SP:StmtStarting and SP:StmtCompleted – track the statements inside a complex sp, used when the sp has many commands inside.
                                                  iv.     Choose the option - SQL Processing trace data (if not, Profiler – client side trace will be used). A better way is to use SQL trace scripts, which can be generated from an exported trace.
b.      Analyze Trace Results
                                                    i.     Read/Write – number of times, not page or KB; Logical, not physical
                                                   ii.     fn_trace_gettable(FILENAME,number_files) -- Filename cannot be Chinese
1.      can import multiple files into a table
2.      easy to query, even with index
USE [tempdb] --一定要指定tempdb数据库
IF OBJECT_ID('#temp_trc') IS NOT  NULL
    DROP TABLE #temp_trc

INTO    #temp_trc
FROM    fn_trace_gettable('D:\1.trc', 1)  --trace文件名字不能是中文!!!!!!!!!
WHERE   [EventClass] IN ( 10, 12 )

FROM    [#temp_trc]
                                                  iii.     ReadTrace (now Replay Markup Language (RML) Utilities for SQL Server) at http://support.microsoft.com/kb/944837

--自动化分析SQL Trace


 --安装完毕后,在C:\Program Files\Microsoft Corporation\RMLUtils

 --注意参数是-f  不能是/f

 >"C:\Program Files\Microsoft Corporation\RMLUtils\ReadTrace.exe" -ID:\TraceAnalysis\1.trc  -oD:\TraceAnalysis -f


 --生成的报表,可以选择RML Utilities for SQLSERVER ->Reporter来访问

 --注意,要确保当前你的数据库有PerfAnalysis 数据库!!!!!!!!!!!
5.      DMVs
a.      Why need them?
                                                    i.     Provide many that SQL trace cannot capture
                                                   ii.     Low overhead
                                                  iii.     If we have no ideas about the problem, we can use DMVs first, then use SQL Trace after problem becomes clearer.

b.      Which ones to use?
                                                    i.     To capture live data
 SELECT * FROM sys.[sysprocesses]
 SELECT * FROM sys.[dm_exec_sessions]
 SELECT * FROM sys.[dm_exec_requests]
 SELECT * FROM sys.[dm_os_tasks]
 SELECT * FROM sys.[dm_exec_connections]

 --SQL 各种等待状态出现的次数的统计
 DBCC SQLPERF(waitstats)

 --每个调度进程里的队列以及他们的状态信息Scheduler ID
 DBCC SQLPERF(umsstats)--User-Mode Scheduling: internal thread scheduler in SQL
 SELECT  type ,
         SUM(virtual_memory_reserved_kb) AS [vm reserved] ,
         SUM(virtual_memory_committed_kb) AS [vm commited] ,
         SUM(awe_allocated_kb) AS [awe allocated] ,
         SUM(shared_memory_reserved_kb) AS [sm reserved] ,
         SUM(shared_memory_committed_kb) AS [sm committed] ,
         SUM(single_pages_kb) AS [singlepage allocator],
         SUM(multi_pages_kb) AS [multi page allocated]
 FROM    sys.dm_os_memory_clerks
 GROUP BY type
 ORDER BY type

--An example of 跟踪系统性能脚本:
--Save the script to c:\users\xxx\desktop\queryprocess.sql

 USE master

 DECLARE @delayStr VARCHAR(20)

 SET @loops = 1
 SET @delayStr = '00:0:10'  --每秒执行一次脚本


 SET @i = 0
 WHILE @i < @loops
         WAITFOR DELAY @delayStr
         PRINT 'END TIME'
         PRINT ''
         SELECT  *
         FROM    [sys].[sysprocesses]
         PRINT ''
         DBCC SQLPERF(waitstats)
         PRINT ''
         DBCC SQLPERF(umsstats)
         PRINT ''
         --     ...
         SELECT  @i = @i + 1
Run the following osql or sqlcmd:
osql /E /S /i c:\users\charlie\desktop\queryprocess.sql  /o c:\users\charlie\desktop\queryprocess.out /w 3000

                                                   ii.     Download more sample scripts from the SQL Nexus

What is SQL Nexus?
SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data. Visit Getting Started page.

SQLdiag is a successor of PSSdiag. SQL Nexus is a tool used to load and analyze performance data collected by SQLdiag

What is SQLDiag?
In SQL Server 2005, the SQLdiag utility is a general purpose diagnostics collection utility that can be run as a console application or as a service. For more information about sqldiag, please refer to SQL Server books online documentation:http://msdn2.microsoft.com/en-us/library/ms162833.aspx.

Thought default templates are available, shipped products don't have a configuration user interface that would allow you to customize what profiler trace events or perfmon counters to collect. If you want to customize data collection, please use 
pssdiag/sqldiag manager
 from codeplex.

What is PSSDIAG?
PSSDIAG is a diagnostics utility used to collect profiler trace, perfmon data for SQL Server. PSSDAIG was created to troubleshoot SQL Server 7.0 and 2000 issues. It was evolved and shipped with SQL Server 2005 and beyond. But the shipped product only has a data collector engine called sqldiag.exe. Microsoft Product support continues to maintain a configuration tool that allows engineers to dynamically configure on what to collect. Due to customers' demand, this configuration tool has released under codeplex. please go toPssdiag/SqlDiag Manager project

If you troubleshoot SQL Server 2000 performance problems, you can continue to use an older version of pssdiag (

Steps 1-2-3

1.     Install SQL Nexus and RML Utilities
2.      Collect perf data from your SQL Server 2005/2008/2008R2/2012 instance using SQLDiag
3.      Import and analyze the data using SQL Nexus

What is RML?
The Microsoft SQL Server support team uses several internally written utilities to ease the work that is related to a typical customer support case. This article describes one utility suite that is called the Replay Markup Language (RML) Utilities for Microsoft SQL Server. Database developers and system administrators can use the RML Utilities for SQL Server to work with SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 CTP2.

                                                  iii.     Some useful DMVs for cumulative statistics
 SELECT * FROM sys.[dm_exec_query_stats]

 SELECT * FROM sys.[dm_db_index_operational_stats](DB_ID('AdventureWorks'),NULL,NULL,NULL)
 SELECT * FROM sys.[dm_db_index_usage_stats] WHERE [database_id]=DB_ID('AdventureWorks')

 SELECT * FROM sys.[dm_io_virtual_file_stats](DB_ID('AdventureWorks'),NULL)

 SELECT * FROM sys.[dm_db_file_space_usage] WHERE [database_id]=DB_ID('AdventureWorks')
 SELECT * FROM sys.[dm_db_session_space_usage] WHERE [database_id]=DB_ID('AdventureWorks')
 SELECT * FROM sys.[dm_db_task_space_usage] WHERE [database_id]=DB_ID('AdventureWorks')

 SELECT * FROM sys.[dm_os_buffer_descriptors] WHERE [database_id]=DB_ID('AdventureWorks')

                                                  iv.     More scripts from others
                                                   v.     Scripts from the author

6.      SQLDiag – Automatic Data Collection

 ()SQLSERVER ErrorLog,以及SQL配置信息,一些重要运行信息


 C:\Program Files\Microsoft SQL Server\90\Tools\Binn

 当出现SQLDIAG Collection started.  Press Ctrl+C to stop. 信息以后就
 Crtl+C,终止这个工具的执行。SQLDIAG 工具会在Binn目录下产生三个

 (1)SQL errorlog文件内容,以及SQL配置信息和一些重要运行信息(XXXX_sp_sqldiag_Shutdown.out
 (4)SQL默认开启的Default Trace文件(log_XXX.trc

 但是使用另外两个XML配置文件SD_General.xml SD_Detailed.xml,就能够收集到
 SQL Trace和性能日志。但是这样,产生的文件就会比较大,放在SQL安装目录下

 SQLdiag.exe /I <configure_file> /O <output_directory>
 SQLdiag.exe /I SD_General.XML /O D:\SQLDIAOUT

  运行这句话之后,就能看到SQLdiag开启PerfmonAddingPerfmon counters
 TraceStarting Profiler Trace)。等SQLdiag成功开启以后,DBA

 ()SQL Trace文件(XXXX_sp_trace.trc
 ()Windows性能日志(SQLdiag.blg)用性能监视器也打不开这个文件,要使用系统自带的小工具relog /? cmd下运行的

要将名为SQLDIAG.BLG sqldiag性能日志文件转换为名为sqldiag.csv.csv 日志文件,使用命令:

 relog D:\SQLDIAOUT\SQLDIAG.BLG -f CSV -o D:\sqldiag.csv

 两个XML配置文件SD_General.xml SD_Detailed.xml的主要区别是SD_Detailed.xml
 会在SQL Trace里收集更多的事件,所以输出会要大很多。DBA可以根据需要选择其中一个

 SQLdiag(使用SD_General.xml SD_Detailed.xml做配置文件),再加上

PerfStatsScript2005 - Mainly Current Data
PerfStatsScript2008R2 (the same as 2008)

7.      Performance Dashboard – for Customer Reports in SQL Server
a.      SQL Server 2005
b.      SQL Server 2008 and R2
c.      SQL Server 2012