SQL Server 2008性能监视

客官°小女子只卖身不卖艺 2022-05-27 02:37 274阅读 0赞

\--------http://www.cnblogs.com/xupengnannan20070617/archive/2012/09/01/2665851.html

**性能监视**

SQL Server 2008监视本质上可以分为5个区域：系统资源；SQL Server自身；数据库；数据库应用程序和网络。

监视和优化SQL 的策略是相当简单的，分为以下几步：创建一个性能基准；完成定期性能审核；作出改动并评估他们的影响；重设基准

那么基准中应该都包含些什么呢？下面是一个基准和性能审核对象和进程的列表：

计数器：计数器包括Processor:%Processor Timer计数器，它显示了处理非限制线程所用时间的总百分比；Process：%Processor Time计数器用来确定总处理时间中有多少由SQL Server占用；System：Processor Queue Length计数器显示等待由CPU处理的线程的数量。

磁盘计数器：磁盘计数器可以返回每个物理磁盘或所有磁盘的磁盘读写性能信息和数据传输信息。PhysicalDis：Avg.Disk Queue Length计数器返回等待访问一个独立磁盘或所有磁盘的读写操作的平均数量；PhysicalDisk：%Disk Time计数器测量一个物理磁盘或硬件磁盘阵列的繁忙程度。

内存计数器：Memory：Pages/Sec计数器测量每秒从内存中分页到磁盘或从磁盘分页到内存的页数；Memory：Available Bytes计数器指示有多少内存可供进程使用；Process：Working Set（sqlservr）计数器显示了SQL Server使用了多少内存；SQL Server：Buffer Manager：Buffer Cache Hit Ratio计数器测量在缓冲区中找到数据，而不用再去读磁盘的时间百分比；SQL Server：Buffer Manager：Page Life Expectancy计数器返回一个单位为秒的时间值，显示数据页在缓冲区中停留而不会被数据操作引用的时间。

网络计数器：Network Interface：Bytes Total/Sec计数器测量在服务器和网络之间来回发送的字节总数

SQL Server计数器：SQL Server：General Statistics：User Connections计数器显示了当前连接到SQL Server的用户连接数；SQL Server：Locks：Average Wait Time计数器是一个很好的计数器，用来监视和跟踪由于并发数据阻塞造成的用户对于数据资源的请求需要等待的平均时间；SQL Server：Locks：Deadlock/Sec计数器是描述关于死锁的相关指标；SQL Server Access Methods：Page Splits/Sec计数器通过对页拆分活动进行监视来确定表索引的所片化速度。

动态管理视图：SQL Server 2008提供了很多动态管理视图，可以用来搜集基准信息和诊断性能问题。如sys.dm\_os\_performance\_counters, sys.dm\_index\_physical\_stats, sys.dm\_db\_index\_usage\_Stats等

**用于监视的工具和技术**

日志文件查看器：可在SQL Server Management Studio中展开管理文件夹，然后展开SQL Server日志文件夹，右击想查看的日志，然后选择查看SQL Server日志命令。每次重启SQL Server和SQL Server代理时，会关闭各自的日志文件并打开一个新的日志，在一个生产系统中，这种情况可能不会经常发生，于是就会形成一个较大的日志文件。想要避免出现过于庞大的日志文件，必须到处日志文件的内容，然后再循环使用该文件。要想循环使用SQL Server日志，可以执行sp\_cycle\_errorlog存储过程。要想循环使用代理日志，可以使用sp\_cycle\_agent\_errorlog存储过程。这些过程可以清除日志的内容而不用重启服务。

活动监视器：活动监视器是一个可以帮助对服务器的整体运行状况和性能有更深入理解的优秀工具。它显示直观的图形、详细的进程和锁定信息、文件IO统计信息和长时间运行的查询的信息。另外，可以对所有的网格视图进行配虚和筛选。要运行活动监视器，需要具有view server state权限，要终止任何进程，还需要是sysadmin或processadmin服务器角色的成员。活动监视器主要有5个部分构成：概述、进程、资源等待、数据文件IO和最近耗费大量资源的查询。

系统存储过程：虽然就查看进程以及他们使用的资源而言，活动监视器是一个很好的图形工具，但通常来说，系统存储过程的输出更简单，更适合用来识别当前进程以及任意资源竞争。sp\_who2存储过程是一个未归档的系统过程，和已归档的同类存储过程sp\_who相比，它有显著的优势。他们都返回当前SQL Server进程的信息，但是前者返回的信息更加全面。sp\_lock存储过程返回活动数据库进程所持有的锁的数目和类型。还有KILL命令，它虽然不是一个存储过程，但是它使得数据库管理员可以终止一个违反规则的进程，其语法很简单：KILL spid。KILL命令很有用，但是要注意，虽然有时候有必须要终止一个终端的进程，但是在终止它之前搜集与它有关的尽可能多的信息还是很重要的。

现在打开一个查询窗口，执行如下代码：

USE AdventureWorks2008;
    GO
    BEGIN TRAN
    UPDATE Person.Person SET LastName='Gates' WHERE BusinessEntityID=1

打开另外一个查询窗口，输入并执行下列代码

USE AdventureWorks2008;
    GO
    SELECT * FROM Person.Person WHERE BusinessEntityID=1;

在执行此语句时不会看到任何返回的结果，因为在第一个查询释放其锁定之前，此语句不会完成执行，现在打开第三个窗口，执行下列命令运行sp\_who系统存储过程

EXEC sp_who

执行sp\_who2存储过程，但将结果集限制为阻塞绘画的服务进程ID，这里是60

EXEC sp_who2 60；

确定哪个对象在被两个进程竞争，执行sp\_lock存储过程，输入并执行下列命令显示有关被阻塞的SPID的信息

EXEC sp_lock 59；

要终止阻塞进程，需要执行KILL命令并制定合适的SPID，这里是60：KILL 60.

使用Profiler：Profiler指示一个查看SQL跟踪的图形化界面，它是一个很好的工具，但对于有着繁重事务负荷的大型数据库来说，可能需要使用sp\_trace\_setevent，sp\_trace\_setfilter，sp\_trace\_setstatus和sp\_trace\_create存储过程，通过文件中搜集的跟踪数据创建、配置和运行跟踪。然后可以使用Profiler直接通过搜集的文件查看这些数据或者可以将它们导入到一个数据库中以供分析。但是使用Profiler时一个需要考虑到事项时开销，交互式运行Profiler会导致大量服务器开销，以及教的的不确定因素。

打开SQL Server Management Studio，连接到驻留AdventureWorks2008数据库的服务器，在连接后通过工具菜单启动SQL Server Profiler，创建一个基于空白模板的新跟踪，在事件选择选项卡上，选择Deadlock graph和Lock：Deadlock Chain事件，如果选择了Deadlock graph事件，则会出现事件提取设置选项卡；要限制返回给Profiler的数据，单击列筛选按钮，然后选择DatabaseName，在不类似于文本框中，输入MSDB以避免跟踪SQL代理和计划的监视活动，单击确定按钮。在事件提取设置选项卡上，选中分别保存死锁XML事件复选框，然后输入一个文质来保存文件，选中不同文件中的每个死锁XML批单选按钮，然后单击运行按钮。在SQL Server Management Studio中，打开两个新的查询窗口，第一个查询窗口中输入如下代码并执行：

--Connection 1
    USE AdventureWorks2008;
    GO
    BEGIN TRAN
    UPDATE Person.Address SET City='Redmond' WHERE AddressID=1;

在第二个查询窗口中输入如下代码并运行：

--Connection 2
    USE AdventureWorks2008;
    GO
    BEGIN TRAN
    UPDATE Person.Person SET LastName='Gates' WHERE BusinessEntityID=1;
    UPDATE Person.Address SET AddressLine1='1 Microsoft Way' WHERE AddressID=1;

此更新将不会完成，因为Connection1中的事务在想要更新的Person.Person表的行上有一个排他锁。在第一个连接上，编写并执行如下代码以更新Person.Person表

UPDATE Person.Person SET FirstName='Bill' WHERE BusinessEntityID=1

此更新会导致一个死锁发生，因为两个连接都建立在对立事务完成所需的资源上都持有排他锁。回到Profiler，停止跟踪，然后选择Deadlock graph事件类行。死锁图形显示了被死锁的服务器进程ID和已锁定的资源，将鼠标指针悬停在一个进程上面会显示参与死锁的进程。要捕捉用来运行这个跟踪的脚本，单击SQL Profiler中的文件菜单，然后选择到处-编写跟踪定义的脚本-用于SQL Server 2005-2008选项，这时候将会出现一个另存为对话框，存储为DeadlockTrace.SQL。

在捕捉到跟踪文件以后，可以使用Management Studio打开，而不用启动和运行Profiler。如果跟踪较大，可以把它插入表，以便使用传统的TSQL查询分析。要把数据移动至表中，可以使用fn\_trace\_gettable表值函数。此表值函数需要两个值，需要导入的跟踪文件的名称和需要搜集的滚动更新文件的最大值。默认的文件数目是为跟踪设置的最大文件数，下面的例子显示了如何将之前搜集的跟踪添加到AdventureWorks2008数据库的一个叫做DeadLockTraceTable的表里面

USE AdventureWorks2008
    GO
    SELECT * INTO DeadLockTraceTable FROM fn_trace_gettable（‘C:\ProfilerTraces\DeadLocks.trc’，NULL）;

使用Profile检测和分析长时间运行的查询：Profiler是一个很好的工具，可以用于分析所，以及调式存储过程和数据库应用程序。它也可以用于检测和分析影响SQL Server性能的长时间运行的查询。Profiler可以返回查询执行信息，数据库管理员可以通过这些信息找出导致冗长查询的原因。要分析查询，需要遵循下列步骤：启动Profiler，使用空白模板创建一个叫做QueryTuning的新跟踪，在时间选择选项卡上选择下列事件：Performance：ShowPlan XML，Stored Procedures：SP:Completed，TSQL:SQL:BatchCompleted，单击列筛选器按钮创建一个筛选器，其中数据库名称类似于AdventureWorks2008，然后单击确定按钮应用筛选器，单击组织列按钮，找到Duration列，将其移至列表的顶部，使得易于阅读持久数据，在事件提取设置选项卡上，选中分别保存XML显示计划事件复选框。选择一个保存ShowPlan信息的位置，将文件命名为QueryTuning，然后选择不同文件中的每个XML显示计划批选项。SQLPlan是给予ShowPlan数据的文件扩展，ShowPlan数据坤出位XML格式，并且可以使用Management Studio查看，当将查询计划保存在单独的文件中时，每个文件都采用在目标位置中定义的名称，并且名称后面附加有数字标识符。单击运行按钮运行此跟踪，接下来，在SQL Server Management Studio中打开一个iexin的查询窗口，输入并执行下列代码：

![复制代码][copycode.gif]

USE AdventureWorks 2008;
    GO
    SELECT P.ProductID,P.name AS Product, TH.TransactionDate, SUM(TH.Quantity),SUM(TH.ActualCost),SUM(P.StandardCost) FROM Production.Product P INNER JOIN Production.TransactionHistory TH ON P.ProductID=TH.ProductID GROUP BY P.ProductID, P.Name, TH.TransactionDate;
    GO
    EXEC dbo.uspGetManagerEmployees 109;
    GO
    EXEC dbo.uspGetEmployeeManagers 1;
    GO
    SELECT P.name AS Product, SUM(SOD.OrderQty) AS SumQty,SUM(SOD.UnitPrice) AS SumPrice, SUM(SOD.LineTotal) AS SumTotal, CONVERT(char(10),SOH.OrderDate,101) AS orderDate, CONVERT(char(10),SOH.ShipDate,101) AS ShipDate, CONVERT(char(10),SOH.DueDate,101) AS DueDate FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHeader SOH ON SOH.SalesOrderID=SOD.SalesOrderID INNER JOIN Production.Product P ON P.ProductID=SOD.ProductID GROUP BY P.Name, SOH.OrderDate, SOH.ShipDate, SOH.DueDate

![复制代码][copycode.gif]

完成查询以后，停止跟踪并检查结果，导航至ShowPlan目标文件夹并检查内容，应该看到4个文件，分别从QueryTuning\_1.SQLPlan到QueryTuning\_4.SQLPlan进行命名。双击QueryTuning\_4.SQLPlan文件，这将启动SQL Server Management Studio，并把该文件作为一个图形化执行计划打开。

监视文件：数据库管理员较为普通的任务之一就是监视和管理文件的大小。一般来说，在数据库的设计和规划阶段已经确定了数据库的大小，这一决定中应表示数据库文件的起始大小，以及每个文件类型的预计增长率。监视数据库文件大小的方法有很多种，SQL Server Management Studio中的磁盘使用情况的报表即是一种；sp\_spaceused存储过程也可以用来返回一些和磁盘使用报表类似的信息，但是如果没有给他传递具体对象名称参数，该存储过程将智慧返回整个数据库的信息空间。下列显示了如何运行sp\_spaceused存储过程从AdventureWorks2008数据库或其中的一个表中检索信息

![复制代码][copycode.gif]

SET NOCOUNT ON;
    SUE AdventureWorks2008；
    GO
    SELECT 'AdventureWorks2008 Space Used Data'
    EXEC sp_spaceused；
    
    SELECT 'Person.Person Space Used Data'
    EXEC sp_spaceused；

![复制代码][copycode.gif]

性能计数器可能是监视数据和日志文件中的空闲空间的最有效办法。SQL Server:Databases性能对象有几个可以用来监视磁盘和日志文件大小的计数器。可以使用这些计数器创建警报。这样SQL Server就能替您执行监视，并在数据文件超出预制的大小或事务日志的填充程度超出一定百分比时通知您。

**审核**

从根本上讲，审核就是监视和跟踪对系统的更改的过程。

SQL Server审核的创建包括以下过程：创建一个SQL Server审核包并定义输出目标；创建一个服务器审核规范和一个或多个数据库审核规范，他们定义了审核事件标准；启用审核规范；启用审核；查看那并分析不活动审核事件。

审核安全事件：要审核安全事件，需要遵守下列步骤，打开SQL Server Management Studio，连接至驻留AdventureWorks2008数据库的服务器。展开对象资源管理器中的安全性节点，右击审核文件夹并选择新建审核命令，创建一个新的审核；右击服务器审核规范文件夹，选择新建服务器审核规范命令，在审核下拉列表中，选择上面创建的审核并添加下列操作组：SERVER\_PRINCIPAL\_CHANGE\_GROUP;SERVER\_PRINCIPAL\_IMPERSONATION\_GROUP;LOGIN\_CHANGE\_PASSWORD\_GROUP，保存审核规范，然后右击它并选择启用服务器审核规范命令，审核规范上的图标将显示已被启用；展开AdventureWorks2008数据库，通过安全性-数据库审核规范文件夹创建一个新的数据库审核规范，将这个规范映射至上面创建的审核报，然后添加下列操作组：SELECT:对象类Schema对象名称Person主体名称public，DATABASE\_OBJECT\_PERMISSION\_CHANGE\_GROUP，保存并启用该审核规范，数据库审核规范的图标应改为显示它已被启用；现在启用审核以便开始接收包含的事件。在启用了审核后，将下列代码输入到一个新的窗口并执行：

![复制代码][copycode.gif]

--Create a new server login
    EXEC sp_addlogin 'Paul','Microsoft123','AdventureWorks2008';
    GO
    
    --Exclude this login from OS policy constraints
    ALTER LOGIN Paul WITH CHECK_POLICY=OFF
    GO
    
    --change password
    EXEC sp_password @old='Microsoft123',@new='Microsoft456',@loginname='Paul'
    GO
    
    --Allow this user to access AdventureWorks2008
    USE AdventureWorks2008
    GO
    CREATE USER Paul FOR LOGIN Paul
    GO
    
    --Try to select as Paul, no permissions yet
    EXECUTE AS LOGIN='Paul'
    SELECT * FROM Person.Person WHERE BusinessEntityID=1;
    REVERT
    GO
    
    --Assign permissions
    GRANT SELECT ON Person.Person  TO Paul;
    GO
    
    --now the select should succeed
    EXECUTE AS LOGIN='Paul'
    SELECT * FROM Person.Person WHERE BusinessEntityID=1;
    REVERT
    GO
    
    --Clean up
    DROP USER Paul
    GO
    EXEC sp_droplogin 'Paul';
    GO

![复制代码][copycode.gif]

最后，禁用该审核，然后右击审核并选择查看审核日志命令，在日志文件查看器中显示结果，在审核及锅中可以注意到，所有的目标事件都被捕获，包括失败的SELECT尝试，也可以使用新的fn\_get\_audit\_file函数以表形式查看审核结果

SELECT * FROM fn_get_audit_file('C:\SQLAudit、*'，default，default)

审核登录：登录审核仅记录成功的登录尝试、失败的的登录尝试或者两者都记录。对登录审核的支持内置于SQL Server中，可通过SQL Server Management Studio的服务器属性对话框的安全性启用页，在更改了登录审核级别后，只有重启服务器实例，新的设置才会生效。登录成功和失败事件都会被写入WIndows 应用程序日志以及SQL Server 的日志中。

C2审核模式：C2审核模式是由美国国防部制定的一组应用于指定计算机系统中的安全级别的等级。C2审核所设计的不仅仅是服务器事件，而是扩展至包含访问所有语句和对象的成功和失败的尝试。如果您想标识出可能存在的违反安全的行为，这是很有用的。但是它会耗费大量的空间和性能。要启用C2审核模式，右击SSMS的对象资源管理器中的服务器实例，选择属性命令，然后选择安全性页，在选择启用C2审核跟踪设置。要禁用C2审核跟踪，取消选择即可。设置后，必须重启服务器实例才能生效，也可以使用TSQL启用C2审核跟踪

![复制代码][copycode.gif]

--Enable C2 audit mode
    sp_configure ‘show advanced options’,1
    GO
    RECONFIGURE
    GO
    sp_configure 'c2 audit mode',1
    GO
    RECONFIGURE
    GO
    
    --Disable c2 audit mode
    sp_configure 'show advanced options',1
    GO
    RECONFIGURE
    GO
    sp_configure 'c2 audit mode',0
    GO
    RECONFIGURE
    GO

![复制代码][copycode.gif]

当然，也同样需要重启服务器实例才有效果，C2审核日志跟踪文件总是存储在服务器实例的数据目录中，可以使用SQL Server Profiler或sys.fn\_trace\_gettable系统函数阅读这些文件

安全审核事件类别：SQL Profiler可以用来监视各种安全审核事件，当选择要包含进跟踪的事件时，可以在Security Audit部分看到这些事件。安全审核事件使您可以有选的监视和完整的C2审核几乎同样的信息，但是在使用SQL Profiler时，可以挑选确实想监视的内容并旨在必要时进行监视。要使用SQL Server Profiler审核安全事件，可以遵循下列步骤：启动SQL Server Profiler，使用空白模板创建一个新的名为SecurityAudit的跟踪，在“事件选择”选项卡的Security Audit部分选择下列事件：Audit Add DB User Event，Audit Add Member to DB Role Event，Audit Login Change Password Event，Audit Server Principal Management Event；单击组织列按钮，找到EventSubClass，TargetLoginName，TargetUserName，RoleName和ObjectName列，将他们移动至列表顶部并排在EventClass之后，以便于阅读结果；单击运行按钮启动这一跟踪；在SQL Server Management Studio中打开一个新的查询窗口，输入下列代码

![复制代码][copycode.gif]

--Create a new server login
    EXEC sp_addlogin 'Paul','Microsoft123','AdventureWorks2008';
    GO
    
    --Exclude this login from OS policy constraints
    ALTER LOGIN Paul WITH CHECK_POLICY=OFF
    GO
    
    --Allow this user to access AdventureWorks2008
    USE AdventureWorks2008
    GO
    
    CREATE USER Paul FOR LOGIN Paul
    GO
    EXEC sp_addrolemember N'db_owner','Paul'
    GO
    
    --Clean up
    DROP USER Paul
    GO
    EXEC sp_droplogin 'Paul';
    GO

![复制代码][copycode.gif]

在查询完成后，停止跟踪并检查结果，图（略）

SQL跟踪：SQL跟踪提供了使用SQL Server Profiler捕获事件的另外一种方式。通过SQL跟踪，可以使用TSQL系统存储过程定义跟踪，这对于想开发自己的自定义审核解决方案的组织特别有用。创建SQL 跟踪的基本步骤如下所示：使用sp\_trace\_create存储过程创建一个跟踪；使用sp\_trace\_setevent存储过程添加想包含的事件。在添加事件时，必须为想包含进跟踪中的每个事件和列组合执行一次该存储过程；如果有需要，可以使用sp\_trace\_setfilter存储过程为捕获的事件定义一个筛选器。创建好跟踪后，可以使用sp\_trace\_setstatus存储过程启动，停止和关闭它。

下列代码创建和启动一个“安全审核事件类别”一节所包含的SQL Server Profiler示例等效的跟踪，这个跟踪将一直运行，直到使用sp\_trace\_setstatus存储过程停止它或者服务器实例重启

![复制代码][copycode.gif]

--Create a new trace
    Declare @id int
    exec sp_trace_create @id output,0,N'C:\ProfilerTraces\SecurityAudit'
    select @id 'traceid' --Display the trace id
    
    --Add some events
    Declare @On bit
    SET @On=1
    --Event 109=Audit Add DB User Event
    exec sp_trace_setevent @id,109,21,@On --EventSubClass
    exec sp_trace_setevent @id,109,42,@On --TargetLoginName
    exec sp_trace_setevent @id,109,39,@On --TargetUserName
    exec sp_trace_setevent @id,109,38,@On --RoleName
    exec sp_trace_setevent @id,109,34,@On --ObjectName
    --Event 104=Audit Add Login Event
    exec sp_trace_setevent @id,104,21,@On --EventSubClass
    exec sp_trace_setevent @id,104,42,@On --TargetLoginName
    exec sp_trace_setevent @id,104,39,@On --TargetUserName
    exec sp_trace_setevent @id,104,38,@On --RoleName
    exec sp_trace_setevent @id,104,34,@On --ObjectName
    --Event 110=Audit Add Number to DB Role Event
    exec sp_trace_setevent @id,110,21,@On --EventSubClass
    exec sp_trace_setevent @id,110,42,@On --TargetLoginName
    exec sp_trace_setevent @id,110,39,@On --TargetUserName
    exec sp_trace_setevent @id,110,38,@On --RoleName
    exec sp_trace_setevent @id,104,34,@On --ObjectName
    --Event 107=Audit Login  Change Password Event
    exec sp_trace_setevent @id,107,21,@On --EventSubClass
    exec sp_trace_setevent @id,107,42,@On --TargetLoginName
    exec sp_trace_setevent @id,107,39,@On --TargetUserName
    exec sp_trace_setevent @id,107,38,@On --RoleName
    exec sp_trace_setevent @id,107,34,@On --ObjectName
    
    --Start the trace
    exec sp_trace_setstatus @tracedid=@id,@status=1 --Starts the trace
    GO

![复制代码][copycode.gif]

下列代码将停止和关闭跟踪，要确保替换了启动跟踪时所返回的跟踪ID

![复制代码][copycode.gif]

--Stop and close the trace
    Declare @id int
    SET @id=3  --enter the value recorded above
    
    exec sp_trace_setstatus @traceid=@id,@status=0 --Stops the trace
    exec sp_trace_setstatus @traceid=@id,@status=2 --Closes the trace
    GO

![复制代码][copycode.gif]

**跟踪数据更改**

**　　**SQL Server 2008引入了两种不同的跟踪更改的方法，第一种是变更数据捕获，这是一个功能全面的，高度可定制的更改和数据跟踪解决方案；第二种是更改跟踪，它与第一种方法十分相似，但是没有跟踪数据的功能。

变更数据捕获Change Data Capture，CDC：在使用变更数据捕获之前，必须配置它，配置过程非常简单，使用sys.sp\_cdc\_enable\_db存储过程在数据库上启用变更数据捕获；使用sys.sp\_cdc\_enable\_table存储过程为数据库中的一个或多个表启用变更数据捕获。在数据库上启用变更数据捕获会创建所有必须的元数据表和支持的存储过程、函数和数据管理视图。要在数据库上启用CDC，必须是sysadmin固定服务器角色的成员。CDC进程将独占使用一个名为cdc的架构和一个名为cdc的数据库用户。如果数据库中已存在此名称的架构或用户，那么在重命名或删除冲突对象以前将无法启用CDC

Use AdventureWorks2008
    GO
    
    --Enable Change Data Capture
    EXEC sys.sp_cdc_enable_db
    GO

在对该数据库启用了CDC后，可以使用sys.sp\_cdc\_enable\_table存储过程为特定表启用CDC

User AdventureWorks2008
    GO
    
    --Enable Person.Person table for Change Data Capture
    EXEC sys.sp_cdc_enable_table 'Person','Person',@role_name=NULL,@supports_net_changes=1
    GO

如果启用了变更数据捕获，CDC会创建一个相应的更改表，该表用一些额外的元数据列镜像源表架构。另外还会创建两个作业，一个是用于处理SQL事务日志的捕获作业，另一个是用于从更改表中清除超过指定保留时间的行。需要确保运行了SQL Server代理服务来处理这些作业。如果想获得启用了CDC的数据库或表，可使用sys.databases中的iscdc\_enabled列或sys.tables中的istracked\_by\_cdc列：

![复制代码][copycode.gif]

--Display the name of every database enabled for cdc
    SELECT name FROM sys.databases WHERE is_cdc_enabled=1
    
    --Display the name of every table enabled for cdc in AdventureWorks2008
    Use AdventureWorks2008
    GO
    SELECT name FROM sys.tables WHERE is_tracked_by_cdc=1
    GO

![复制代码][copycode.gif]

可以使用sys.sp\_cdc\_disable\_table存储过程禁用表的CDC，也可以使用sys.sp\_cdc\_disable\_db存储过程对整个数据库禁用CDC

![复制代码][copycode.gif]

USE AdventureWorks2008
    GO
    EXEC sys.sp_cdc_disable_table 'Person','Person','All' 
    GO
    
    Use AdventureWorks2008
    GO
    EXEC sys.sp_cdc_disable_db
    GO

![复制代码][copycode.gif]

在配置了变更数据捕获之后，您可能想在某个时间查询更改表以了解是否发挥僧了变更，如果，具体又是什么，对于每个更改表，有两个表值函数可用于此目的：cdc.fn\_get\_all\_changes\_<schema\_table>，为制定的更改表返回一个完整的变更列表，cdc.fn\_get\_net\_changes\_<schema\_table>，返回一个变更的合并列表，其中一个输出行代表变更的每行的最终版本。下面的示例将显示为Person.Person表捕获的所有更改，以及表明更改是否设计LastName列

![复制代码][copycode.gif]

USE AdventureWorks2008
    GO
    Declare @from_lsn binary(10)
    Declare @to_lsn binary(10)
    Declare @lastname_ordinal int
    
    --Get the availabe log sequence range
    SET @from_lsn=sys.fn_cdc_get_min_lsn('Person_Person')
    SET @to_lsn=sys.fn_cdc_get_max_lsn()
    
    --Get the ordinal value for the LastName column
    SET @lastname_ordinal=sys.fn_cdc_get_column_ordinal('Person_Person','LastName')
    
    --Return the list of changes and whether or not the change affected in the LastName column
    SELECT sys.fn_cdc_is_bit_set(@lastname_ordinal,_$update_mask) 'LastNameChanged',* FROM cdc.fn_cdc_get_all_changes_Person_Person(@from_lsn,@to_lsn,'all')
    GO

![复制代码][copycode.gif]

如前所述,变更数据捕获使用捕获作业和清除作业使更改表保持最新,并且是一个可管理的大小,当为数据库的第一个比表启用变更数据捕获时,会自动创建这些作业,对于这些作业的默认配置不能进行任何控制;不过,可以在他们创建之后使用sys.sp\_cdc\_change\_job存储过程进行修改。可以使用sys.sp\_cdc\_help\_jobs查看当前作业配置，对作业配置的更改将在停止并重启作业后生效。通过sys.sp\_cdc\_stop\_job和sys.sp\_cdc\_start\_job存储过程可以方便地完成这项工作，下面的示例显示了如果修改清除作业，使其变更数据保留五天

![复制代码][copycode.gif]

USE AdventureWorks2008
    GO
    
    --Change the Cleanup Job retention to 6000 minutes
    EXEC sys.sp_cdc_change_job @job_type='cleanup',@retention=6000
    GO
    
    EXEC sys.sp_cdc_stop_job 'cleanup'
    GO
    EXEC sys.sp_cdc_start_job 'cleanup'
    GO
    
    EXEC sys.sp_cdc_help_jobls
    GO

![复制代码][copycode.gif]

还有一些动态管理视图可以帮助管理员监视变更数据捕获进程的状态,sys.dm\_cdc\_log\_scan\_sessions四暗示子服务器最近一次启动以来已运行的每个日志扫描会话的详细信息;sys.dm\_repl\_traninfo显示有关每个复制的事务的信息,如果一起启用了CDC和复制，这将只与变更数据捕获有关；sys.dm\_cdc\_erros这个视图返回有关在最后32个会话中发生的任何错误的信息。

更改跟踪：更改跟踪可确定是否对表中的行做了改动，但不会提供有关已更改的实际数据的详细信息。下面的示例显示了如何在AdventureWOrks2008数据库中启用更改跟踪

![复制代码][copycode.gif]

ALTER DATABASE AdventureWorks2008
    SET CHANGE_TRACKING=ON
    (CHANGE_RETENTION=2 DAYS,AUTO_CLEANUP=ON)
    GO
    
    USE AdventureWorks2008
    GO
    ALTER TABLE Person.Person ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED=ON)
    GO

![复制代码][copycode.gif]

现在假定有一个外部应用程序需要与Person.Person同步，其中设计的最困难的任务是如何确定自上次更新后发生了哪些改变，此时更改跟踪就有了用武之地。更改跟踪使用一个内部的，数据库范围的版本控制系统将所有更改排序。为了获得最近一次同步以来的净变更，需要知道从哪个版本开始。如果这是初始加载，那么将记录当前版本号，并完整复制源数据以建立初始基准。为了获得这个模拟同步的起点，需要遵循以下步骤，在一个查询窗口中输入并执行下面的代码

SELECT CHANGE_TRACKING_CURRENT_VERSION() 'current_version'

写下结果，因为后面会用到它。此时，假定外部应用程序是断开的，当外部应用程序重新连接时，在这一时刻之后发生的变更需要同步，在一个查询窗口中输入并执行下列代码，对Person.Person表做一些更改

![复制代码][copycode.gif]

USE AdventureWorks2008
    GO
    
    --Change some data in the Person table
    UPDATE Person.Person
    SET FirstName='Paul',LastName='Allen' WHERE BusinessEntityID=1
    GO
    
    --Change the ame record again
    UPDATE Person.Person
    SET FirstName='Bill',LastName='Gates' WHERE BusinessEntityID=1
    GO
    
    --Insert a new row
    INSERT Person.Person (BusinessEntityID,PersonType,FirstName,LastName) VALUES(310,'SC','Joan','Ballmer')
    GO
    
    --Update the new row
    UPDATE Person.Person
    SET FirstName='Steve' WHERE BusinessEntityID=310
    GO

![复制代码][copycode.gif]

接着，外部应用程序重新连接并试图同步，为此，它需要知道自他最近一次同步以来发生了哪些变。更改跟踪使用之前记录的初始版本号提供这一信息，将下列代码输入一个命令窗口并执行，确保替换之前记录的版本号：

![复制代码][copycode.gif]

USE AdventureWorks2008
    GO
    
    Declare @sync_version bigint
    SET @sync_version=[INSERT YOUR VALUE HERE!]
    
    SELECT CHANGE_TRACKING_CURRENT_VERSION() 'current_version';
    
    --Identify the changes made after an earlier version
    SELECT CT.BusinessEntityID, p.FirstName, p.LastName, CT.SYS_CHANGE_OPERATION,CT.SYS_CHANGE_COLUMNS,CT.SYS_CHANGE_CONTEXT FORM CHANGETABLE(CHANGES Person.Person,@sync_version) CT left join Person.Person p on CT.BusinessEntityID=p.BusinessEntityID
    GO

![复制代码][copycode.gif]

**　　**有可能外部应用程序断开提啊就，变更记录变得不再可用，通过使用CHANGE\_TRACKING\_MIN\_VALID\_VERSION函数确保保存的基准版本大于最低版本，可以很容易地确定这一点。如果保存的基准版本不大于最低版本，那么外部应用程序需要获取同步对象的完整副本并建立新的基准

**数据收集**

**　　**数据收集可以自动地将不同的可定制的管理数据收集到一个数据仓库中，并在这里对数据进行分析和制作报表。这种功能与本文其他介绍的监视同能不同，因为数据收集是定期反复捕捉数据快照，从而允许监视数据随时间推移如何改变并查看趋势。数据收集器不仅可以被用来使用SQL跟踪和性能监视，还可以为跨多个服务器管理数据提供了一个中央收集点；允许控制捕获哪些管理度量标注；允许使用SQL Server Reporting Services生成报表；还可以通过一个功能丰富的API扩展。

数据收集器是将所有事务联系在一起的中心对象。它以来数据收集组来定义数据提供程序、计划和所收集数据的保持期。SQL Server代理使用收集组中定义的计划运行数据收集器，后者执行SSIS包从数据提供程序中收集所请求的数据，SQL Server代理通过使用收集组中的单独计划，再次运行数据收集器，这次后者运行SSIS包处理数据并将其上载到管理数据仓库。收据收集器还维护审核和历史记录信息，这样管理员可轻松确定并解决问题。

数据收集器使用msdb存储其大部分控制数据，包括配置，运行时，审核和收集历史记录信息，如果服务器上没有msdb数据库，将不能使用数据收集。想使用数据收集器，用户必须是已定义的数据收集器安全角色的成员，角色只有三个，分别是dc\_admin;dc\_operator,dc\_proxy。

如果还没有数据仓库数据库，那么需要创建一个，在创建了该数据库后，就可以配置数据收集了。这两个人物都可通过SSMS中的配置管理数据仓库向导完成，该向导位于服务器实例的管理节点的数据收集节点的上下文菜单中，你inkeneng需要运行该向导两次，一次用于创建一个新的存储数据库，另一次用于配置数据收集。在该向导的第一个页面上可以选择执行哪个人物。在创建一个新的数据仓库时，必须制定存储数据库的位置，如果没有存储数据库，可以单击新建按钮从同一对话框中创建一个。在选择了存储数据库后，下一步是指明应将什么访问级别指派给特定用户或组，数据收集使用3个预定义的安全角色：mdw\_admin，mdw\_writer，mdw\_reader。在创建一个管理数据仓库后，需要运行向导配置数据收集，对于这个人物，需要设置两个选项：用于存储的管理数据仓库的名称；用于缓存文件的目录的名称。在配置数据收集向导完成后，SQL Server 2008将启用数据收集并启动系统数据收集组。数据收集节点旁边的图标将会改变，而您将可以启用或禁用数据收集并管理单独的数据收集组。

数据收集器类型定义了可用于为数据收集组收集数据的数据提供程序。目前MS提供了四种不同收集类型：TSQL查询收集器类型，SQL跟踪收集器类型，性能计数器收集器类型和查询活动收集器类型。数据收集组收集来自一个或多个收集想的数据，并将数据上载到数据仓库。每个收集项基于使用XML格式的输入参数配置的数据收集器类型。收集组还定义了数据收集和上载的计划，运行作业的帐户以及上载数据的保持其。SQL Server 2008本身安装了3个系统数据收集组，磁盘使用情况，服务器活动，和查询统计信息

每个系统数据收集组都有一个相关联的报表，可以通过右击“数据收集”节点并选择报表-管理数据仓库命令在SSRS中进行查看，可用的报表分别是服务器活动历时记录，磁盘使用情况摘要和查询统计信息历史记录。可以使用sp\_syscollector\_create\_collection\_set系统存储过程创建自定义的数据收集组。目前，必须使用T-SQL创建新的数据收集组，因为还没有GUI可用。

收据收集器的主要用途之一就是收集和监视性能数据；不过，默认的收集组可能包含太多的信息。下面的示例演示如何创建一个新的数据收集组，它包含一些用于检测CPU瓶颈的CPU特定的性能计数器。首先，新黄建一个新的自定义数据收集组，在新的查询窗口中输入并执行下列代码

![复制代码][copycode.gif]

--Step1
    USE msdb
    GO
    
    DECLARE @collection_set_id int
    DECLARE @collection_set_uid uniqueidentifier
    SET @collection_set_uid-'9170CBA3-2C8D-402f-82F5-CD427F75D221'
    
    exec dbo.sp_syscollector_create_collection_set
    @name='CPU Bottlenecks',
    @target='', --use this for another server '/Server[@Name="MSSQLSERVER"]'
    @collection_mode=0,
    @days_until_expiration=5,
    @description='Collects selected PerfMon CPU counters',
    @logging_level=0, --0-2 are valid, in increasing verbosity
    @schedule_name=N'CollectorSchedule_Every_5min',
    @collection_set_id=@collection_set_id OUTPUT,
    @collection_set_uid=@collection_set_uid OUTPUT
    
    SELECT @collection_set_id 'collection set id',
    @collection_set_uid 'collection set uid'
    GO

![复制代码][copycode.gif]

记下在collection set id列中返回的值。后面需要用到它。使用性能计数器数据收集器类型添加一个新的收集项，这个收集项引用3个用于标识CPU瓶颈的CPU计数器。在一个新的查询窗口中输入并执行下列代码，确保替换上面记录的收集组ID值

![复制代码][copycode.gif]

--STEP 2
    USE msdb
    GO
    
    DECLARE @collector_type_uid uniqueidentifier,@collection_item_id int,@collection_set_id int
    SET @collection_set_id=[YOUR VALUE] --use the value you recorded above
    
    --Get collector type uid
    SELECT @collector_type_uid=collector_type_uid FROM [dbo].[syscollector_collector_types] WHERE name=N'Performance Counters Collector Type';
    
    --Add a new collection item
    EXEC [dbo].[sp_syscollector_create_collection_item]
    @name='Perfmon CPU counters',
    @parameters='
    <ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">
    <PerformanceCounters Objects="System" Counters="Processor Queue Length" Instances="*" />
    <PerformanceCounters Objects="Processor" Counters="% Processor Time" Instances="_Total"/>
    <PerformanceCounters Objects="Process" Counters="% Processor Time" Instances="sqlservr">
    </ns:PerformanceCountersCollector>',
    @frequency=5,--every 5 seconds
    @collection_set_id=@collection_set_id,
    @collector_type_uid=@collector_type_uid,
    @collection_item_id=@collection_item_id OUTPUT
    
    select @collection_item_id 'collection item id'
    GO

![复制代码][copycode.gif]

现在就创建了这个新的收集组。实际上，它已经显示在SQL Server Management Studio中，使用SSMS，展开服务器的管理-数据收集节点，您将看到一个名为CPU Bottlenecks的新项。需要启动这个新的数据收集组，使其开始在数据仓库中存储性能监视器计数器。为此，右击CPU Bottlenecks数据收集组，选择启动数据收集组命令。现在可以稍事休息，这个数据收集组将每五秒捕获一次数据，每五分钟上载一次数据。所以在查询数据仓库之前，需要至少五分钟来确保一些数据上载完成，开始查询时，可以在一个新的查询窗口中输入并执行下列代码

![复制代码][copycode.gif]

--STEP 3
    --Report the captured data
    USE MgmtDataWarehouse
    GO
    
    DECLARE @collection_set_uid uniqueidentifier,@endtime datetime
    SET @collection_set_uid='9170CBA3-2C8D-402f-82F5-CD427F75D221'
    
    SELECT pci.[object_name],pci.counter_name,pci.instance_name,pc.* FROM snapshots.performance_counter_values AS pc INNER JOIN core.snapshots s ON s.snapshot_id=pc.snapshot_id left join snapshots.performance_counter_instances pci on pc.performance_counter_instance_id=pci.performance_counter_id WHERE s.collection_set_uid=@collection_set_uid ORDER BY pc.performance_counter_instance_id,pc.collection_time 
    GO

![复制代码][copycode.gif]

另外，可以从SSMS的数据收集节点的上下文菜单或任意数据收集组的上下文菜单中选择查看日志命令来查看这些数据收集日志。

在SSMS中右击数据收集节点，然后通过报表-管理数据仓库命令选择一个报表可以运行他们。这三个报表分别是服务器活动历时记录、磁盘使用情况摘要和查询统计信息历史记录。

**监视数据库修改**

SQL Server 2008提供了一个新的功能，就是使用数据定义语言触发器和事件通知来监视甚至防止数据库被修改。DDL触发器可以在数据库和服务器作用域中进行定义，和传统的数据修改语言触发器一样，显示事件被激发，然后在其上定义的DDL触发器被激发。如果一个触发器被定义为防止数据库删除，则该数据库会首先被删除，然后当这个带有ROLLBACK语句的触发器被激发时，在还原这个数据库。和传统触发器不同，DDL触发器定义在一个特定的语句或语句组上，而不管该语句指向的对象是什么，也不会被指派给一个特定的对象。因此，不管删除哪个数据库，DROP\_DATABASE触发器都会激发。

在传统DML触发器中，触发器的很多功能是通过访问内存中的Deleted和Inserted表得到的，这些表值在触发器存在的时间里存在。DDL触发器不使用Inserted和Deleted表。相反，如果需要捕获来自事件的信息，就需要使用EVENTDATA函数。

EVENTDATA函数返回一个XML文档，包含有关导致触发器执行的事件的预定义数据。数据类型很大程度上取决于导致触发器执行的触发器，但是所有的触发器都返回触发器被激发的时间、导致触发器执行的进程ID以及事件类型。下面的例子创建了一个服务器范围的DDL触发器，它会在创建、修改或删除数据库时执行

![复制代码][copycode.gif]

USE Master
    GO
    CREATE TRIGGER ServerDBEvent ON ALL SERVER FRO CREATE_DATEBASE,DROP_DATABASE_ALTER_DATABASE AS
    DELCARE @Data AS xml
    DECLARE @EventType AS nvarchar(25)
    DECLARE @PostTime AS nvarchar(25)
    DECLARE @ServerName AS nvarchar(25)
    DECLARE @DBName AS nvarchar(25)
    DECLARE @Login AS nvarchar(25)
    DECLARE @TSQLCommand AS nvarchar(MAX)
    
    SET @Data=EVENTDATA()
    SELECT @EventType=@Data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(25)'),@PostTime=@Data.value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(25)'),@ServerName=@Data.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(25)'),@Login=@Data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(25)'),@DBName=@Data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(25)'),@TSQLCommand=@Data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')
    
    PRINT @EventType
    PRINT @PostTime
    PRINT @ServerName
    PRINT @login
    PRINT @DBName
    PRINT @TSQLCommand;
    GO

![复制代码][copycode.gif]

要测试此触发器，执行下列代码并检查输出结果

USE Master;
    GO
    CREATE DATABASE SampleDB;
    GO
    ALTER DATABASE SampleDB SET RECOVERY SIMPLE;

下面的这个例子中，创建一个用于组织修改数据库的DDL触发器，并在一个审核表中记录视图修改数据库的人员以及他们采用的方式，步骤如下，首先创建一个表，用于包含从DDL触发器收集来的审核信息，执行下列代码

USE AdventureWorks2008;
    GO
    CREATE TABLE DatabaseDDLAudit (AuditID int IDENTITY(1,1) NOT NULL, PostTime datetime NOT NULL, LoginName nvarchar(128) NULL,Command nvarchar(MAX) NULL,EventData xml NULL);

在有了一个审核表之后，就可以创建DDL数据库触发器，它会插入到表中并防止修改数据库，执行下列代码

![复制代码][copycode.gif]

USE AdventureWorks2008
    GO
    CREATE TRIGGER NoDDLAllowed ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
    AS 
    SET NOCOUNT ON
    DECLARE @data AS xml, @PostTime AS datetime, @HostName AS nvarchar(128)
    DECLARE @LoginName AS nvarchar(128), @Command AS nvarchar(MAX)
    
    SET @data=EVENTDATA()
    
    SELECT
    @PostTIme=CAST(@Data.value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(25)') AS datetime),
    @HostName=@Data.value('(/EVENT_INSTANCE/HostName)[1]','nvarchar(25)'),
    @LoginName=@Data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)')
    
    RAISERROR ('What?! Are you nuts? Modifications to this database are not allowed! You can expect a visit from human resources shortly.',16,1)
    ROLLBACK
    INSERT DatabaseDDLAudit(PostTime, LoginTime, Command, EventData) VALUES (@PostTime, @LoginName, @Command, @Data) 
    RETURN;

![复制代码][copycode.gif]

要测试此触发器并查看收集的数据，执行下列代码

USE AdventureWorks2008;
    GO
    ALTER TABLE Person.Person ADD NewColumn varchar(10) NULL;

现在，使用下列命令查询审核表以查看收集的数据

USE AdventureWorks2008;
    GO
    SELECT * FROM DatabaseDDLAudit;
    GO

如果需要对数据库做授权改动，需要禁用或删除（最好禁用）触发器，下面的脚本演示了如何禁用DDL触发器，作出改动并在完成后重新启动触发器

![复制代码][copycode.gif]

USE AdventureWorks2008;
    GO
    DISABLE TRIGGER NoDDLAllowed ON DATABASE;
    GO
    CREATE TABLE TestTable(Column1 int,Column2 int);
    GO
    ENABLE TRIGGER NoDDLAllowed ON DATABASE;
    GO

![复制代码][copycode.gif]

[copycode.gif]: /images/20220527/73b91ed894d14395b587ab9413d2e665.png