如何使用SQLDiag工具来追踪死锁错误(ZT)

SQLDiag最初是在SQL Server 7.0中引入的，它是一个非常方便的工具可以用来监控数据库服务器中出现的问题。这个命令行工具可以为微软的客户提供必需的信息，在运行 时，SQLDiag可以将不同的SQL Server工具提供的数据输出到一个文本文件中，以便于您阅览，该文本文件包含了配置、数据库和错误日志的信息。
在SQL Server 2005中，您可以将 SQLDiag作为一个服务运行，也可以将它作为一个命令行工具。在以下的讨论中，我将假定您的机器上运行的是非集群的SQL Server 2000，而我将通过命令行提示符来发出命令。[b] [/b]
[b]实战[/b][b]SQLDiag[/b]
我觉得实际操作比阅读更容易吸收技术信息，让我们通过一个实例来看看SQLDiag是如何工作的，以及怎样用它让数据库管理员的日子好过些。在这个例子中，我将展示如何捕获造成数据库服务器死锁的语句，并进行研究，然后使用一些技巧性的调整来避免死锁的出现。
如果您对死锁不太熟悉，也不了解它们是怎样对数据库系统造成严重破坏的，可以参考我以前的文章，我可以对死锁做一些简单的解释，以及如何在SQL Server 2005中捕获死锁并重试。
在 服务器上捕获死锁信息之前，我必需开启服务器上的一些追踪标记，一个追踪标记是设定在SQL Server 中的一种状态，它可以监测到服务器对非法操作的响应。在这种情况下，我要开启可以监测死锁错误的追踪标记，并在错误发生时将这些信息写入错误日志。为了开 启这些追踪标记，请执行以下的命令：(您必需具备SQL Server系统管理员权限来运行DBCC命令)
DBCC TRACEON(1204, -1)
DBCC TRACEON(1205, -1)
DBCC TRACEON(3605, -1)
1204 标记将返回死锁时遇到的锁的类型；1205标记则会返回更加详细的信息，它可以记录出现死锁时正在运行的语句；3605标记将把追踪信息发送到错误日 志，SQLDiag工具可以将这些信息输出到一个文本文件中。命令中的“-1”参数表示对所有的数据库连接应用追踪标记。
现在系统已经具备捕获死锁的能力了，现在让我们来创造一个死锁的情况，在列表A中，我创建了一些表格来制造死锁。
IF EXISTS(SELECT [name] FROM sysobjects WHERE type = 'U' AND name = 'DeadlockTable1')
      DROP TABLE DeadlockTable1
GO
IF EXISTS(SELECT [name] FROM sysobjects WHERE type = 'U' AND name = 'DeadlockTable2')
      DROP TABLE DeadlockTable2
GO
CREATE TABLE DeadlockTable1
(
      IDCol TINYINT IDENTITY (1,1) PRIMARY KEY,
      DeadlockValue1 VARCHAR(20)
)
GO
CREATE TABLE DeadlockTable2
(
      IDCol TINYINT IDENTITY (1,1) PRIMARY KEY,
      DeadlockValue2 VARCHAR(20)
)
GO
列表A
现在表格已经创建好了，我将在每个表格中插入一行，这样我们就有一个值来更新了，我将使用两条临近的更新语句来造成死锁，如列表B所示：
INSERT INTO DeadlockTable1 (DeadlockValue1)
SELECT 'Deadlock1'
INSERT INTO DeadlockTable2 (DeadlockValue2)
SELECT 'Deadlock2'
GO
列表B
这 部分很有意思，在以上的代码中，我在每一个表格中都添加了一行更新语句，但是这两条语句又都在互相争夺表资源，最终会造成死锁情况的发生。在此，我将使用 两个单独的数据库连接，剪切并粘贴这部分SQL Server代码到一个连接中，我将它命名为连接A，参见列表C，然后，剪切并粘贴这部分SQL Server代码到另外一个连接，我将它命名为连接B，参见列表D。
BEGIN TRANSACTION
      UPDATE DeadlockTable2
      SET DeadlockValue2 = 'Connection1'
      WAITFOR DELAY '0:0:3'
      UPDATE DeadlockTable1
      SET DeadlockValue1 = 'Connection1'
COMMIT TRANSACTION
列表C
BEGIN TRANSACTION
      UPDATE DeadlockTable1
      SET DeadlockValue1 = 'Connection2'
      WAITFOR DELAY '0:0:6'
      UPDATE DeadlockTable2
      SET DeadlockValue2 = 'Connection2'
COMMIT TRANSACTION
列表D
为 了制造死锁，您需要首先运行连接A中的代码，紧接着运行连接B中的代码，正如连接A中WAITFOR DELAY语句所示的那样，您大约有3秒钟的时间来切换连接并执行连接B中的代码。如果您正确执行了以上步骤，那将会在一个查询窗口中收到和下面这条信息 非常相似的出错信息：
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 53) was deadlocked on
resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.
发 生死锁是因为两个连接之间发生了资源争夺，连接A锁定了DeadlockTable2并试图更新DeadlockTable1，而这是无法实现的，因为与 此同时，连接B锁定了DeadlockTable1，而且需要在DeadlockTable2上运行更新语句，这个场景是一个非常经典的例子，这也是为什 么在设计事务的时候，您要尽可能按照相同的使用顺序来更新表格，这一点非常重要。
如果您按照这种方式来设计事务，那么出现这种令人头疼的情况的可能性就会降低很多。SQL Server数据库引擎有专门的算法来通知您有这种情况出现，它还会系统性地关闭一个进程并允许另外一个顺利完成。
现 在我们已经体验了死锁，我们可以使用SQLDiag工具将错误信息输出到一个文本文件了。您可能会感到奇怪，既然在出现死锁错误的情况下可以从屏幕上看到 错误信息，为什么还要将这些信息输出到文本文件中呢？开启追踪标记可以捕获在您的系统上发生的所有死锁，这对于有大量数据更新的系统来说，这些信息是非常 有价值的。在文本文件中查找并观察何时发生错误、哪些对象牵连在错误中，可以节省您大量的研究时间。
为了执行SQLDiag工具，打开一个命令提示符窗口并浏览到SQL Server安装目录中的Binn文件夹。一个可能出现该工具的文件夹是C:Program FilesMicrosoft SQL ServerMSSQLBinn。
在使用这个工具的时候，您可能会用到一些参数，我会用到－E和－O开关，－E的含义是在执行是使用集成的安全属性；而－O开关可以指定您要输出的文件路径，以下是使用该工具参数的一个例子：
C:Program FilesMicrosoft SQL ServerMSSQLBinn
SQLDiag –E –O C:SQLDiagOutput.txt
[b]Conclusion[/b]
死 锁，尤其在2005版之前的SQL Server中，常常是数据库管理员的致命伤，如果没有好的方法来捕获并重现死锁事件，数据库管理员和开发者不得不依赖SQL Profiler和其它的工具来捕获死锁，并用于进一步的研究工作。SQLDiag是一个非常有效的追踪工具，不仅可以用来对付这些令人尴尬的死锁错误， 还可以用来捕获SQL Server环境中很多其它的诊断信息。我建议您要花些时间来研究这个强大的工具，而不仅仅是尝试本文中的例子，因为SQLDiag可以作为预防性的工具 来避免未来可能出现的令人头痛的问题。

解决/优化SQL Server查询速度慢的方法

1、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量（尺寸）越大，提高I/O越重要。
2、纵向、横向分割表，减少表的尺寸(sp_spaceuse)
3、升级硬件
4、根据SQL Server查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建）,不要对有限的几个值的字段建单一索引如性别字段。
5、提高网速。
6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。
配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server? 2000时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的1.5倍。如果另外安装了全文检索功能，并打算运行Microsoft搜索服务以便执行 全文索引和SQL Server查询。
可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的3倍。将SQL Server max server memory服务器配置选项配置为物理内存的1.5倍（虚拟内存大小设置的一半）。
7、增加服务器CPU个数;但是必须 明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询 的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作UPDATE,INSERT， DELETE还不能并行处理。
8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like ''a%'' 使用索引 like ''%a'' 不使用索引用 like ''%a%'' 查询时，SQL Server查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。
9、DB Server 和APPLication Server 分离；OLTP和OLAP分离
10、分布式分区视图可用于实现数据库服务器联合体。
联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。（参照SQL帮助文件''分区视图''）
a、在实现分区视图之前，必须先水平分区表
b、 在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上 运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。
11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。
在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理SQL Server查询计划的过程是这样的：
1、 查询语句的词法、语法检查
2、 将语句提交给DBMS的查询优化器
3、 优化器做代数优化和存取路径的优化
4、 由预编译模块生成SQL Server查询规划
5、 然后在合适的时间提交给系统处理执行
6、 最后将执行结果返回给用户。
其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。

12、 Commit和rollback的区别 Rollback:回滚所有的事物。 Commit:提交当前的事物. 没有必要在动态SQL里写事物，如果要写请写在外面如： begin tran exec(@s) commit trans 或者将动态SQL 写成函数或者存储过程。
13、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。
14、SQL的注释申明对执行没有任何影响

分析方法：
第一、先优化一下索引，索引对数据查询速度影响很大的。
第二、将日志文件与数据文件分开存放。
第三、优化系统中存储过程的查询条件，这个是提高查询速度的最基本，曾在一个大型公司的数据库中写存储过程，取数是从北京，上海，广州三地取数，数据量超过50万，整个查询不超过 10秒。就是因为索引与存储过程的优化。

–首先从两个方面:
1\分析是硬件的问题,比如内存经常在90%,CPU经常在80%以上,建议加硬件了.提升硬件速度.加内存,内存够的话对整个磁盘的读写能力会减轻很多.我们公司以前的内存是4G,现在升级为64位,可以用16G,速度大幅度提高,但有时也会出现慢,只是有时锁的问题…
  硬盘建议用RAID0+1,现在的硬盘又不贵.数据库一般都建议用RAID10.一是安全,二是速度.都比较有保障.
2\软件方面:
  检测耗用内存,CPU最多的语句或过程,进行跟踪优化,SQL2005中有一个表sys.dm_exec_query_stats可以看到.当然也可以根据Read80Trace程序进行统计跟踪.(比如一个小时的跟踪查看耗用资源最多的语句).
根据这些信息再来优化数据,如:加索引,分区表,….优化语句啊.
总结:以前听过一句话, 数据库慢的80%的因素是SQL语句.所以把SQL语句优化好对速度提高很快的当然加硬件也可以的

A. 按平均 CPU 时间排在前五个的查询
此方案提供有关 CPU 时间、IO 读写以及按平均 CPU 时间排在前五个查询的执行次数的信息。SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],

(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_textFROM sys.dm_exec_query_statsORDER BY [Avg CPU Time] DESC

还是建议做个SQLDIAG的诊断报告，获得整个系统的性能信息，然后才能更有针对性的做出调整。

1、把系统换成64位，然后加大内存。
2、数据库性能的优化。相信很多查询都是有很大的优化空间的，也不清楚你们有没有专门的数据库维护人员，看一下索引碎片之类的情况。 

Important: ASP.NET Security Vulnerability

http://weblogs.asp.net/scottgu/archive/2010/09/18/important-asp-net-security-vulnerability.aspx