前言

前言

Trace flags can be used to alter the behavior of SQL Server and they can
help when diagnosing performance issues as well. During the course of
days I’ve come across cases from where I learnt about them & their
purpose; certainly do let me know for any corrections that I may have
missed out.

一.What is Trace?

对于SQL Profiler这个工具相信大家都不是很陌生,没用过的朋友可以在SQL
Server Management Studio>工具>SQL Server
Profiler处使用。这个工具是用来监控SQL,存储过程的执行,用户登录等等信息。但这个工具只是一个GUI,他的本质就是Trace。下面是Trace的架构:

图片 1  

数据库引擎会产生一系列事件,然后各个trace可以去订阅自己感兴趣的事件,一旦数据库产生了相关事件就会发给订阅该事件的trace,各个trace通过自己的过滤器对该事件的信息过滤(例如:捕获执行时间超过1秒的SQL语句),然后放到一个缓冲队列里,最终写入文件或者是一些客户端应用。

简单介绍完理论,下面就开始实践:

一提到跟踪俩字,很多人想到警匪片中的场景,同样在我们的SQL
Server数据库中“跟踪”也是无处不在的,如果我们利用好了跟踪技巧,就可以针对某些特定的场景做定向分析,找出充足的证据来破案。

一提到跟踪俩字,很多人想到警匪片中的场景,同样在我们的SQL
Server数据库中“跟踪”也是无处不在的,如果我们利用好了跟踪技巧,就可以针对某些特定的场景做定向分析,找出充足的证据来破案。

 

Summary: This paper introduces Trace and Replay objects, a
new feature in Microsoft SQL Server 2005. Trace and Replay
objects is a new managed API for tracing, trace manipulation, and trace
replay. (12 printed pages)

How to Create a Trace?

要创建一个追踪器,总共分三步:

1.执行存储过程sp_trace_create创建一个追踪器

2.执行存储过程sp_trace_setevent添加自己想订阅的事件以及最终结果集的列名

3.执行存储过程sp_trace_setfilter设置过滤器来对过滤产生数据

下面是一个创建一个追踪器的SQL脚本

 1 DECLARE @return_code INT;
 2 DECLARE @TraceID INT;
 3 DECLARE @maxfilesize BIGINT;
 4 SET @maxfilesize = 5;
 5 --step 1: create a new empty trace definition
 6 EXEC sp_trace_create
 7                 @traceid OUTPUT
 8                , @options = 2
 9                , @tracefile = N'C:\TraceFiles\LongRunningQueries'
10                , @maxfilesize = @maxfilesize
11     , @stoptime =NULL
12     , @filecount = 2; 
13 -- step 2: add the events and columns
14 EXEC sp_trace_setevent
15                 @traceid = @TraceID
16                , @eventid = 10 -- RPC:Completed
17                , @columnid = 1 -- TextData
18                , @on = 1;--include this column in trace
19 EXEC sp_trace_setevent
20                 @traceid = @TraceID
21                , @eventid = 10 -- RPC:Completed
22                , @columnid = 13 --Duration
23                , @on = 1;--include this column in trace
24 EXEC sp_trace_setevent
25                 @traceid = @TraceID
26                , @eventid = 10 -- RPC:Completed
27                , @columnid = 15 --EndTime
28                , @on = 1;--include this column in trace  
29 EXEC sp_trace_setevent
30                 @traceid = @TraceID
31                , @eventid = 12 -- SQL:BatchCompleted
32                , @columnid = 1 -- TextData
33                , @on = 1;--include this column in trace
34 EXEC sp_trace_setevent
35                 @traceid = @TraceID
36                , @eventid = 12 -- SQL:BatchCompleted
37                , @columnid = 13 --Duration
38                , @on = 1;--include this column in trace
39 EXEC sp_trace_setevent
40                 @traceid = @TraceID
41                , @eventid = 12 -- SQL:BatchCompleted
42                , @columnid = 15 --EndTime
43                , @on = 1;--include this column in trace        
44 -- step 3: add duration filter
45 DECLARE @DurationFilter BIGINT;
46 SET @DurationFilter = 10000000; --duration in microseconds
47 EXEC sp_trace_setfilter
48                 @traceid = @TraceID
49                , @columnid = 13
50                , @logical_operator = 0 --AND
51                , @comparison_operator = 4 -- greater than or equal to
52                , @value = @DurationFilter; --filter value
53 SELECT @TraceID AS TraceID;

对于第九行中的C:\TraceFiles\LongRunningQueries,请确保TraceFiles文件夹存在,而LongRunningQueries是文件名,创建后会自动加上.trc后缀。

对于过滤器中具体的事件以及列名,大家可以参考:

对于像我一样的一些初学者,写上面的脚本可能比较吃力,那么我们可以通过SQL
Server Profiler配置各种需求,然后导出脚本:

图片 2

然后把路径等一系列其他参数设置下即可。

简单的举几个应用场景:

简单的举几个应用场景:

Flags can be set for Session or Global (some are startup) levels though
for later some can only be switched at startup using –T (you are
suggested to avoid –t which turns on other internal flags) otherwise
DBCC TRACEON/TRACEOFF (FlagNo,(-1) optional ) comes quite handy.

Contents

Introduction
Trace Objects
Replay Objects
Conclusion

三.How to Operate a Trace?

上面我们已经把创建了一个追踪器,但这个追踪器目前并未开始运行,我们可以通过下面的脚本来查看trace的状态

select * from sys.traces

执行之后你会发现有2个trace记录,第一个是SQL
Server默认的trace,它提供极其有限的功能,第二个就是我们刚刚创建的trace

图片 3

status就是指追踪器的状态

@status Action
0 Stops the trace
1 Starts the trace
2 Closes the trace and deletes its definition

 

 

 

操作status的脚本:

-- stop  the trace
 DECLARE @TraceID int ;
 SET @TraceID = 2 ; -- specify value from sp_trace_create
 EXEC sp_trace_setstatus
    @traceid = @TraceID
    ,@status = 0 ;-- stop trace
 -- delete the trace
 EXEC sp_trace_setstatus
    @traceid = @TraceID
  ,@status = 2 ;-- delete trace
 -- start the trace
 EXEC sp_trace_setstatus
    @traceid = @TraceID
  ,@status = 1 ;-- start trace

在线生产库为何突然宕机?数百张数据表为何不翼而飞?刚打好补丁的系统为何屡遭黑手?新添加的信息表为何频频丢失?某张表字段的突然更改,究竟为何人所为?这些个匿名的访问背后,究竟是人是鬼?突然增加的增量数据,究竟是对是错?数百兆的日志爆炸式的增长背后又隐藏着什么?这一且的背后,是应用程序的BUG还是用户品质的缺失?

在线生产库为何突然宕机?数百张数据表为何不翼而飞?刚打好补丁的系统为何屡遭黑手?新添加的信息表为何频频丢失?某张表字段的突然更改,究竟为何人所为?这些个匿名的访问背后,究竟是人是鬼?突然增加的增量数据,究竟是对是错?数百兆的日志爆炸式的增长背后又隐藏着什么?这一且的背后,是应用程序的BUG还是用户品质的缺失?

 

Introduction

This paper is written for users who deal with SQL Profiler or collect
Microsoft SQL Server traces and want to automate trace collection, trace
manipulation, and trace replay.

Knowledge of the C# language and some experience with the SQL Profiler
tool is required to fully take advantage of the information provided.

SQL Server trace is a mechanism for monitoring and recording activity
inside SQL Server. Essentially, when any activity occurs (a query is
sent against the instance of SQL Server, for example), a special entity
called an event is generated inside the server. The event is then
shipped to a monitoring tool called SQL Profiler, which displays this
event and all its attributes (called columns).

This combination of SQL Trace and SQL Profiler has proven to be a very
powerful performance analyzing and tuning tool—it enables users to see
what activity a particular application generates and what impact it has
on the server.

However, the SQL Profiler tool is completely manual; therefore, it is
not easy to automate the creation of the trace or perform trace analysis
and trace manipulation. For most of these tasks, users must start SQL
Profiler and use it interactively.

Trace and Replay objects, a new feature in Microsoft SQL Server
2005 that offers a new managed API for tracing and trace replay, are
introduced to enable automation.

Trace and Replay objects simplify the management of SQL Server
in the following ways:

  • First, they enable users to completely automate tuning, security
    audits, and health monitoring of traced servers.
  • Second, they enable automatic functional verification of new server
    releases and security packs.
  • Third, they provide users with the ability to establish performance
    benchmarks against current server performance.

This paper discusses Trace objects and Replay objects in detail.

四.How to Viewing Trace Data?

前面已经说过,追踪器最终把追踪到的信息写到了一个文件里,也就是我们创建时指定的路径。通过一个系统函数就可以查看分析这些数据了:

SELECT *
FROM fn_trace_gettable(N'C:\TraceFiles\LongRunningQueries.trc',DEFAULT);

请关注本篇文章,让我们一起利用数据库的“跟踪”(Trace)走进数据库背后,查看其内部原理。

请关注本篇文章,让我们一起利用数据库的“跟踪”(Trace)走进数据库背后,查看其内部原理。

Note: This list also contains some numbers which have not been
checked, so in case you come across those a have a conflict do let me
know.

Trace Objects

Organizations where the tracing of SQL Servers is performed on a regular
basis often have to deal with large amounts of trace data, which needs
to be processed and analyzed. SQL Profiler provides features to filter
the traces; such an approach can be extremely labor-intensive, however.
The ability to create a small program that would perform analysis in an
automated fashion would provide a better way to handle this. For
example, such a program could crawl through trace data and identify the
most frequently executed query on the server. Such a program could be
reused and perhaps later expanded to include increasingly sophisticated
analysis logic.

Another important aspect of automation is a timely reaction to events
that are happening on the server. It would be convenient to have a
program that could start a trace, monitor events of the trace, and
perhaps alert the administrator if the duration of particular queries
exceeds a certain threshold.

It would also be convenient to be able to implement a way to perform
other tasks, like moving trace files into SQL Server tables, or from
tables to files, in a customized fashion.

Trace Object technology can help address these and other problems.

Trace objects serve the purpose of starting a new trace, and of
reading and writing trace log files and trace tables. Essentially, there
are three classes for accomplishing these purposes:

  • TraceServer—Starts and reads a new trace.
  • TraceFile—Reads and writes to a trace file.
  • TraceTable—Reads and writes to a trace table.

These classes belong to the Microsoft.SqlServer.Management.Trace
namespace and provide an abstraction of a stream of trace events. This
stream could be read-only (TraceServer) or provide reading and
writing capabilities (TraceFile and TraceTable). Figure 1
illustrates the relationships between these classes.

图片 4.gif)

五.Summary

SQL
Trace对象提供服务器端的追踪的技术,包括一些存储过程,数据库,视图来创建trace,
通过使用T-SQL和trace暴露的元数据来管理trace数据,这些都是SQL
Profiler所无法提供的

 

 

 

WARNING: These should be practiced with caution where they may not
cause a performance boost and ‘ve an adverse effect in some cases. .
They are used in this post for discussion purposes only and may not be
supported in future versions.

我相信如用过SQL Server数据库的人,都会或多或少的利用过SQL
Profiler工具。这个玩意就是利用SQL
Trace形成的一个图形化操作工具,我们直接进入本篇的正题。

我相信如用过SQL Server数据库的人,都会或多或少的利用过SQL
Profiler工具。这个玩意就是利用SQL
Trace形成的一个图形化操作工具,我们直接进入本篇的正题。

flag

 

 

Trace Flag Description (underlined are
sp_configure’able)

一.查看系统默认跟踪信息(Default Trace)

一.查看系统默认跟踪信息(Default Trace)

-1

Trace作为一个很好的数据库追踪工具,在SQL Server
2005中便集成到系统功能中去,并且默认是开启的,当然我们也可以手动的关掉它,它位于sp_config配置参数中,我们可以通过以下语句查看:

Trace作为一个很好的数据库追踪工具,在SQL Server
2005中便集成到系统功能中去,并且默认是开启的,当然我们也可以手动的关掉它,它位于sp_config配置参数中,我们可以通过以下语句查看:

Sets trace flags for all connections.

select * from sys.configurations where configuration_id = 1568
select * from sys.configurations where configuration_id = 1568

105

图片 5

图片 5

SQL 6.5 – To over ride limitation of max 16 tables or sub queries
allowed in a single select statement.

我们也可以通过下面的语句找到这个跟踪的记录

我们也可以通过下面的语句找到这个跟踪的记录

106

select * from sys.traces
select * from sys.traces

SQL 6.5/7 – Disables line number information for syntax errors.

图片 7

图片 7

107

如果没有开启,我们也可以利用如下语句进行开启,或者关闭等操作

如果没有开启,我们也可以利用如下语句进行开启,或者关闭等操作

SQL 6.5/7/8 – Interprets numbers with a decimal point as float instead
of decimal. KB 203787

图片 9

图片 10

110

--开启Default Trace
sp_configure 'show advanced options' , 1 ;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled' , 1 ;
GO
RECONFIGURE;
GO

--测试是否开启
EXEC sp_configure 'default trace enabled';
GO

--关闭Default Trace
sp_configure 'default trace enabled' , 0 ;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options' , 0 ;
GO
RECONFIGURE;
GO
--开启Default Trace
sp_configure 'show advanced options' , 1 ;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled' , 1 ;
GO
RECONFIGURE;
GO

--测试是否开启
EXEC sp_configure 'default trace enabled';
GO

--关闭Default Trace
sp_configure 'default trace enabled' , 0 ;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options' , 0 ;
GO
RECONFIGURE;
GO

SQL 6.5 – Turns off ANSI select characteristics.
KB 152032

图片 11

图片 12

146

通过以下命令找到默认跟踪的文件路径

通过以下命令找到默认跟踪的文件路径

Consider using when replaying against SQL 8.0, to avoid an attempt to
set an encrypted connection.

select * from ::fn_trace_getinfo(0)
select * from ::fn_trace_getinfo(0)

168

图片 13

图片 13

SQL 9/10 – On querying through a view that uses the ORDER BY clause, the
result are still returned in random order. KB 926292

以上命令返回的结果值,各个值(property)代表的含义如下:

以上命令返回的结果值,各个值(property)代表的含义如下:

204

第一个:2表示滚动文件;

第一个:2表示滚动文件;

SQL 6.5 – Backward compatibility switch that enables non-ansi standard
behavior. E.g. previously SQL server ignored trailing blanks in the like
statement and allowed queries that contained aggregated functions to
have items in the group by clause that were not in the select list.

第二个:表示当前使用的trace文件路径,根据它我们可以找到其它的跟踪文件,默认是同一目录下

第二个:表示当前使用的trace文件路径,根据它我们可以找到其它的跟踪文件,默认是同一目录下

205

第三个:表示滚动文件的大小(单位MB),当到达这个值就会创建新的滚动文件

第三个:表示滚动文件的大小(单位MB),当到达这个值就会创建新的滚动文件

SQL 7/8 – Report when a statistics-dependent stored procedure is being
recompiled as a result of AutoStat.
KB 195565

第四个:跟踪的停止时间,这里为Null,表示没有固定的停止时间

第四个:跟踪的停止时间,这里为Null,表示没有固定的停止时间

206

第五个:当前跟踪的状态:0 停止;1 运行

第五个:当前跟踪的状态:0 停止;1 运行

SQL 6.5 – Provides backward compatibility for the set user statement. KB
160732

 

 

208

找到该目录,我们查看下该文件:

找到该目录,我们查看下该文件:

SET QUOTED IDENTIFIER ON.

图片 15

图片 15

210

系统默认提供5个跟踪文件,并且每一个文件默认大小都是20MB,SQL
Server会自己维护这5个文件,当实例重启的时候或者到达最大值的时候,之后会重新生成新的文件,将最早的跟踪文件删除,依次滚动更新。

系统默认提供5个跟踪文件,并且每一个文件默认大小都是20MB,SQL
Server会自己维护这5个文件,当实例重启的时候或者到达最大值的时候,之后会重新生成新的文件,将最早的跟踪文件删除,依次滚动更新。

SQL 9 – Error when you run a query against a view: “An error occurred
while executing batch”.
KB 945892

 

 

212

我们通过以下命令来查看跟踪文件中的内容:

我们通过以下命令来查看跟踪文件中的内容:

SQL 9 – Query may run much slower when compared to SQL 8 when you use a
cursor to run the query. KB 951184

图片 17

图片 17

237

 默认的跟踪文件,提供的跟踪信息还是很全的,从中我们可以找到登录人,操作信息等,上面的截图只是包含的部分信息。我们可以利用该语句进行自己的加工,然后获得更有用的信息。

 默认的跟踪文件,提供的跟踪信息还是很全的,从中我们可以找到登录人,操作信息等,上面的截图只是包含的部分信息。我们可以利用该语句进行自己的加工,然后获得更有用的信息。

Tells SQL Server to use correlated sub-queries in Non-ANSI standard
backward compatibility mode.

图片 19

图片 20

242

--获取跟踪文件中前100行执行内容
SELECT TOP 100
 gt.[HostName] 
,gt.[ServerName] 
,gt.[DatabaseName] 
,gt.[SPID] 
,gt.[ObjectName] 
,gt.[objecttype] [ObjectTypeID] 
,sv.[subclass_name] [ObjectType] 
,e.[category_id] [CategoryID] 
,c.[Name] [Category] 
,gt.[EventClass] [EventID] 
,e.[Name] [EventName] 
,gt.[LoginName] 
,gt.[ApplicationName] 
,gt.[StartTime] 
,gt.[TextData] 
FROM fn_trace_gettable('E:\dataDefaultFileManger\MSSQL10.MSSQLSERVER\MSSQL\Log\log_1267.trc', DEFAULT) gt 
LEFT JOIN sys.trace_subclass_values sv 
ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype] 
INNER JOIN sys.trace_events e 
ON gt.[eventclass] = e.[trace_event_id] 
INNER JOIN sys.trace_categories c 
ON e.[category_id] = c.[category_id] 
WHERE gt.[spid] > 50 AND --50以内的spid为系统使用
    gt.[DatabaseName] = 'master' AND --根据DatabaseName过滤
    gt.[ObjectName] = 'fn_trace_getinfo' AND --根据objectname过滤
    e.[category_id]  = 5 AND --category 5表示对象,8表示安全
    e.[trace_event_id] = 46 
    --trace_event_id 
    --46表示Create对象(Object:Created),
    --47表示Drop对象(Object:Deleted),
    --93表示日志文件自动增长(Log File Auto Grow),
    --164表示Alter对象(Object:Altered),
    --20表示错误日志(Audit Login Failed)
ORDER BY [StartTime] DESC
--获取跟踪文件中前100行执行内容
SELECT TOP 100
 gt.[HostName] 
,gt.[ServerName] 
,gt.[DatabaseName] 
,gt.[SPID] 
,gt.[ObjectName] 
,gt.[objecttype] [ObjectTypeID] 
,sv.[subclass_name] [ObjectType] 
,e.[category_id] [CategoryID] 
,c.[Name] [Category] 
,gt.[EventClass] [EventID] 
,e.[Name] [EventName] 
,gt.[LoginName] 
,gt.[ApplicationName] 
,gt.[StartTime] 
,gt.[TextData] 
FROM fn_trace_gettable('E:\dataDefaultFileManger\MSSQL10.MSSQLSERVER\MSSQL\Log\log_1267.trc', DEFAULT) gt 
LEFT JOIN sys.trace_subclass_values sv 
ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype] 
INNER JOIN sys.trace_events e 
ON gt.[eventclass] = e.[trace_event_id] 
INNER JOIN sys.trace_categories c 
ON e.[category_id] = c.[category_id] 
WHERE gt.[spid] > 50 AND --50以内的spid为系统使用
    gt.[DatabaseName] = 'master' AND --根据DatabaseName过滤
    gt.[ObjectName] = 'fn_trace_getinfo' AND --根据objectname过滤
    e.[category_id]  = 5 AND --category 5表示对象,8表示安全
    e.[trace_event_id] = 46 
    --trace_event_id 
    --46表示Create对象(Object:Created),
    --47表示Drop对象(Object:Deleted),
    --93表示日志文件自动增长(Log File Auto Grow),
    --164表示Alter对象(Object:Altered),
    --20表示错误日志(Audit Login Failed)
ORDER BY [StartTime] DESC

Provides backward compatibility for correlated subqueries where
non-ANSI-standard results are desired.

图片 21

图片 22

243

图片 23

图片 23

Provides backward compatibility for nullability behavior. When set, SQL
Server has the same nullability violation behavior as that of a ver 4.2:

 我创建了一张表,通过上面的跟踪,可以跟踪到该记录的信息,根据不同的过滤信息,我们可以查询出到跟踪的某个库的某个表的更改信息,包括:46创建(Created)、47删除(Deleted)、93文件自动增长信息(Log
File Auto Grow)、146修改(Alter)、20表示错误日志(Login Failed)

 我创建了一张表,通过上面的跟踪,可以跟踪到该记录的信息,根据不同的过滤信息,我们可以查询出到跟踪的某个库的某个表的更改信息,包括:46创建(Created)、47删除(Deleted)、93文件自动增长信息(Log
File Auto Grow)、146修改(Alter)、20表示错误日志(Login Failed)

  • Processing of the entire batch is terminated if the nullability
    error (inserting NULL into a NOT NULL field) can be detected at
    compile time.
  • Processing of offending row is skipped, but the command continues if
    the nullability violation is detected at run time.

 

 

Behavior of SQL Server is now more consistent because nullability checks
are made at run time and a nullability violation results in the command
terminating and the batch or transaction process continuing.

在生产环境中,以上几个分类都是比较常用的,对定位部分问题的定位能够在找到充分的证据可循,比如某厮将数据库数据删除掉了还不承认等,这里面的Login
Failed信息,能够追踪出有那么用户尝试登陆过数据库,并且失败,如果大面积的出现这种情况,那就要谨防黑客袭击了。

在生产环境中,以上几个分类都是比较常用的,对定位部分问题的定位能够在找到充分的证据可循,比如某厮将数据库数据删除掉了还不承认等,这里面的Login
Failed信息,能够追踪出有那么用户尝试登陆过数据库,并且失败,如果大面积的出现这种情况,那就要谨防黑客袭击了。

244

 

 

Disables checking for allowed interim constraint violations. By default,
SQL Server checks for and allows interim constraint violations. An
interim constraint violation is caused by a change that removes the
violation such that the constraint is met, all within a single statement
and transaction. SQL Server checks for interim constraint violations for
self-referencing DELETE statements, INSERT, and multi-row UPDATE
statements. This checking requires more work tables. With this trace
flag you can disallow interim constraint violations, thus requiring
fewer work tables.

当然,这里我还可以利用SQL
Server自带的Profile工具,打开查看跟踪文件中的内容。

当然,这里我还可以利用SQL
Server自带的Profile工具,打开查看跟踪文件中的内容。

246

图片 25

图片 25

Derived or NULL columns must be explicitly named in a select…INTO or
create view statement when not done they raise an error. This flag
avoids that.

这个图像化的工具就比较熟悉了,直接打开进行筛选就可以了。

这个图像化的工具就比较熟悉了,直接打开进行筛选就可以了。

253

这种方式看似不错,但是它也有本身的缺点,我们来看:

这种方式看似不错,但是它也有本身的缺点,我们来看:

Prevents ad-hoc query plans to stay in cache.

1、这5个文件是滚动更新的,而且每个文件默认最大都为20MB,并且没有提供更改的接口,所以当文件填充完之后就会删除掉,所以会找不到太久以前的内容;

1、这5个文件是滚动更新的,而且每个文件默认最大都为20MB,并且没有提供更改的接口,所以当文件填充完之后就会删除掉,所以会找不到太久以前的内容;

257

2、本身默认的跟踪,只是提供一些关键信息的追踪,其中包括:auditing
events,database events,error events,full text events,object
creation,object deletion,object
alteration,想要找到其它更详细的内容,此方式可能无能为力;

2、本身默认的跟踪,只是提供一些关键信息的追踪,其中包括:auditing
events,database events,error events,full text events,object
creation,object deletion,object
alteration,想要找到其它更详细的内容,此方式可能无能为力;

Will invoke a print algorithm on the XML output before returning it to
make the XML result more readable.

3、在SQL Server2012后续版本的 Microsoft SQL Server
将删除该功能,改用扩展事件。

3、在SQL Server2012后续版本的 Microsoft SQL Server
将删除该功能,改用扩展事件。

260

 

 

Prints versioning information about extended stored procedure
dynamic-link libraries (DLLs). For more information
about __GetXpVersion(), seeCreating Extended Stored
Procedures. Scope: global
or session

二.自定义跟踪信息(Default Trace)

二.自定义跟踪信息(Default Trace)

262

根据上面SQL Server自带的跟踪信息有一些局限性,SQL
Server为我们提供了自定义跟踪的接口,我们可以自己定义跟踪,充分扩展方法。

根据上面SQL Server自带的跟踪信息有一些局限性,SQL
Server为我们提供了自定义跟踪的接口,我们可以自己定义跟踪,充分扩展方法。

SQL 7 – Trailing spaces are no longer truncated from literal strings in
CASE statements.       KB 891116

利用如下系统存储过程,我们可以创建自定义的Trace

利用如下系统存储过程,我们可以创建自定义的Trace

302

sp_trace_create [ @traceid = ] trace_id OUTPUT 
          , [ @options = ] option_value  
          , [ @tracefile = ] 'trace_file' 
     [ , [ @maxfilesize = ] max_file_size ]
     [ , [ @stoptime = ] 'stop_time' ]
     [ , [ @filecount = ] 'max_rollover_files' ]
sp_trace_create [ @traceid = ] trace_id OUTPUT 
          , [ @options = ] option_value  
          , [ @tracefile = ] 'trace_file' 
     [ , [ @maxfilesize = ] max_file_size ]
     [ , [ @stoptime = ] 'stop_time' ]
     [ , [ @filecount = ] 'max_rollover_files' ]

Should be used with 310 to show the actual join ordering. Prints
information about whether the statistics page is used, the actual
selectivity (if available), and what SQL Server estimated the physical
and logical I/O would be for the indexes.

@traceid  系统默认分配跟踪的ID号

@traceid  系统默认分配跟踪的ID号

310

@options 指定为跟踪设置的选项,系统默认提供的几个选项:

**@options **指定为跟踪设置的选项,系统默认提供的几个选项:

Prints information about join order. Index selection information is also
available in a more readable format using SET SHOWPLAN_ALL, as
described in the SET statement.

                  2表示当文件写满的时候,关闭当前跟踪并创建新文件。

                  2表示当文件写满的时候,关闭当前跟踪并创建新文件。

320

                  4表示如果不能将跟踪写入文件,不管什么原因导致,SQL
Server则会关闭。这个可以利用此选项,追踪问题

                  4表示如果不能将跟踪写入文件,不管什么原因导致,SQL
Server则会关闭。这个可以利用此选项,追踪问题

Disables join-order heuristics used in ANSI joins. To see join-order
heuristics use flag 310. SQL Server uses join-order heuristics to reduce
the no’ of permutations when using the best join order.

                  8制定服务器产生的最后5MB的跟踪信息记录由服务器保存。

                  8制定服务器产生的最后5MB的跟踪信息记录由服务器保存。

323

@tracefile 跟踪文件的路径,这里可以是share的路径

@tracefile 跟踪文件的路径,这里可以是share的路径

SQL 6.5 – Reports on the use of update statements using UPDATE in place.
Shows a detailed description of the various update methods used.

@maxfilesize 跟踪文件的大小,单位是MB,默认不设置为5MB

@maxfilesize 跟踪文件的大小,单位是MB,默认不设置为5MB

325

@stoptime 跟踪停止的时间,利用它我们可以定时跟踪结束的日期

@stoptime 跟踪停止的时间,利用它我们可以定时跟踪结束的日期

Prints information about the cost of using a non-clustered index or a
sort to process an ORDER BY clause.

@filecount 默认生产的跟踪文件的数量,比如默认的为5个,那就在第5个文件写完的时候进行覆盖第1个文件滚动

@filecount 默认生产的跟踪文件的数量,比如默认的为5个,那就在第5个文件写完的时候进行覆盖第1个文件滚动

326

 

 

Prints information about estimated & actual costs of sorts. Instructs
server to use arithmetic averaging when calculating density instead of a
geometric weighted average when updating statistics. Useful for building
better stats when an index has skew on the leading column. Use only for
updating the stats of a table/index with known skewed data.

比如我们可以利用如下脚本进行创建 

比如我们可以利用如下脚本进行创建 

330

图片 27

图片 28

Enables full output when using the SET SHOWPLAN_ALL option, which gives
detailed information about joins.

--创建跟踪文件返回值
declare @rc int
--创建一个跟踪句柄
declare @TraceID int
--创建跟踪文件路径
declare @TraceFilePath nvarchar(500)
set @TraceFilePath=N'F:\SQLTest\'
--跟踪文件的大小
declare @maxfilesize bigint
set @maxfilesize=5
--设置停止的时间
declare @EndTime datetime
set @EndTime=null
--设置系统默认的操作
declare @options int
set @options=2
--设置默认滚动文件的数目
declare @filecount int
set @filecount=5

exec @rc=sp_trace_Create
@TraceID output,
@options,
@TraceFilePath,
@maxfilesize,
@EndTime,
@filecount
if(@rc=0)
select  @TraceID
--创建跟踪文件返回值
declare @rc int
--创建一个跟踪句柄
declare @TraceID int
--创建跟踪文件路径
declare @TraceFilePath nvarchar(500)
set @TraceFilePath=N'F:\SQLTest\'
--跟踪文件的大小
declare @maxfilesize bigint
set @maxfilesize=5
--设置停止的时间
declare @EndTime datetime
set @EndTime=null
--设置系统默认的操作
declare @options int
set @options=2
--设置默认滚动文件的数目
declare @filecount int
set @filecount=5

exec @rc=sp_trace_Create
@TraceID output,
@options,
@TraceFilePath,
@maxfilesize,
@EndTime,
@filecount
if(@rc=0)
select  @TraceID

342

图片 29

图片 30

Disables the costing of pseudo-merge joins, thus significantly reducing
time spent on the parse for certain types of large, multi-table joins.
One can also use SET FORCEPLAN ON to disable the costing of pseudo-merge
joins because the query is forced to use the order specified in the FROM
clause.

我们通过上面的跟踪创建的过程,可以在系统自带的默认的sys.traces中找到该跟踪的明细
图片 31

我们通过上面的跟踪创建的过程,可以在系统自带的默认的sys.traces中找到该跟踪的明细
图片 31

345

select * from sys.traces
where id=2
select * from sys.traces
where id=2

Increase the accuracy of choice of optimum order when you join 6 or more
tables.

图片 33

图片 33

506

通过上面的脚本,我们已经创建了一个新的跟踪(trace),但是这个跟踪状态为0,也就是说还没有运行,下面我们的步骤就是要为这个跟踪添加事件(event)

通过上面的脚本,我们已经创建了一个新的跟踪(trace),但是这个跟踪状态为0,也就是说还没有运行,下面我们的步骤就是要为这个跟踪添加事件(event)

Enforces SQL-92 standards regarding null values for comparisons between
variables and parameters. Any comparison of variables and parameters
that contain a NULL always results in a NULL.

 

 

610

这个也是利用SQL Server为我们提供的操作函数

这个也是利用SQL Server为我们提供的操作函数

SQL 10 – Enable the potential for minimal-logging when:

sp_trace_setevent [ @traceid = ] trace_id  
          , [ @eventid = ] event_id 
          , [ @columnid = ] column_id 
          , [ @on = ] on
sp_trace_setevent [ @traceid = ] trace_id  
          , [ @eventid = ] event_id 
          , [ @columnid = ] column_id 
          , [ @on = ] on
  • Bulk loading into an empty clustered index, with no nonclustered
    indexes
  • Bulk loading into a non-empty heap, with no nonclustered indexes

@traceid 要修改的跟踪的 ID号

@traceid 要修改的跟踪的 ID号

611

@eventid 要打开的事件的 ID

@eventid 要打开的事件的 ID

SQL 9 – When turned on, each lock escalation is recorded in the error
log along with the SQL Server handle number.

@columnid 要为该事件添加的列的 ID

**@columnid **要为该事件添加的列的 ID

652

@on 表示事件状态

@on 表示事件状态

Disables read ahead for the server.

其中最主要的就是时间ID,这个是SQL
Server为我们提供的一些列的码表时间值,具体值可以参考联机丛书 sp_trace_setevent
(Transact-SQL).aspx)

其中最主要的就是时间ID,这个是SQL
Server为我们提供的一些列的码表时间值,具体值可以参考联机丛书 sp_trace_setevent
(Transact-SQL).aspx)

653

这里面最常用的就是:

这里面最常用的就是:

Disables read ahead for the current connection.

事件号

事件名称

说明

10                 

RPC:Completed

在完成了远程过程调用 (RPC) 时发生。

11

RPC:Starting

在启动了 RPC 时发生。

12

SQL:BatchCompleted

在完成了 Transact-SQL 批处理时发生。

13

SQL:BatchStarting

在启动了 Transact-SQL 批处理时发生。

14

Audit Login

在用户成功登录到 SQL Server 时发生。

15

Audit Logout

在用户从 SQL Server 注销时发生。

16

Attention

在发生需要关注的事件(如客户端中断请求或客户端连接中断)时发生。

17

ExistingConnection

检测在启动跟踪前连接到 SQL Server 的用户的所有活动。

18

Audit Server Starts and Stops

在修改 SQL Server 服务状态时发生。

20

Audit Login Failed

指示试图从客户端登录到 SQL Server 失败。

21

EventLog

指示已将事件记录到 Windows 应用程序日志中。

22

ErrorLog

指示已将错误事件记录到 SQL Server 错误日志中。

23

Lock:Released

指示已释放某个资源(如页)的锁。

24

Lock:Acquired

指示获取了某个资源(如数据页)的锁。

25

Lock:Deadlock

指示两个并发事务由于试图获得对方事务拥有的资源的不兼容锁而发生了相互死锁。

26

Lock:Cancel

指示已取消获取资源锁(例如,由于死锁)。

27

Lock:Timeout

指示由于其他事务持有所需资源的阻塞锁而使对资源(例如页)锁的请求超时。 超时由 @@LOCK_TIMEOUT 函数确定,并可用 SET LOCK_TIMEOUT 语句设置。

28

Degree of Parallelism Event(7.0 插入)

在执行 SELECT、INSERT 或 UPDATE 语句之前发生。

33

Exception

指示 SQL Server 中出现了异常。

34

SP:CacheMiss

指示未在过程缓存中找到某个存储过程。

35

SP:CacheInsert

指示某个项被插入到过程缓存中。

36

SP:CacheRemove

指示从过程缓存中删除了某个项。

37

SP:Recompile

指示已重新编译存储过程。

38

SP:CacheHit

指示在过程缓存中找到了存储过程。

40

SQL:StmtStarting

在启动了 Transact-SQL 语句时发生。

41

SQL:StmtCompleted

在完成了 Transact-SQL 语句时发生。

42

SP:Starting

指示启动了存储过程。

43

SP:Completed

指示完成了存储过程。

44

SP:StmtStarting

指示已开始执行存储过程中的 Transact-SQL 语句。

45

SP:StmtCompleted

指示存储过程中的 Transact-SQL 语句已执行完毕。

46

Object:Created

指示 CREATE INDEX、CREATE TABLE 和 CREATE DATABASE 这样的语句已创建了一个对象。

47

Object:Deleted

指示已在 DROP INDEX 和 DROP TABLE 这样的语句中删除了对象。

50

SQL Transaction

跟踪 Transact-SQL BEGIN、COMMIT、SAVE 和 ROLLBACK TRANSACTION 语句。

51

Scan:Started

指示启动了表或索引扫描

52

Scan:Stopped

指示停止了表或索引扫描。

53

CursorOpen

指示 ODBC、OLE DB 或 DB-Library 在 Transact-SQL 语句中打开了一个游标。

54

TransactionLog

将事务写入事务日志时进行跟踪。

55

Hash Warning

指示未在缓冲分区进行的某一哈希操作(例如,哈希联接、哈希聚合、哈希 union 运算、哈希非重复)已恢复为替换计划。 发生此事件的原因可能是递归深度、数据扭曲、跟踪标记或位计数。

58

Auto Stats

指示发生了自动更新索引统计信息。

59

Lock:Deadlock Chain

为导致死锁的每个事件而生成。

60

Lock:Escalation

指示较细粒度的锁转换成了较粗粒度的锁(例如,页锁升级或转换为 TABLE 或 HoBT 锁)。

61

OLE DB Errors

指示发生了 OLE DB 错误。

67

Execution Warnings

指示在执行 SQL Server 语句或存储过程期间发生的任何警告。

68

Showplan Text (Unencoded)

显示所执行 Transact-SQL 语句的计划树。

69

Sort Warnings

指示不适合内存的排序操作。 不包括与创建索引有关的排序操作;只包括某查询内的排序操作(如 SELECT 语句中使用的 ORDER BY 子句)。

70

CursorPrepare

指示已准备了 ODBC、OLE DB 或 DB-Library 用于 Transact-SQL 语句的游标。

71

Prepare SQL

ODBC、OLE DB 或 DB-Library 已准备好了一个或多个要使用的 Transact-SQL 语句。

72

Exec Prepared SQL

ODBC、OLE DB 或 DB-Library 已执行了一个或多个准备好的 Transact-SQL 语句。

73

Unprepare SQL

ODBC、OLE DB 或 DB-Library 已撤消(删除)了一个或多个准备好的 Transact-SQL 语句。

74

CursorExecute

执行了先前由 ODBC、OLE DB 或 DB-Library 为 Transact-SQL 语句准备的游标。

75

CursorRecompile

由 ODBC 或 DB-Library 为 Transact-SQL 语句打开的游标已直接重新编译或由于架构更改而重新编译。

为 ANSI 和非 ANSI 游标触发。

76

CursorImplicitConversion

SQL Server 将 Transact-SQL 语句的游标从一种类型转换为另一种类型。

为 ANSI 和非 ANSI 游标触发。

77

CursorUnprepare

ODBC、OLE DB 或 DB-Library 撤消(删除)了准备好的 Transact-SQL 语句的游标。

78

CursorClose

关闭了先前由 ODBC、OLE DB 或 DB-Library 为 Transact-SQL 语句打开的游标。

79

Missing Column Statistics

可能曾经对优化器有用的列统计信息不可用。

80

Missing Join Predicate

正在执行没有联接谓词的查询。 这可能导致长时间运行查询。

81

Server Memory Change

SQL Server 内存的使用量已增加或减少了 1 MB 或最大服务器内存的 5%(两者中较大者)。

82-91

User Configurable (0-9)

用户定义的事件数据。

92

Data File Auto Grow

指示服务器已自动扩展了数据文件。

93

Log File Auto Grow

指示服务器已自动扩展了日志文件。

94

Data File Auto Shrink

指示服务器已自动收缩了数据文件。

95

Log File Auto Shrink

指示服务器已自动收缩了日志文件。

96

Showplan Text

显示来自查询优化器的 SQL 语句的查询计划树。 请注意,TextData 列不包含此事件的显示计划。

97

Showplan All

显示查询计划,并显示已执行的 SQL 语句的完整编译时详细信息。 请注意,TextData 列不包含此事件的显示计划。

98

Showplan Statistics Profile

显示查询计划,并显示已执行的 SQL 语句的完整运行时详细信息。 请注意,TextData 列不包含此事件的显示计划。

100

RPC Output Parameter

生成每个 RPC 的参数的输出值。

108

Audit Add Login to Server Role Event

在从固定服务器角色添加或删除登录时发生;针对 sp_addsrvrolemember 和 sp_dropsrvrolemember。

112

Audit App Role Change Password Event

在更改应用程序角色的密码时发生。

113

Audit Statement Permission Event

在使用语句权限(如 CREATE TABLE)时发生。

114

Audit Schema Object Access Event

在成功或未成功使用了对象权限(如 SELECT)时发生。

115

Audit Backup/Restore Event

在发出 BACKUP 或 RESTORE 命令时发生。

116

Audit DBCC Event

在发出 DBCC 命令时发生。

117

Audit Change Audit Event

在修改审核跟踪时发生。

118

Audit Object Derived Permission Event

在发出 CREATE、ALTER 和 DROP 对象命令时发生。

119

OLEDB Call Event

为分布式查询和远程存储过程调用 OLE DB 访问接口时发生。

120

OLEDB QueryInterface Event

为分布式查询和远程存储过程调用 OLE DB QueryInterface 时发生。

121

OLEDB DataRead Event

对 OLE DB 访问接口调用数据请求时发生。

122

Showplan XML

在执行 SQL 语句时发生。 包括该事件可以标识 Showplan 运算符。 每个事件都存储在格式正确的 XML 文档中。 请注意,此事件的 Binary 列包含已编码的显示计划。 使用 SQL Server Profiler 可打开跟踪并查看显示计划。

123

SQL:FullTextQuery

执行全文查询时发生。

124

Broker:Conversation

报告 Service Broker 会话的进度。

125

Deprecation Announcement

使用将从 SQL Server 的未来版本中删除的功能时发生。

126

Deprecation Final Support

使用将从 SQL Server 的下一个主版本中删除的功能时发生。

127

Exchange Spill Event

在 tempdb 数据库临时写入并行查询计划中的通信缓冲区时发生。

128

Audit Database Management Event

创建、更改或删除数据库时发生。

129

Audit Database Object Management Event

对数据库对象(如架构)执行 CREATE、ALTER 或 DROP 语句时发生。

130

Audit Database Principal Management Event

创建、更改或删除数据库的主体(如用户)时发生。

131

Audit Schema Object Management Event

创建、更改或删除服务器对象时发生。

132

Audit Server Principal Impersonation Event

服务器范围中发生模拟(如 EXECUTE AS LOGIN)时发生。

133

Audit Database Principal Impersonation Event

数据库范围中发生模拟(如 EXECUTE AS USER 或 SETUSER)时发生。

134

Audit Server Object Take Ownership Event

服务器范围中的对象的所有者发生更改时发生。

135

Audit Database Object Take Ownership Event

数据库范围中的对象的所有者发生更改时发生。

136

Broker:Conversation Group

Service Broker 创建新的会话组或删除现有会话组时发生。

137

Blocked Process Report

进程被阻塞的时间超过了指定的时间时发生。 不包括系统进程或正在等待未发现死锁的资源的进程。 请使用 sp_configure 来配置生成报表时的阈值和频率。

138

Broker:Connection

报告 Service Broker 管理的传输连接的状态。

139

Broker:Forwarded Message Sent

Service Broker 转发消息时发生。

140

Broker:Forwarded Message Dropped

Service Broker 删除用于转发的消息时发生。

141

Broker:Message Classify

Service Broker 确定消息的路由时发生。

142

Broker:Transmission

指示在 Service Broker 传输层中发生了错误。 错误号和状态值指示了错误源。

143

Broker:Queue Disabled

指示检测到有害消息,这是由于在 Service Broker 队列中有五个连续的事务回滚。 该事件包含数据库 ID 和包含有害消息的队列的队列 ID。

146

Showplan XML Statistics Profile

在执行 SQL 语句时发生。 标识 Showplan 运算符,并显示完整的编译时数据。 请注意,此事件的 Binary 列包含已编码的显示计划。 使用 SQL Server Profiler 可打开跟踪并查看显示计划。

148

Deadlock Graph

取消获取锁的尝试时发生,这是因为该尝试是死锁的一部分,并且被选为死锁牺牲品。 提供死锁的 XML 说明。

149

Broker:Remote Message Acknowledgement

Service Broker 发送或收到消息确认时发生。

150

Trace File Close

跟踪文件在回滚期间关闭时发生。

152

Audit Change Database Owner

使用 ALTER AUTHORIZATION 更改数据库的所有者,并且检查执行该操作的权限时发生。

153

Audit Schema Object Take Ownership Event

使用 ALTER AUTHORIZATION 来将所有者分配给对象,并且检查执行该操作的权限时发生。

155

FT:Crawl Started

全文爬网(填充)开始时发生。 用于检查工作线程任务是否拾取了爬网请求。

156

FT:Crawl Stopped

全文爬网(填充)停止时发生。 爬网成功完成或发生错误时停止。

157

FT:Crawl Aborted

在全文爬网过程中遇到异常时发生。 通常导致全文爬网停止。

158

Audit Broker Conversation

报告与 Service Broker 对话安全性相关的审核消息。

159

Audit Broker Login

报告与 Service Broker 传输安全性相关的审核消息。

160

Broker:Message Undeliverable

Service Broker 无法保留收到的消息时发生,该消息应当已传递给某个服务。

161

Broker:Corrupted Message

Service Broker 收到损坏的消息时发生。

162

User Error Message

显示出现错误或异常时用户看到的错误消息。

163

Broker:Activation

队列监视器启动激活存储过程时,发送 QUEUE_ACTIVATION 通知时,或者队列监视器启动的激活存储过程退出时发生。

164

Object:Altered

数据库对象更改时发生。

165

Performance statistics

将经过编译的查询计划第一次缓存、重新编译或从计划缓存中删除时发生。

166

SQL:StmtRecompile

发生语句级别的重新编译时发生。

167

Database Mirroring State Change

镜像数据库的状态更改时发生。

168

Showplan XML For Query Compile

编译 SQL 语句时发生。 显示完整的编译时数据。 请注意,此事件的 Binary 列包含已编码的显示计划。 使用 SQL Server Profiler 可打开跟踪并查看显示计划。

169

Showplan All For Query Compile

编译 SQL 语句时发生。 显示完整的编译时数据。 用于标识 Showplan 运算符。

170

Audit Server Scope GDR Event

指示在服务器范围中发生了权限的授予、拒绝或撤消事件(如创建登录)。

171

Audit Server Object GDR Event

指示发生了对架构对象(如表或函数)的授予、拒绝或撤消事件。

172

Audit Database Object GDR Event

指示发生了对数据库对象(如程序集和架构)的授予、拒绝或撤消事件。

173

Audit Server Operation Event

使用了安全审核操作(如使用了更改设置、资源、外部访问或授权)时发生。

175

Audit Server Alter Trace Event

检查语句的 ALTER TRACE 权限时发生。

176

Audit Server Object Management Event

创建、更改或删除服务器对象时发生。

177

Audit Server Principal Management Event

创建、更改或删除了服务器主体时发生。

178

Audit Database Operation Event

发生数据库操作(如检查或订阅查询通知)时发生。

180

Audit Database Object Access Event

访问数据库对象(如架构)时发生。

181

TM: Begin Tran starting

BEGIN TRANSACTION 请求开始时发生。

182

TM: Begin Tran completed

BEGIN TRANSACTION 请求完成时发生。

183

TM: Promote Tran starting

PROMOTE TRANSACTION 请求开始时发生。

184

TM: Promote Tran completed

PROMOTE TRANSACTION 请求完成时发生。

185

TM: Commit Tran starting

COMMIT TRANSACTION 请求开始时发生。

186

TM: Commit Tran completed

COMMIT TRANSACTION 请求完成时发生。

187

TM: Rollback Tran starting

ROLLBACK TRANSACTION 请求开始时发生。

188

TM: Rollback Tran completed

ROLLBACK TRANSACTION 请求完成时发生。

189

Lock:Timeout (timeout > 0)

对资源(如页)的锁请求超时时发生。

190

Progress Report: Online Index Operation

报告生成进程正在运行时,联机索引生成操作的进度。

191

TM: Save Tran starting

SAVE TRANSACTION 请求开始时发生。

192

TM: Save Tran completed

SAVE TRANSACTION 请求完成时发生。

193

Background Job Error

后台作业不正常终止时发生。

194

OLEDB Provider Information

分布式查询运行并收集对应于提供程序连接的信息时发生。

195

Mount Tape

收到磁带装入请求时发生。

196

Assembly Load

发生加载 CLR 程序集的请求时发生。

198

XQuery Static Type

执行 XQuery 表达式时发生。 此事件类提供静态类型的 XQuery 表达式。

199

QN: subscription

无法订阅查询注册时发生。 TextData 列包含事件的有关信息。

200

QN: parameter table

有关活动订阅的信息存储在内部参数表中。 在创建或删除参数表时发生该事件类。 通常,重新启动数据库时将创建或删除这些表。 TextData 列包含事件的有关信息。

201

QN: template

查询模板代表订阅查询的类。 通常,除参数值以外,相同类中的查询是相同的。 当新的订阅请求针对已存在的类 (Match)、新类 (Create) 或 Drop 类(指示清除没有活动订阅的查询类的模板)时,发生此事件类。 TextData 列包含事件的有关信息。

202

QN: dynamics

跟踪查询通知的内部活动。 TextData 列包含事件的有关信息。

213

Database Suspect Data Page

指示何时将某页添加到 msdb 的 suspect_pages 表。

214

CPU threshold exceeded

指示资源调控器检测到查询超过 CPU 阈值 (REQUEST_MAX_CPU_TIME_SEC) 的时间。

215

指示 LOGON 触发器或资源调控器分类器函数开始执行的时间。

指示 LOGON 触发器或资源调控器分类器函数开始执行的时间。

216

PreConnect:Completed

指示 LOGON 触发器或资源调控器分类器函数完成执行的时间。

217

Plan Guide Successful

指示 SQL Server 已成功为计划指南中包含的查询或批处理生成执行计划。

218

Plan Guide Unsuccessful

指示 SQL Server 无法为包含计划指南的查询或批处理生成执行计划。 SQL Server 尝试在不应用计划指南的情况下为此查询或批处理生成执行计划。 无效的计划指南可能是导致此问题的原因。 您可以通过使用 sys.fn_validate_plan_guide 系统函数验证该计划指南。

事件号

事件名称

说明

10                 

RPC:Completed

在完成了远程过程调用 (RPC) 时发生。

11

RPC:Starting

在启动了 RPC 时发生。

12

SQL:BatchCompleted

在完成了 Transact-SQL 批处理时发生。

13

SQL:BatchStarting

在启动了 Transact-SQL 批处理时发生。

14

Audit Login

在用户成功登录到 SQL Server 时发生。

15

Audit Logout

在用户从 SQL Server 注销时发生。

16

Attention

在发生需要关注的事件(如客户端中断请求或客户端连接中断)时发生。

17

ExistingConnection

检测在启动跟踪前连接到 SQL Server 的用户的所有活动。

18

Audit Server Starts and Stops

在修改 SQL Server 服务状态时发生。

20

Audit Login Failed

指示试图从客户端登录到 SQL Server 失败。

21

EventLog

指示已将事件记录到 Windows 应用程序日志中。

22

ErrorLog

指示已将错误事件记录到 SQL Server 错误日志中。

23

Lock:Released

指示已释放某个资源(如页)的锁。

24

Lock:Acquired

指示获取了某个资源(如数据页)的锁。

25

Lock:Deadlock

指示两个并发事务由于试图获得对方事务拥有的资源的不兼容锁而发生了相互死锁。

26

Lock:Cancel

指示已取消获取资源锁(例如,由于死锁)。

27

Lock:Timeout

指示由于其他事务持有所需资源的阻塞锁而使对资源(例如页)锁的请求超时。 超时由 @@LOCK_TIMEOUT 函数确定,并可用 SET LOCK_TIMEOUT 语句设置。

28

Degree of Parallelism Event(7.0 插入)

在执行 SELECT、INSERT 或 UPDATE 语句之前发生。

33

Exception

指示 SQL Server 中出现了异常。

34

SP:CacheMiss

指示未在过程缓存中找到某个存储过程。

35

SP:CacheInsert

指示某个项被插入到过程缓存中。

36

SP:CacheRemove

指示从过程缓存中删除了某个项。

37

SP:Recompile

指示已重新编译存储过程。

38

SP:CacheHit

指示在过程缓存中找到了存储过程。

40

SQL:StmtStarting

在启动了 Transact-SQL 语句时发生。

41

SQL:StmtCompleted

在完成了 Transact-SQL 语句时发生。

42

SP:Starting

指示启动了存储过程。

43

SP:Completed

指示完成了存储过程。

44

SP:StmtStarting

指示已开始执行存储过程中的 Transact-SQL 语句。

45

SP:StmtCompleted

指示存储过程中的 Transact-SQL 语句已执行完毕。

46

Object:Created

指示 CREATE INDEX、CREATE TABLE 和 CREATE DATABASE 这样的语句已创建了一个对象。

47

Object:Deleted

指示已在 DROP INDEX 和 DROP TABLE 这样的语句中删除了对象。

50

SQL Transaction

跟踪 Transact-SQL BEGIN、COMMIT、SAVE 和 ROLLBACK TRANSACTION 语句。

51

Scan:Started

指示启动了表或索引扫描

52

Scan:Stopped

指示停止了表或索引扫描。

53

CursorOpen

指示 ODBC、OLE DB 或 DB-Library 在 Transact-SQL 语句中打开了一个游标。

54

TransactionLog

将事务写入事务日志时进行跟踪。

55

Hash Warning

指示未在缓冲分区进行的某一哈希操作(例如,哈希联接、哈希聚合、哈希 union 运算、哈希非重复)已恢复为替换计划。 发生此事件的原因可能是递归深度、数据扭曲、跟踪标记或位计数。

58

Auto Stats

指示发生了自动更新索引统计信息。

59

Lock:Deadlock Chain

为导致死锁的每个事件而生成。

60

Lock:Escalation

指示较细粒度的锁转换成了较粗粒度的锁(例如,页锁升级或转换为 TABLE 或 HoBT 锁)。

61

OLE DB Errors

指示发生了 OLE DB 错误。

67

Execution Warnings

指示在执行 SQL Server 语句或存储过程期间发生的任何警告。

68

Showplan Text (Unencoded)

显示所执行 Transact-SQL 语句的计划树。

69

Sort Warnings

指示不适合内存的排序操作。 不包括与创建索引有关的排序操作;只包括某查询内的排序操作(如 SELECT 语句中使用的 ORDER BY 子句)。

70

CursorPrepare

指示已准备了 ODBC、OLE DB 或 DB-Library 用于 Transact-SQL 语句的游标。

71

Prepare SQL

ODBC、OLE DB 或 DB-Library 已准备好了一个或多个要使用的 Transact-SQL 语句。

72

Exec Prepared SQL

ODBC、OLE DB 或 DB-Library 已执行了一个或多个准备好的 Transact-SQL 语句。

73

Unprepare SQL

ODBC、OLE DB 或 DB-Library 已撤消(删除)了一个或多个准备好的 Transact-SQL 语句。

74

CursorExecute

执行了先前由 ODBC、OLE DB 或 DB-Library 为 Transact-SQL 语句准备的游标。

75

CursorRecompile

由 ODBC 或 DB-Library 为 Transact-SQL 语句打开的游标已直接重新编译或由于架构更改而重新编译。

为 ANSI 和非 ANSI 游标触发。

76

CursorImplicitConversion

SQL Server 将 Transact-SQL 语句的游标从一种类型转换为另一种类型。

为 ANSI 和非 ANSI 游标触发。

77

CursorUnprepare

ODBC、OLE DB 或 DB-Library 撤消(删除)了准备好的 Transact-SQL 语句的游标。

78

CursorClose

关闭了先前由 ODBC、OLE DB 或 DB-Library 为 Transact-SQL 语句打开的游标。

79

Missing Column Statistics

可能曾经对优化器有用的列统计信息不可用。

80

Missing Join Predicate

正在执行没有联接谓词的查询。 这可能导致长时间运行查询。

81

Server Memory Change

SQL Server 内存的使用量已增加或减少了 1 MB 或最大服务器内存的 5%(两者中较大者)。

82-91

User Configurable (0-9)

用户定义的事件数据。

92

Data File Auto Grow

指示服务器已自动扩展了数据文件。

93

Log File Auto Grow

指示服务器已自动扩展了日志文件。

94

Data File Auto Shrink

指示服务器已自动收缩了数据文件。

95

Log File Auto Shrink

指示服务器已自动收缩了日志文件。

96

Showplan Text

显示来自查询优化器的 SQL 语句的查询计划树。 请注意,TextData 列不包含此事件的显示计划。

97

Showplan All

显示查询计划,并显示已执行的 SQL 语句的完整编译时详细信息。 请注意,TextData 列不包含此事件的显示计划。

98

Showplan Statistics Profile

显示查询计划,并显示已执行的 SQL 语句的完整运行时详细信息。 请注意,TextData 列不包含此事件的显示计划。

100

RPC Output Parameter

生成每个 RPC 的参数的输出值。

108

Audit Add Login to Server Role Event

在从固定服务器角色添加或删除登录时发生;针对 sp_addsrvrolemember 和 sp_dropsrvrolemember

112

Audit App Role Change Password Event

在更改应用程序角色的密码时发生。

113

Audit Statement Permission Event

在使用语句权限(如 CREATE TABLE)时发生。

114

Audit Schema Object Access Event

在成功或未成功使用了对象权限(如 SELECT)时发生。

115

Audit Backup/Restore Event

在发出 BACKUP 或 RESTORE 命令时发生。

116

Audit DBCC Event

在发出 DBCC 命令时发生。

117

Audit Change Audit Event

在修改审核跟踪时发生。

118

Audit Object Derived Permission Event

在发出 CREATE、ALTER 和 DROP 对象命令时发生。

119

OLEDB Call Event

为分布式查询和远程存储过程调用 OLE DB 访问接口时发生。

120

OLEDB QueryInterface Event

为分布式查询和远程存储过程调用 OLE DB QueryInterface 时发生。

121

OLEDB DataRead Event

对 OLE DB 访问接口调用数据请求时发生。

122

Showplan XML

在执行 SQL 语句时发生。 包括该事件可以标识 Showplan 运算符。 每个事件都存储在格式正确的 XML 文档中。 请注意,此事件的 Binary 列包含已编码的显示计划。 使用 SQL Server Profiler 可打开跟踪并查看显示计划。

123

SQL:FullTextQuery

执行全文查询时发生。

124

Broker:Conversation

报告 Service Broker 会话的进度。

125

Deprecation Announcement

使用将从 SQL Server 的未来版本中删除的功能时发生。

126

Deprecation Final Support

使用将从 SQL Server 的下一个主版本中删除的功能时发生。

127

Exchange Spill Event

在 tempdb 数据库临时写入并行查询计划中的通信缓冲区时发生。

128

Audit Database Management Event

创建、更改或删除数据库时发生。

129

Audit Database Object Management Event

对数据库对象(如架构)执行 CREATE、ALTER 或 DROP 语句时发生。

130

Audit Database Principal Management Event

创建、更改或删除数据库的主体(如用户)时发生。

131

Audit Schema Object Management Event

创建、更改或删除服务器对象时发生。

132

Audit Server Principal Impersonation Event

服务器范围中发生模拟(如 EXECUTE AS LOGIN)时发生。

133

Audit Database Principal Impersonation Event

数据库范围中发生模拟(如 EXECUTE AS USER 或 SETUSER)时发生。

134

Audit Server Object Take Ownership Event

服务器范围中的对象的所有者发生更改时发生。

135

Audit Database Object Take Ownership Event

数据库范围中的对象的所有者发生更改时发生。

136

Broker:Conversation Group

Service Broker 创建新的会话组或删除现有会话组时发生。

137

Blocked Process Report

进程被阻塞的时间超过了指定的时间时发生。 不包括系统进程或正在等待未发现死锁的资源的进程。 请使用 sp_configure 来配置生成报表时的阈值和频率。

138

Broker:Connection

报告 Service Broker 管理的传输连接的状态。

139

Broker:Forwarded Message Sent

Service Broker 转发消息时发生。

140

Broker:Forwarded Message Dropped

Service Broker 删除用于转发的消息时发生。

141

Broker:Message Classify

Service Broker 确定消息的路由时发生。

142

Broker:Transmission

指示在 Service Broker 传输层中发生了错误。 错误号和状态值指示了错误源。

143

Broker:Queue Disabled

指示检测到有害消息,这是由于在 Service Broker 队列中有五个连续的事务回滚。 该事件包含数据库 ID 和包含有害消息的队列的队列 ID。

146

Showplan XML Statistics Profile

在执行 SQL 语句时发生。 标识 Showplan 运算符,并显示完整的编译时数据。 请注意,此事件的 Binary 列包含已编码的显示计划。 使用 SQL Server Profiler 可打开跟踪并查看显示计划。

148

Deadlock Graph

取消获取锁的尝试时发生,这是因为该尝试是死锁的一部分,并且被选为死锁牺牲品。 提供死锁的 XML 说明。

149

Broker:Remote Message Acknowledgement

Service Broker 发送或收到消息确认时发生。

150

Trace File Close

跟踪文件在回滚期间关闭时发生。

152

Audit Change Database Owner

使用 ALTER AUTHORIZATION 更改数据库的所有者,并且检查执行该操作的权限时发生。

153

Audit Schema Object Take Ownership Event

使用 ALTER AUTHORIZATION 来将所有者分配给对象,并且检查执行该操作的权限时发生。

155

FT:Crawl Started

全文爬网(填充)开始时发生。 用于检查工作线程任务是否拾取了爬网请求。

156

FT:Crawl Stopped

全文爬网(填充)停止时发生。 爬网成功完成或发生错误时停止。

157

FT:Crawl Aborted

在全文爬网过程中遇到异常时发生。 通常导致全文爬网停止。

158

Audit Broker Conversation

报告与 Service Broker 对话安全性相关的审核消息。

159

Audit Broker Login

报告与 Service Broker 传输安全性相关的审核消息。

160

Broker:Message Undeliverable

Service Broker 无法保留收到的消息时发生,该消息应当已传递给某个服务。

161

Broker:Corrupted Message

Service Broker 收到损坏的消息时发生。

162

User Error Message

显示出现错误或异常时用户看到的错误消息。

163

Broker:Activation

队列监视器启动激活存储过程时,发送 QUEUE_ACTIVATION 通知时,或者队列监视器启动的激活存储过程退出时发生。

164

Object:Altered

数据库对象更改时发生。

165

Performance statistics

将经过编译的查询计划第一次缓存、重新编译或从计划缓存中删除时发生。

166

SQL:StmtRecompile

发生语句级别的重新编译时发生。

167

Database Mirroring State Change

镜像数据库的状态更改时发生。

168

Showplan XML For Query Compile

编译 SQL 语句时发生。 显示完整的编译时数据。 请注意,此事件的 Binary 列包含已编码的显示计划。 使用 SQL Server Profiler 可打开跟踪并查看显示计划。

169

Showplan All For Query Compile

编译 SQL 语句时发生。 显示完整的编译时数据。 用于标识 Showplan 运算符。

170

Audit Server Scope GDR Event

指示在服务器范围中发生了权限的授予、拒绝或撤消事件(如创建登录)。

171

Audit Server Object GDR Event

指示发生了对架构对象(如表或函数)的授予、拒绝或撤消事件。

172

Audit Database Object GDR Event

指示发生了对数据库对象(如程序集和架构)的授予、拒绝或撤消事件。

173

Audit Server Operation Event

使用了安全审核操作(如使用了更改设置、资源、外部访问或授权)时发生。

175

Audit Server Alter Trace Event

检查语句的 ALTER TRACE 权限时发生。

176

Audit Server Object Management Event

创建、更改或删除服务器对象时发生。

177

Audit Server Principal Management Event

创建、更改或删除了服务器主体时发生。

178

Audit Database Operation Event

发生数据库操作(如检查或订阅查询通知)时发生。

180

Audit Database Object Access Event

访问数据库对象(如架构)时发生。

181

TM: Begin Tran starting

BEGIN TRANSACTION 请求开始时发生。

182

TM: Begin Tran completed

BEGIN TRANSACTION 请求完成时发生。

183

TM: Promote Tran starting

PROMOTE TRANSACTION 请求开始时发生。

184

TM: Promote Tran completed

PROMOTE TRANSACTION 请求完成时发生。

185

TM: Commit Tran starting

COMMIT TRANSACTION 请求开始时发生。

186

TM: Commit Tran completed

COMMIT TRANSACTION 请求完成时发生。

187

TM: Rollback Tran starting

ROLLBACK TRANSACTION 请求开始时发生。

188

TM: Rollback Tran completed

ROLLBACK TRANSACTION 请求完成时发生。

189

Lock:Timeout (timeout > 0)

对资源(如页)的锁请求超时时发生。

190

Progress Report: Online Index Operation

报告生成进程正在运行时,联机索引生成操作的进度。

191

TM: Save Tran starting

SAVE TRANSACTION 请求开始时发生。

192

TM: Save Tran completed

SAVE TRANSACTION 请求完成时发生。

193

Background Job Error

后台作业不正常终止时发生。

194

OLEDB Provider Information

分布式查询运行并收集对应于提供程序连接的信息时发生。

195

Mount Tape

收到磁带装入请求时发生。

196

Assembly Load

发生加载 CLR 程序集的请求时发生。

198

XQuery Static Type

执行 XQuery 表达式时发生。 此事件类提供静态类型的 XQuery 表达式。

199

QN: subscription

无法订阅查询注册时发生。 TextData 列包含事件的有关信息。

200

QN: parameter table

有关活动订阅的信息存储在内部参数表中。 在创建或删除参数表时发生该事件类。 通常,重新启动数据库时将创建或删除这些表。 TextData 列包含事件的有关信息。

201

QN: template

查询模板代表订阅查询的类。 通常,除参数值以外,相同类中的查询是相同的。 当新的订阅请求针对已存在的类 (Match)、新类 (Create) 或 Drop 类(指示清除没有活动订阅的查询类的模板)时,发生此事件类。 TextData 列包含事件的有关信息。

202

QN: dynamics

跟踪查询通知的内部活动。 TextData 列包含事件的有关信息。

213

Database Suspect Data Page

指示何时将某页添加到 msdb 的 suspect_pages 表。

214

CPU threshold exceeded

指示资源调控器检测到查询超过 CPU 阈值 (REQUEST_MAX_CPU_TIME_SEC) 的时间。

215

指示 LOGON 触发器或资源调控器分类器函数开始执行的时间。

指示 LOGON 触发器或资源调控器分类器函数开始执行的时间。

216

PreConnect:Completed

指示 LOGON 触发器或资源调控器分类器函数完成执行的时间。

217

Plan Guide Successful

指示 SQL Server 已成功为计划指南中包含的查询或批处理生成执行计划。

218

Plan Guide Unsuccessful

指示 SQL Server 无法为包含计划指南的查询或批处理生成执行计划。 SQL Server 尝试在不应用计划指南的情况下为此查询或批处理生成执行计划。 无效的计划指南可能是导致此问题的原因。 您可以通过使用 sys.fn_validate_plan_guide 系统函数验证该计划指南。

661

 

 

Disables the ghost record removal process. A ghost record is the result
of a delete operation. When you delete a record, the deleted record is
kept as a ghost record. Later, the deleted record is purged by the ghost
record removal process. When you disable this process, the deleted
record is not purged. Therefore, the space that the deleted record
consumes is not freed. This behavior affects space consumption and the
performance of scan operations. SCOPE: Global. If you turn off this
trace flag, the ghost record removal process works correctly. KB 920093

上述的跟踪事件中,基本包含了SQL
Server中所能做的任何操作,我们可以根据自己需要进行定义,当我们可以针对日常经常遇到的一些问题进行定位,比如:死锁、等待、登录失败等等吧…当然也可以追踪某个人的所有行为,这里我们来定义几个来看看

上述的跟踪事件中,基本包含了SQL
Server中所能做的任何操作,我们可以根据自己需要进行定义,当我们可以针对日常经常遇到的一些问题进行定位,比如:死锁、等待、登录失败等等吧…当然也可以追踪某个人的所有行为,这里我们来定义几个来看看

698

 

 

SQL 9 – Performance of INSERT operations against a table with an
identity column may be slow when compared to SQL 8.
KB 940545

我们定义追踪所有语句批量操作的追踪,从上面表我们可以查找到为12,13

我们定义追踪所有语句批量操作的追踪,从上面表我们可以查找到为12,13

699

exec sp_trace_setevent 2,12,1,1
exec sp_trace_setevent 2,13,1,1
exec sp_trace_setevent 2,12,1,1
exec sp_trace_setevent 2,13,1,1

Turn off transaction logging for the entire SQL dataserver.

 

 

806

通过如下存储过程,将我们自定的追踪启动

通过如下存储过程,将我们自定的追踪启动

Cause ‘DBCC-style’ page auditing to be performed whenever a database
page is read into the buffer pool. This is useful to catch cases where
pages are being corrupted in memory and then written out to disk with a
new page checksum. When they’re read back in the checksum will look
correct, but the page is corrupt (because of the previous memory
corruption). This page auditing goes someway to catching this –
especially on non-Enterprise Edition systems that don’t have the
‘checksum sniffer’.

--设置跟踪状态以启动
exec sp_trace_setstatus @TraceID,1
--设置跟踪状态以启动
exec sp_trace_setstatus @TraceID,1

809

图片 35

图片 35

SQL 8 – Limits the amount of Lazy write activity.

至此,我们新建的追踪已经开始运行了,我们可以利用上面的方法,来查看我们生成的追踪文件了,其实大部分时候,我们都是利用此种方法设置好”圈套“,等待鱼儿上网

至此,我们新建的追踪已经开始运行了,我们可以利用上面的方法,来查看我们生成的追踪文件了,其实大部分时候,我们都是利用此种方法设置好”圈套“,等待鱼儿上网

815

比如死锁查找,CPU消耗高,IO值高的那些语句….

比如死锁查找,CPU消耗高,IO值高的那些语句….

SQL 8/9 – Enables latch enforcement. SQL Server 8 (with service pack 4)
and SQL Server 9 can perform latch enforcement for data pages found in
the buffer pool cache. Latch enforcement changes the virtual memory
protection state while database page status changes from “clean” to
“dirty” (“dirty” means modified through INSERT, UPDATE or DELETE
operation). If an attempt is made to modify a data page while latch
enforcement is set, it causes an exception and creates a mini-dump in
SQL Server installation’s LOG directory. Microsoft support can examine
the contents of such mini-dump to determine the cause of the exception.
In order to modify the data page the connection must first acquire a
modification latch. Once the data modification latch is acquired the
page protection is changed to read-write. Once the modification latch is
released the page protection changes back to read-only.

图片 37

图片 37

818

 

 

SQL 8 – Enables in memory ring buffer used to track last 2048 successful
write operations.

我们可以利用如下语句,查找跟踪文件的信息

我们可以利用如下语句,查找跟踪文件的信息

828

--查看跟踪文件以表显示
select * from ::fn_trace_gettable('F:\SQLTest\.trc',1)
--查看跟踪文件以表显示
select * from ::fn_trace_gettable('F:\SQLTest\.trc',1)

SQL 8 – When enabled checkpoint ignores the recovery interval target and
keeps steady I/O otherwise it uses recovery interval setting as a target
for the length of time that checkpoint will take – KB 906121.

图片 39

图片 39

830

将我们刚才的所有操作,已经追踪出来了。

将我们刚才的所有操作,已经追踪出来了。

SQL 9 – Disable the reporting of CPU Drift errors in the SQL Server
errorlog like SQL Server has encountered 2 occurrence(s) of I/O requests
taking longer than 15 seconds to complete

 

 

831

通过如下命令进行跟踪的关闭

通过如下命令进行跟踪的关闭

Protect unchanged pages in the buffer pool to catch memory corruptions.

--设置跟踪状态以停止
exec sp_trace_setstatus @TraceID,0
--设置跟踪状态以停止
exec sp_trace_setstatus @TraceID,0

834

通过如下命令进行跟踪的删除

通过如下命令进行跟踪的删除

SQL 8+ – Causes SQL Server to use Windows large-page allocations for the
memory that is allocated for the buffer pool. The page size varies
depending on the hardware platform, but the page size may be from 2 MB
to 16 MB. Large pages are allocated at startup and are kept throughout
the lifetime of the process. Trace flag 834 improves performance by
increasing the efficiency of the translation look-aside buffer (TLB) in
the CPU. Applies only to 64-bit versions & you should have the Lock
pages in memory right granted to turn this on. It may prevent the server
from starting if memory is fragmented and large pages cannot be
allocated. Therefore its better suited dedicated hosts.
Scope STARTUP.

--从系统中移除跟踪
exec sp_trace_setstatus @TraceID,2
--从系统中移除跟踪
exec sp_trace_setstatus @TraceID,2

Details – http://msdn2.microsoft.com/en-us/library/aa366720.aspx

我们知道在SQL
Server默认的跟踪文件在实例重启时候,都会消失,所以我们可以通过如下方法解决,保证在每次实例重新启动的时候都会执行该追踪

我们知道在SQL
Server默认的跟踪文件在实例重启时候,都会消失,所以我们可以通过如下方法解决,保证在每次实例重新启动的时候都会执行该追踪

835

图片 41

图片 42

SQL 9 / 10 – On 64 bit SQL Server it turns off Lock pages in memory.

--新建追踪的存储过程
use master
go
create proc StartBlackBoxTrace
as
begin
    --默认开启追踪所有的SQL 执行语句,文件文件路径为默认
    DECLARE @TraceID int
    DECLARE @MaxFileSize bigint
    SET @MaxFileSize=25
    EXEC SP_TRACE_CREATE
    @TraceID OUTPUT,
    8,
    NULL,
    @MaxFileSize
    EXEC SP_TRACE_SETSTATUS @TraceID,1
END

--将该存储过程设置为SQL Server服务启动时自动启动
EXEC sp_procoption
'StartBlackBoxTrace','STARTUP','ON'
GO
--新建追踪的存储过程
use master
go
create proc StartBlackBoxTrace
as
begin
    --默认开启追踪所有的SQL 执行语句,文件文件路径为默认
    DECLARE @TraceID int
    DECLARE @MaxFileSize bigint
    SET @MaxFileSize=25
    EXEC SP_TRACE_CREATE
    @TraceID OUTPUT,
    8,
    NULL,
    @MaxFileSize
    EXEC SP_TRACE_SETSTATUS @TraceID,1
END

--将该存储过程设置为SQL Server服务启动时自动启动
EXEC sp_procoption
'StartBlackBoxTrace','STARTUP','ON'
GO

836

图片 43

图片 44

Causes SQL Server to size the buffer pool at startup based on the value
of the max server mem option instead of based on the total physical
memory. You can use trace flag 836 to reduce the number of buffer
descriptors that are allocated at startup in 32-bit AWE mode. Scope
Startup.

 

 

840

通过如下脚本删除到所有的跟踪

通过如下脚本删除到所有的跟踪

SQL 9 – When trace turned on, SQL Server can perform larger I/O extent
reads to populate the buffer pool when SQL Server starts this populates
the buffer pool faster. Additionally, the larger I/O extent reads
improve the initial query compilation and the response time when SQL
Server starts. KB 912322

图片 45

图片 46

842

create  proc [dbo].[Performance_Trace_StopAll]  
AS   
declare traceCursor cursor for 
select id from sys.traces where id <> 1   
open traceCursor   
    declare @curid int   
    fetch next from traceCursor 
    into @curid   
    while(@@fetch_status=0)  
    begin          
        exec  sp_trace_setstatus @curid,0  
        exec  sp_trace_setstatus @curid,2   
        fetch next from traceCursor into @curid   
    end   
close traceCursor   
deallocate traceCursor 
create  proc [dbo].[Performance_Trace_StopAll]  
AS   
declare traceCursor cursor for 
select id from sys.traces where id <> 1   
open traceCursor   
    declare @curid int   
    fetch next from traceCursor 
    into @curid   
    while(@@fetch_status=0)  
    begin          
        exec  sp_trace_setstatus @curid,0  
        exec  sp_trace_setstatus @curid,2   
        fetch next from traceCursor into @curid   
    end   
close traceCursor   
deallocate traceCursor 

Use sys.dm_os_memory_node_access_stats to verify local vs. foreign
memory under NUMA configurations after turning on this flag.

图片 47

图片 48

845

 

 

SQL 9 / 10 – On 64 bit SQL Server non ENT. This turns on Lock pages in
memory.  Startup time of SQL Server takes longer because SQL Server
allocates all memory up to the Max Server Memory setting. Scope Startup
KB 970070.

三.死锁案例(2014年11月23日晚补充)

**三.死锁案例(2014年11月23日晚补充)**

902

这里我们来利用自己新建跟踪来跟踪一个死锁的发生过程,并且将其记录到我们的Trace文件中,这里我们来制作一个死锁

这里我们来利用自己新建跟踪来跟踪一个死锁的发生过程,并且将其记录到我们的Trace文件中,这里我们来制作一个死锁

SQL server may not start after (un) install of updates. When sql server
holds at the script upgrade mode. KB 2163980. Mode examples details at

图片 49图片 50

图片 49图片 50

以上代码参照院子里大牛宋沄剑,这里我们利用系统的自带的profile进行设计追踪,我们直接选择系统自带的死锁模板,进行追踪

以上代码参照院子里大牛宋沄剑,这里我们利用系统的自带的profile进行设计追踪,我们直接选择系统自带的死锁模板,进行追踪

&

图片 53

图片 53

然后设置,默认的SPID为大于等于50,小于50的为系统自有事件

然后设置,默认的SPID为大于等于50,小于50的为系统自有事件

1106

图片 55

图片 55

SQL 9 – Used space in tempdb increases continuously when you run a query
that creates internal objects in tempdb. KB 947204.

然后,我们利用上面的死锁脚本,运行获取死锁的捕捉

然后,我们利用上面的死锁脚本,运行获取死锁的捕捉

1117

图片 57

图片 57

Grows all data files at once, else it goes in turns.

可以看到,我们已经顺利的追踪到这个死锁。我们知道这种追踪是高成本的,并且我们有时候不知道死锁发生的具体时间,所以不能一直开着这个Profile,出于性能考虑也不建议这么做,所以我们采用新建的Trace文件,来保存改脚本,然后重定向到我们自己的文件夹,将死锁的信息放置到该文件夹下,提供更大灵活性。

可以看到,我们已经顺利的追踪到这个死锁。我们知道这种追踪是高成本的,并且我们有时候不知道死锁发生的具体时间,所以不能一直开着这个Profile,出于性能考虑也不建议这么做,所以我们采用新建的Trace文件,来保存改脚本,然后重定向到我们自己的文件夹,将死锁的信息放置到该文件夹下,提供更大灵活性。

1118

SQL
Server本身自带的Profile工具就提供编辑脚本的功能,我们将上面的设计,导出成Trace脚本,我们点击“文件”,导出该设计脚本

SQL
Server本身自带的Profile工具就提供编辑脚本的功能,我们将上面的设计,导出成Trace脚本,我们点击“文件”,导出该设计脚本

Switches allocations in tempDB from 1pg at a time (for first 8 pages) to
one extent. There is now a cache of temp tables. When a new temp table
is created on a cold system it uses the same mechanism as for SQL 8.
When it is dropped though, instead of all the pages being deallocated
completely, one IAM page & one data page are left allocated, then the
temp table is put into a special cache. Subsequent temp table creations
will look in the cache to see if they can just grab a pre-created temp
table. If so, this avoids accessing the allocation bitmaps completely.
The temp table cache isn’t huge (32 tables), but this can still lead to
a big drop in latch contention in
tempdb.

图片 59

图片 59

1119

将该脚本保存到一个位置,然后我们打开,我顺便将默认的文件路径添加上

将该脚本保存到一个位置,然后我们打开,我顺便将默认的文件路径添加上

Turns off mixed extent allocation.

图片 61

图片 62

1140

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 2014/11/23  20:28:11         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
--可以更改文件大小
set @maxfilesize = 5 

--默认死锁文件放置目录
declare @FilePath nvarchar(max)
set @FilePath=N'F:\SQLTest\DeadLock.trc'
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, @FilePath, @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 32, @on
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
exec sp_trace_setevent @TraceID, 137, 22, @on
exec sp_trace_setevent @TraceID, 148, 11, @on
exec sp_trace_setevent @TraceID, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 26, 15, @on
exec sp_trace_setevent @TraceID, 26, 32, @on
exec sp_trace_setevent @TraceID, 26, 1, @on
exec sp_trace_setevent @TraceID, 26, 9, @on
exec sp_trace_setevent @TraceID, 26, 57, @on
exec sp_trace_setevent @TraceID, 26, 2, @on
exec sp_trace_setevent @TraceID, 26, 10, @on
exec sp_trace_setevent @TraceID, 26, 11, @on
exec sp_trace_setevent @TraceID, 26, 35, @on
exec sp_trace_setevent @TraceID, 26, 12, @on
exec sp_trace_setevent @TraceID, 26, 13, @on
exec sp_trace_setevent @TraceID, 26, 6, @on
exec sp_trace_setevent @TraceID, 26, 14, @on
exec sp_trace_setevent @TraceID, 26, 22, @on
exec sp_trace_setevent @TraceID, 25, 15, @on
exec sp_trace_setevent @TraceID, 25, 32, @on
exec sp_trace_setevent @TraceID, 25, 1, @on
exec sp_trace_setevent @TraceID, 25, 9, @on
exec sp_trace_setevent @TraceID, 25, 57, @on
exec sp_trace_setevent @TraceID, 25, 2, @on
exec sp_trace_setevent @TraceID, 25, 10, @on
exec sp_trace_setevent @TraceID, 25, 11, @on
exec sp_trace_setevent @TraceID, 25, 35, @on
exec sp_trace_setevent @TraceID, 25, 12, @on
exec sp_trace_setevent @TraceID, 25, 13, @on
exec sp_trace_setevent @TraceID, 25, 6, @on
exec sp_trace_setevent @TraceID, 25, 14, @on
exec sp_trace_setevent @TraceID, 25, 22, @on
exec sp_trace_setevent @TraceID, 59, 32, @on
exec sp_trace_setevent @TraceID, 59, 1, @on
exec sp_trace_setevent @TraceID, 59, 57, @on
exec sp_trace_setevent @TraceID, 59, 2, @on
exec sp_trace_setevent @TraceID, 59, 14, @on
exec sp_trace_setevent @TraceID, 59, 22, @on
exec sp_trace_setevent @TraceID, 59, 35, @on
exec sp_trace_setevent @TraceID, 59, 12, @on
exec sp_trace_setevent @TraceID, 60, 32, @on
exec sp_trace_setevent @TraceID, 60, 9, @on
exec sp_trace_setevent @TraceID, 60, 57, @on
exec sp_trace_setevent @TraceID, 60, 10, @on
exec sp_trace_setevent @TraceID, 60, 11, @on
exec sp_trace_setevent @TraceID, 60, 35, @on
exec sp_trace_setevent @TraceID, 60, 12, @on
exec sp_trace_setevent @TraceID, 60, 6, @on
exec sp_trace_setevent @TraceID, 60, 14, @on
exec sp_trace_setevent @TraceID, 60, 22, @on
exec sp_trace_setevent @TraceID, 189, 15, @on
exec sp_trace_setevent @TraceID, 189, 32, @on
exec sp_trace_setevent @TraceID, 189, 1, @on
exec sp_trace_setevent @TraceID, 189, 9, @on
exec sp_trace_setevent @TraceID, 189, 57, @on
exec sp_trace_setevent @TraceID, 189, 2, @on
exec sp_trace_setevent @TraceID, 189, 10, @on
exec sp_trace_setevent @TraceID, 189, 11, @on
exec sp_trace_setevent @TraceID, 189, 35, @on
exec sp_trace_setevent @TraceID, 189, 12, @on
exec sp_trace_setevent @TraceID, 189, 13, @on
exec sp_trace_setevent @TraceID, 189, 6, @on
exec sp_trace_setevent @TraceID, 189, 14, @on
exec sp_trace_setevent @TraceID, 189, 22, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 22, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 44, 1, @on
exec sp_trace_setevent @TraceID, 44, 9, @on
exec sp_trace_setevent @TraceID, 44, 10, @on
exec sp_trace_setevent @TraceID, 44, 11, @on
exec sp_trace_setevent @TraceID, 44, 35, @on
exec sp_trace_setevent @TraceID, 44, 12, @on
exec sp_trace_setevent @TraceID, 44, 6, @on
exec sp_trace_setevent @TraceID, 44, 14, @on
exec sp_trace_setevent @TraceID, 44, 22, @on
exec sp_trace_setevent @TraceID, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 9, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 9, @on
exec sp_trace_setevent @TraceID, 40, 6, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 35, @on
exec sp_trace_setevent @TraceID, 40, 12, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - efbc9d24-69cd-465f-8daf-e38493da0332'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go
/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 2014/11/23  20:28:11         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
--可以更改文件大小
set @maxfilesize = 5 

--默认死锁文件放置目录
declare @FilePath nvarchar(max)
set @FilePath=N'F:\SQLTest\DeadLock.trc'
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, @FilePath, @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 32, @on
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
exec sp_trace_setevent @TraceID, 137, 22, @on
exec sp_trace_setevent @TraceID, 148, 11, @on
exec sp_trace_setevent @TraceID, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 26, 15, @on
exec sp_trace_setevent @TraceID, 26, 32, @on
exec sp_trace_setevent @TraceID, 26, 1, @on
exec sp_trace_setevent @TraceID, 26, 9, @on
exec sp_trace_setevent @TraceID, 26, 57, @on
exec sp_trace_setevent @TraceID, 26, 2, @on
exec sp_trace_setevent @TraceID, 26, 10, @on
exec sp_trace_setevent @TraceID, 26, 11, @on
exec sp_trace_setevent @TraceID, 26, 35, @on
exec sp_trace_setevent @TraceID, 26, 12, @on
exec sp_trace_setevent @TraceID, 26, 13, @on
exec sp_trace_setevent @TraceID, 26, 6, @on
exec sp_trace_setevent @TraceID, 26, 14, @on
exec sp_trace_setevent @TraceID, 26, 22, @on
exec sp_trace_setevent @TraceID, 25, 15, @on
exec sp_trace_setevent @TraceID, 25, 32, @on
exec sp_trace_setevent @TraceID, 25, 1, @on
exec sp_trace_setevent @TraceID, 25, 9, @on
exec sp_trace_setevent @TraceID, 25, 57, @on
exec sp_trace_setevent @TraceID, 25, 2, @on
exec sp_trace_setevent @TraceID, 25, 10, @on
exec sp_trace_setevent @TraceID, 25, 11, @on
exec sp_trace_setevent @TraceID, 25, 35, @on
exec sp_trace_setevent @TraceID, 25, 12, @on
exec sp_trace_setevent @TraceID, 25, 13, @on
exec sp_trace_setevent @TraceID, 25, 6, @on
exec sp_trace_setevent @TraceID, 25, 14, @on
exec sp_trace_setevent @TraceID, 25, 22, @on
exec sp_trace_setevent @TraceID, 59, 32, @on
exec sp_trace_setevent @TraceID, 59, 1, @on
exec sp_trace_setevent @TraceID, 59, 57, @on
exec sp_trace_setevent @TraceID, 59, 2, @on
exec sp_trace_setevent @TraceID, 59, 14, @on
exec sp_trace_setevent @TraceID, 59, 22, @on
exec sp_trace_setevent @TraceID, 59, 35, @on
exec sp_trace_setevent @TraceID, 59, 12, @on
exec sp_trace_setevent @TraceID, 60, 32, @on
exec sp_trace_setevent @TraceID, 60, 9, @on
exec sp_trace_setevent @TraceID, 60, 57, @on
exec sp_trace_setevent @TraceID, 60, 10, @on
exec sp_trace_setevent @TraceID, 60, 11, @on
exec sp_trace_setevent @TraceID, 60, 35, @on
exec sp_trace_setevent @TraceID, 60, 12, @on
exec sp_trace_setevent @TraceID, 60, 6, @on
exec sp_trace_setevent @TraceID, 60, 14, @on
exec sp_trace_setevent @TraceID, 60, 22, @on
exec sp_trace_setevent @TraceID, 189, 15, @on
exec sp_trace_setevent @TraceID, 189, 32, @on
exec sp_trace_setevent @TraceID, 189, 1, @on
exec sp_trace_setevent @TraceID, 189, 9, @on
exec sp_trace_setevent @TraceID, 189, 57, @on
exec sp_trace_setevent @TraceID, 189, 2, @on
exec sp_trace_setevent @TraceID, 189, 10, @on
exec sp_trace_setevent @TraceID, 189, 11, @on
exec sp_trace_setevent @TraceID, 189, 35, @on
exec sp_trace_setevent @TraceID, 189, 12, @on
exec sp_trace_setevent @TraceID, 189, 13, @on
exec sp_trace_setevent @TraceID, 189, 6, @on
exec sp_trace_setevent @TraceID, 189, 14, @on
exec sp_trace_setevent @TraceID, 189, 22, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 22, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 44, 1, @on
exec sp_trace_setevent @TraceID, 44, 9, @on
exec sp_trace_setevent @TraceID, 44, 10, @on
exec sp_trace_setevent @TraceID, 44, 11, @on
exec sp_trace_setevent @TraceID, 44, 35, @on
exec sp_trace_setevent @TraceID, 44, 12, @on
exec sp_trace_setevent @TraceID, 44, 6, @on
exec sp_trace_setevent @TraceID, 44, 14, @on
exec sp_trace_setevent @TraceID, 44, 22, @on
exec sp_trace_setevent @TraceID, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 9, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 9, @on
exec sp_trace_setevent @TraceID, 40, 6, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 35, @on
exec sp_trace_setevent @TraceID, 40, 12, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - efbc9d24-69cd-465f-8daf-e38493da0332'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

Continuous tempdb growth after upgrading SQL 2005 pre-SP2 to SP3/SP4 or
to SQL 2008 or newer
KB Article:
First Added: SQL 2005 SP2 build 3166
Versions: SQL 2005 SP3+, SQL 2008, SQL 2008 R2, SQL 2012

图片 63

图片 64

1180

我们只需要将这个脚本运行就可以,当然此段脚本,在实例重启的时候,所有的trace都会删除掉,可以将该段代码改成存储过程,然后设置成实例启动的时候运行,

我们只需要将这个脚本运行就可以,当然此段脚本,在实例重启的时候,所有的trace都会删除掉,可以将该段代码改成存储过程,然后设置成实例启动的时候运行,

SQL 7 – Forces allocation to use free pages for text or image data and
maintain efficiency of storage. 1197 applies only in the case of SQL 7 –
SP3. Helpful in case when DBCC SHRINKFILE and SHRINKDATABASE commands
may not work because of sparsely populated text, ntext, or image columns

方法参考本篇的上半部分。

方法参考本篇的上半部分。

1197

当然我们也可以设置别的参数,比如启动时间,运行时间,跟踪文件大小,位置,数量等,删除不必要的trace跟踪事件…

当然我们也可以设置别的参数,比如启动时间,运行时间,跟踪文件大小,位置,数量等,删除不必要的trace跟踪事件…

1200

当运行到一段时间之后,我们直接拷贝下来,找台电脑分析就可以了。

当运行到一段时间之后,我们直接拷贝下来,找台电脑分析就可以了。

Prints detailed lock information as every request for a lock is made
(the process ID and type of lock requested).

更灵活的方式是采取非业务高峰期,利用SQL
Server自带的邮件提醒功能,直接检测出问题,然后Send Email….

更灵活的方式是采取非业务高峰期,利用SQL
Server自带的邮件提醒功能,直接检测出问题,然后Send Email….

1202

 

 

Insert blocked lock requests into syslocks.

四.SQL Server中黑匣子(2014年11月28日晚补充)

**四.SQL Server中黑匣子(2014年11月28日晚补充)**

1204

黑匣子作为飞机出现事故后的追踪利器,在微软的SQL
Server这个数据库中默认也给装上了此引擎,但是没有开启,此功能可能帮助我们诊断间歇性的服务器崩溃。它比我们上面介绍的默认开启的跟踪跟踪的信息更全,跟踪更大一些。其内容包含了:“SP:启动”、“SQL:批处理启动”、异常和注意等事件

黑匣子作为飞机出现事故后的追踪利器,在微软的SQL
Server这个数据库中默认也给装上了此引擎,但是没有开启,此功能可能帮助我们诊断间歇性的服务器崩溃。它比我们上面介绍的默认开启的跟踪跟踪的信息更全,跟踪更大一些。其内容包含了:“SP:启动”、“SQL:批处理启动”、异常和注意等事件

Returns resources and types of locks participating in a deadlock and
command affected. Scope: global

这个跟踪通过在sp_trace_create的默认@option参数设置为8来配置的。代码如下:

这个跟踪通过在sp_trace_create的默认@option参数设置为8来配置的。代码如下:

1205

DECLARE @Traced INT
EXEC sp_trace_create
@Traced OUTPUT,
@options=8
EXEC sp_trace_setstatus @Traced,1
DECLARE @Traced INT
EXEC sp_trace_create
@Traced OUTPUT,
@options=8
EXEC sp_trace_setstatus @Traced,1

More detailed information about the command being executed at the time
of a deadlock. Documented in SQL 7 BOL.

通过以上的配置会自动配置成两个滚动文件,当达到默认的最大文件容量5MB的时候,就在两个文件中循环滚动依次更新。

通过以上的配置会自动配置成两个滚动文件,当达到默认的最大文件容量5MB的时候,就在两个文件中循环滚动依次更新。

1206

当然如果感觉生成的文件5MB有点小,可以手动配置更改大小,或者自定义文件路径,这些都是允许自定义设置的。

当然如果感觉生成的文件5MB有点小,可以手动配置更改大小,或者自定义文件路径,这些都是允许自定义设置的。

Used to complement flag 1204 by displaying other locks held by deadlock
parties.

图片 65

图片 66

1211

DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize=25
DECLARE @tracefile nvarchar(245)
SET @tracefile=N'F:\SQLTest\TRACE\MYTRACE.trc'
EXEC sp_trace_create
@TraceID OUTPUT,
@options=8,
@tracefile=NULL,
@maxfilesize=@maxfilesize
EXEC sp_trace_setstatus @TraceID,1
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize=25
DECLARE @tracefile nvarchar(245)
SET @tracefile=N'F:\SQLTest\TRACE\MYTRACE.trc'
EXEC sp_trace_create
@TraceID OUTPUT,
@options=8,
@tracefile=NULL,
@maxfilesize=@maxfilesize
EXEC sp_trace_setstatus @TraceID,1

Disables lock escalation based on memory pressure, or based on number of
locks. The SQL Server Database Engine will not escalate row or page
locks to table locks.

图片 67

图片 68

Using this trace flag can generate excessive numbers of locks. This can
slow the performance of the Database Engine, or cause 1204 errors
(unable to allocate lock resource) because of insufficient memory. For
more information, see Lock Escalation (Database
Engine).

此方法能将数据库执行的所有SQL滚动记录下来,以防止SQL
Server宕机之后的事故查找。

此方法能将数据库执行的所有SQL滚动记录下来,以防止SQL
Server宕机之后的事故查找。

If both trace flag 1211 and 1224 are set, 1211 takes precedence over

图片 69

图片 69

  1. However, because trace flag 1211 prevents escalation in every
    case, even under memory pressure, we recommend that you use 1224. This
    helps avoid “out-of-locks” errors when many locks are being
    used. Scope: global or session

参考我博文的上面介绍,只需要将这个方法存储于存储过程,然后设置成实例启动后运行,那么恭喜你的SQL
Server已经成功装上了黑匣子引擎,假如有一天突然宕机,我们只需要打开此黑匣子就可以。

参考我博文的上面介绍,只需要将这个方法存储于存储过程,然后设置成实例启动后运行,那么恭喜你的SQL
Server已经成功装上了黑匣子引擎,假如有一天突然宕机,我们只需要打开此黑匣子就可以。

1216

结语

SQL 7 – Disables Health reporting. Lock monitor when detects a (worker
thread) resource level blocking scenario. If a SPID that owns a lock is
currently queued to the scheduler, because all the assigned worker
threads have been created and all the assigned worker threads are in an
un-resolvable wait state, the following error message is written to the
SQL Server error log:

参考文献有下面

Error 1223: Process ID %d:%d cannot acquire lock “%s” on resource %s
because a potential deadlock exists on Scheduler %d for the resource.
Process ID %d:% d holds a lock “%h” on this resource.

SQL Server 默认跟踪(Default
Trace)

1222

使用SQL Trace来实现SQL
Server的跟踪操作

Returns the resources & types of locks that are participating in a
deadlock and also the current command affected, in an XML format that
does not comply with any XSD schema. Scope: global

SQL Server 2005 – Default Trace
(默认跟踪)

1224

转载

Disables lock escalation based on the number of locks. However, memory
pressure can still activate lock escalation (when > 40%). The
Database Engine escalates row or page locks to table (or partition)
locks if the amount of memory used by lock objects exceeds one of the
following conditions:

      

  • 40% of the memory that is used by Db Engine, exclusive of memory
    allocation using AWE. This is applicable when
    the locks parameter ofsp_configure is set to 0.
  • Forty percent of the lock memory that is configured by using
    the locks parameter of sp_configure.

If both trace flag 1211 & 1224 are set, 1211 takes precedence. However,
because trace flag 1211 prevents escalation in every case, even under
memory pressure, it’s recommend to use 1224 which helps avoid
“out-of-locks” errors when many locks are being used.

Note: Lock escalation to the table- or HoBT-level granularity can
also be controlled by using the LOCK_ESCALATION option of the ALTER
TABLEstatement.  Scope: global
/ session

1261

SQL 8 – Disables Health reporting. Lock monitor when detects a (worker
thread) resource level blocking scenario. If a SPID that owns a lock is
currently queued to the scheduler, because all the assigned worker
threads have been created and all the assigned worker threads are in an
un-resolvable wait state, the following error message is written to the
SQL Server error log: Error 1229: Process ID %d:%d owns resources that
are blocking processes on scheduler %d.

1400

SQL 9 RTM – Enables creation of database mirroring endpoint, which is
required for setting up and using database mirroring. Scope: startup

1448

SQL 9/10 – When the principal database is running exposed or is isolated
the Log Reader Agent will waits for log records to harden on the mirror
before replicating them to the Distributor. When publisher is started
with trace flag 1448, the Log Reader Agent can continue replicating
changes regardless of the mirroring state. Scope – GLOBAL. KB 937041

1462

SQL 10 – Turns off log stream compression and effectively reverts the
behavior back to ver 9.

1603

Use standard disk I/O (i.e. turn off asynchronous I/O).

1604

Once enabled at start up makes SQL Server output information regarding
memory allocation requests.

1609

Turns on the unpacking and checking of RPC information in Open Data
Services. Used only when applications depend on the old behavior.

1610

Boot the SQL dataserver with TCP_NODELAY enabled.

1611

If possible, pin shared memory — check errorlog for success/failure.

1613

Set affinity of the SQL data server engine’s onto particular CPUs —
usually pins engine 0 to processor 0, engine 1 to processor 1…

1704

Prints information when a temporary table is created or dropped.

1717

Causes new objects being created to be system objects.

1802

SQL 9 – After detaching a database that resides on network-attached
storage, you cannot reattach the SQL Server database. Scope: STARTUP –
KB 922804

1806

Disables instant file initialization.

1807

SQL 9/10 – Allows creating a database file on a mapped or UNC network
location. Not required with SQL 2008 R2.

1903

SQL 8 – When you capture a SQL Profiler trace in a file and then you try
to import the trace files into tables by using
the fn_trace_gettablefunction no rows may be returned.
KB 911678

1905

 

2301

Makes your optimizer work harder by enabling advanced optimizations that
are specific to decision support queries, applies to processing of large
data sets.

2328

SQL 9+ – Makes cardinality estimates upon resulting selectivity.  The
reasoning for this is that one or more of the constants may be statement
parameters, which would change from one execution of the statement to
the next.

2330

Stops the collection of statistics for sys.db_index_usage_stats.

2335

SQL 9+ Amount of memory available to SQL Server affects the execution
plan generated though SQL Server generates the most optimal plan based
on this value, but occasionally it may generate an inefficient plan for
a specific query when you configure a large value for max server
memory
. Using 2335 as a startup parameter will cause SQL Server to
generate a plan that is more conservative in terms of memory consumption
when executing the query. It does not limit how much memory SQL Server
can use. The memory configured for SQL Server will still be used by data
cache, query execution & other consumers. KB 2413549.

2340

SQL 9/10 – Query processor may introduce a sort operation for
optimization, though not required where the particular plan only
touches a smaller number of rows. Setup cost for the sort operation may
outweigh its benefits thus resulting in poor performance.
KB 2009160

2371

SQL 10.5 SP1 – Before this automatic statistics were triggered when a
column would get modifications exceeding 20% of the # of rows in the
table. On enabling this flag the standard 20% changes to a dynamic value
if table has more than 25000 rows & reduces as the count increases.

2382

SQL 8 – Statistics collected for system tables.

2388

SQL 9 – Detect when the leading column of a statistics object is
ascending and mark or brand it as ascending. Statistics object that
belong to an ascending column is branded as “ascending” after three
updates on the statistics. It’s necessary to update it with ascending
column values so that when the third update occurs, SQL Server brands
the statistics object as ascending. Flag 2388 helps to check the
statistics’ brand, when turned on the result of the DBCC
SHOW_STATISTICS, has an additional column called Leading column
type
 with the brand of the column.

2389

SQL 9 – Tracks nature of columns by subsequent statistics updates. When
SQL Server determines that the statistics increase three times, the
column is branded ascending. The statistics will be updated
automatically at query compile.

2390

SQL 9 – Does the same like 2389 even if ascending nature of the column
is not known and — never enable without 2389.

2440

SQL 10 – Parallel query execution strategy on partitioned tables. SQL 9
used single thread per partition parallel query execution strategy. In
SQL 10, multiple threads can be allocated to a single partition by
turning on this flag.

2470

Slow performance when an AFTER trigger runs on a partitioned table in
SQL Server 2008 R2 or in SQL Server 2012
KB Article:
First Added: SQL 2008 R2 SP1 CU3 or SQL 2012 CU1
Versions: SQL 2008 R2, SQL 2012

2505

SQL 7 – Prevents DBCC TRACEON 208, SPID 10 errors from appearing in the
error log. KB 243352

2508

Disables parallel non-clustered index checking for DBCC CHECKTABLE.

2509

Used with DBCC CHECKTABLE to see the total count of forward records in a
table

2514

Used with DBCC CHECKTABLE to see the total count of ghost records in a
table

2520

Forces DBCC HELP to return syntax of undocumented DBCC statements. If
2520 is not turned on, DBCC HELP will refuse to give you the syntax
stating: “No help available for DBCC statement ‘undocumented
statement'”. dbcc help (‘?’)

2521

SQL 7 SP2 – Facilitates capturing a Sqlservr.exe user-mode crash dump
for postmortem analysis.

2528

SQL 8+ Disables parallel checking of objects by DBCC CHECKDB,
CHECKFILEGROUP and CHECKTABLE. By default, the degree of parallelism is
automatically determined by the query processor. The maximum degree of
parallelism is configured just like that of parallel queries. For more
information, see max degree of parallelism
Option.

Parallel DBCC should typically be left enabled. For DBCC CHECKDB, the
query processor reevaluates and automatically adjusts parallelism with
each table or batch of tables checked. Sometimes, checking may start
when the server is almost idle. An administrator who knows that the load
will increase before checking is complete may want to manually decrease
or disable parallelism. Disabling parallel checking of DBCC can cause it
to take much longer to complete and if DBCC is run with the TABLOCK
feature enabled and parallelism set off, tables may be locked for longer
periods of
time.  Scope:
global / session

2537

SQL 9/10 – Allows function ::fn_dblog to look inside all logs (not just
the active log).

2542

SQL 8 – Used with Sqldumper.exe to get certain dumps. In range 254x –
255x.

2544

Creates a full memory dump.

2546

All threads in SQL Server process are dumped (mini dump).

2549

SQL 10.5 – DBCC CHECKDB builds an internal list of pages to read per
unique disk drive across all database files. This logic determines
unique disk drives based on the drive letter of the physical file name
of each file. If the underlying disks are actually unique when the drive
letters or not, the DBCC CHECKDB command would treat these as one disk.
When this trace flag is enabled, each database file is assumed to be on
a unique disk drive. Do not use this trace flag unless you know that
each file is based on a unique physical disk. KB 2634571, Scope – Any

2551

SQL 9 – Creates a full filtered dump which is additional information for
the dump file.

2562

SQL 10.5 – As an effect the space requirements for tempdb may increase
as much as 5% or more of the user database that is being processed.
Therefore, it’s recommend to pre-size tempdb to at least 5% of scanned
database.

When enabled it runs the DBCC CHECKDB command in a single “batch”
regardless of the number of indexes in the database. By default, the
DBCC CHECKDB command tries to minimize tempdb resources by limiting the
number of indexes or “facts” that it generates by using a “batches”
concept. This trace flag forces all processing into one batch.

This flag improves the internal processing for determining which pages
to read from the database. This reduces the contention on the
DBCC_MULTIOBJECT_SCANNER latch. KB 2634571, Scope – Any

2566

SQL 9 – DBCC CHECKDB takes longer to run on an x64-based computer
compared to a 32-bit computer. Kb – 945770

2701

SQL 6.5 – Sets the @@ERROR system function to 50000 for RAISERROR
messages with severity levels of 10 or less. When disabled, sets the
@@ERROR system function to 0 for RAISERROR messages with severity levels
of 10 or less.

2861

SQL 8 – Cache query plans for queries that have a cost of zero or near
to zero. When turned on, fn_get_sql function can return the SQL
text for activities that have zero cost plans kb
325607 Tip: Avoid Using Trace
Flag 2861 to Cache Zero-Cost Query
Plans

3001

Stops sending backup entries into MSDB.

3004

Shows information about backups and file creations use with 3605 to
direct to error log.

3014

Provides more information related backups / file
streams. 

3023

How to enable the CHECKSUM option if backup utilities do not expose the
option
KB Article:
First Added: SQL 2005
Versions: SQL 2005, SQL 2008, SQL 2008 R2, SQL 2012

3028

 

3031

SQL 9 – Will turn the NO_LOG and TRUNCATE_ONLY options into
checkpoints in all recovery modes. Read
more:

3042

SQL 10+ Prevents pre-allocation of space while running compressed
backups. kb2001026

3104

Causes SQL Server to bypass checking for free space.

3101

Slow performance when you restore a database in SQL Server 2008 R2, in
SQL Server 2008 or in SQL Server 2012 if CDC is enabled
KB Article:
First Added: SQL 2008 SP3 CU5, SQL 2008 R2 CU10, SQL 2012 CU2
Versions: SQL 2008+

3106

Required to move sys databases.

3111

Cause LogMgr::ValidateBackedupBlock to be skipped during backup and
restore operations.

3117

SQL 9 – SQL Server 2005 tries to restore the log files and the data
files in a single step which some third-party snapshot backup utilities
do not support. Turing on 3117 does things the SQL 8 way multiple-step
restore process. KB 915385

  • Restore the file or the file group from the full database backup.
    The database remains in a restoring state.
  • Restore the transaction log or logs from the log backup chain.

3205

If a tape drive supports hardware compression, either the DUMP or BACKUP
statement uses it. With this trace flag, you can disable hardware
compression for tape drivers. This is useful when you want to exchange
tapes with other sites or tape drives that do not support
compression.Scope: global / session

3213

Trace SQL Server activity during backup process so that we will come to
know which part of backup process is taking more time.

3222

Disables the read ahead that is used by
the recovery operation
during roll forward operations.

3226

Suppress BACKUP COMPLETED log entries going to WIN and SQL
logs. Scope: global

3228

 

3231

SQL 8/9 – Will turn the NO_LOG and TRUNCATE_ONLY options into no-ops
in FULL/BULK_LOGGED recovery mode, and will clear the log in SIMPLE
recovery mode. When set, BACKUP LOG with TRUNCATE_ONLY and BACKUP LOG
with NO_LOG do not allow a log backup to run if the database’s recovery
model is FULL or BULK_LOGGED. Read
more: 

3282

SQL 6.5 – Used after backup restoration fails. KB Q215458

3422

Cause auditing of transaction log records as they’re read (during
transaction rollback or log recovery). This is useful because there is
no equivalent to page checksums for transaction log records and so no
way to detect whether log records are being corrupted e careful with
these trace flags – I don’t recommend using them unless you are
experiencing corruptions that you can’t diagnose. Turning them on will
cause a big CPU hit because of the extra auditing that’s happening.

3502

Tracks CHECKPOINT – Prints a message to the log at the start and end of
each checkpoint.

3503

Indicates whether the checkpoint at the end of automatic recovery was
skipped for a database (this applies only to
read-only databases).

3504

For internal testing. Will raise a bogus log-out-of-space condition from
checkpoint()

3505

Disables automatic checkpoints. May increase recovery time and can
prevent log space reuse until the next checkpoint is issued. Make sure
to issue manual checkpoints on all read/write databases at appropriate
time intervals. Note does not prevent the internal checkpoints that
are issued by certain commands, such as BACKUP.

3601

Stack trace when error raised. Also see 3603

3602

Records all error and warning messages sent to the client.

3603

SQL Server fails to install on tricore, Bypass SMT check is enabled,
flags are added via registry. Also see 3601.

3604

Sends trace output to the client. Used only when setting trace flags
with DBCC TRACEON and DBCC TRACEOFF.

3605

Sends trace output to the error log.  (if SQL Server is started from CMD
output also appears on the screen)

3607

SQL 7+ Skips the recovery of databases on the startup of SQL Server and
clears the TempDB. Setting this flag lets you get past certain crashes,
but there is a chance that some data will be lost

3608

SQL 7+ Prevents Instance from automatically starting-recovering any
database except master. Databases will be started and recovered when
accessed. Some features, such as snapshot isolation and read committed
snapshot, might not work.

Works when SQL Server is started as an application.

3609

Skips the creation of the tempdb database at startup. Use this trace
flag if the device or devices on which tempdb resides are
problematic or problems exist in the model database.

3610

SQL 9 – Divide by zero to result in NULL instead of error.

3625

Limits the amount of information returned in error messages. For more
information, see Metadata Visibility
Configuration. Scope:
global.

3626

Turns on tracking of the CPU data for the sysprocesses table.

3635

Print diagnostic information. Trace Flag 3635 Diagnostics are written to
the console that started it. There are not written to the errorlog, even
if 3605 is turned on.

3640

Eliminates sending DONE_IN_PROC messages to client for each statement
in stored procedure. This is similar to the session setting of SET
NOCOUNT ON, but when set as a trace flag, every client session is
handled this way.

3654

Allocations to stack.

3659

 

3688

SQL 9+, Avoids messages with ID 19030 and message ID 19031 are logged in
the Errorlog when many traces are started & stopped. KB
922578.Scope: global.

3689

Logs extended errors to errorlog when network disconnect occurs, turned
off by default. Will dump out the socket error code this can sometimes
give you a clue as to the root cause.

3913

SQL 7/8 – SQL Server does not update the rowcnt column of
the sysindexes system table until the transaction is committed. When
turned on the optimizer gets row count information from in-memory
metadata that is saved to sysindexes system table when the
transaction commits.

4001

Very verbose logging of each login attempt to the error log. Includes
tons of information.

4010

Allows only shared memory connections to the SQL Server. Meaning, you
will only be able to connect from the server machine itself. Client
connections over TCP/IP or named pipes will not happen.

4013

Writes an entry to error log when a new connection is established. For
each connection that occurs, the trace flag writes two entries that look
like:

Login: sa saSQL Query Analyzer(local)ODBCmaster, server process ID
(SPID): 57, kernel process ID (KPID): 57.
Login: sa XANADUsaSQL Query Analyzer(local)ODBCmaster, server process ID
(SPID): 57, kernel process ID (KPID): 57.

4020

Boot without recover.

4022

Used to bypass automatically started (startup) procedures, this is a
subset of startup option –f. TIP: Each SP consumes one worker thread
while executing so you may prefer to make one startup procedure that
calls others.

4029

Logs extended errors to errorlog when network disconnect occurs, turned
off by default. Will dump out the socket error code this can sometimes
give you a clue as to the root cause.

4030

Prints both a byte and ASCII representation of the receive buffer. Used
when you want to see what queries a client is sending to SQL Server. You
can use this trace flag if you experience a protection violation and
want to determine which statement caused it. Typically, you can set this
flag globally or use SQL Server Enterprise Manager. You can also use
DBCC INPUTBUFFER.

4031

Prints both a byte and ASCII representation of the send buffers (what
SQL Server sends back to the client). You can also use DBCC
OUTPUTBUFFER.

4032

Traces the SQL commands coming in from the client. When enabled with
3605 it will direct those all to the error log.

4044

SA account can be unlocked by rebooting server with trace flag. If sa
(or sso_role) password is lost, add this to your RUN_serverfile. This
will generate new password when server started.

4052

SQL 9+ Prints TDS packets sent to the client (output) to console.–
Startup only.

4055

SQL 9+ Prints TDS packets received from the client to console.– Startup
only.

4102

SQL 9 – Query performance is slow if the execution plan of the query
contains semi join operators Typically, semi join operators are
generated when the query contains the IN keyword or the EXISTS keyword.
Enable flag 4102 and 4118 to overcome this. KB – 940128

4103

 

4104

SQL 9 – Overestimating cardinality of JOIN operator. When additional
join predicates are involved, this problem may increase the estimated
cost of the JOIN operator to the point where the query optimizer chooses
a different join order. When the query optimizer chooses a different
join order, SQL 9 system performance may be slow. KB – 920346

4105

 

4106

 

4107

SQL 9 – When you run a query that references a partitioned table, query
performance may decrease. KB 923849

4108

 

4109

 

4110

 

4111

 

4112

 

4115

 

4116

SQL 9 – Query runs slowly when using joins between a local and a remote
table. KB 950880

4117

SQL 9 – Blocking issues occur when updating rows in a table.
KB 948445

4118

SQL 9 – Query performance is slow if the execution plan of the query
contains semi join operators Typically, semi join operators are
generated when the query contains the IN keyword or the EXISTS keyword.
Enable flag 4102 and 4118 to overcome this. KB 940128

4119

 

4120

 

4101

SQL 9 – Query that involves an outer join operation runs very slowly.
However, if you use the FORCE ORDER query hint in the query, the query
runs much faster. Additionally, the execution plan of the query contains
the following text in the Warnings column:  NO JOIN PREDICATE.

4121

4122

 

4123

Query that has many outer joins takes a long time to compile in SQL
Server 2005
KB Article:
First Added: SQL 2005 SP2 CU4
Versions: SQL 2005

4124

 

4125

SQL 9 – Query may take more time to finish if using an inner join to
join a derived table that uses DISTINCT keyword. KB 949854

4126

 

4127

SQL 9 – Compilation time of some queries is very long in an x64-based
version. Basically its more than execution time because more memory
allocations are necessary in the compilation process. Kb 953569

4128

 

4129

 

4131

 

4133

SQL 9/10 – Size of error log file grows very quickly when query
notifications are created and destroyed in a high ratio.
KB 958006

4134

Incorrect results or constraint violation when you run a SELECT or DML
statement that uses the row_number function and a parallel execution
plan in SQL Server 2008
Purpose 2: Results may change every time that you run a parallel
query in SQL Server 2005, in SQL Server 2008, or in SQL Server 2008 R2
if the query uses a ranking function and if the computer has eight or
more CPUs
KB Article 1:
KB Article 2:
First Added: SQL 2008 CU6 or SQL 2008 SP1 CU2
Versions: SQL 2008

4135

SQL 10 – Disables optimization in Query optimizer KB 960770

4136

Disables parameter sniffing
(())
which is equivalent to adding an OPTIMIZE FOR UNKNOWN hint to each query
which references a parameter. KB 980653

4199

Over time there were multiple fixes, each controlled with a different
trace flag. This can make troubleshooting query performance difficult
and time-consuming. To improve the troubleshooting process, trace flag
4199 was added in CuP 6 for SQL Server 2005 SP3, CuP 7 for SQL Server
2008, CuP 7 for SQL Server 2008 SP1, and SQL Server 2008 R2. This one
trace flag can be used to enable all the fixes that were previously made
for the query processor under many trace flags. In addition, all future
query processor fixes will be controlled by using this trace
flag. Scope: global / session.

KB article

Flag

KB article

Flag

KB article

Flag

KB article

Flag

318530

4101

926024

4108

942659

4119

953569

4127

940128

4102

926773

4109

953948

4120

955694

4128

919905

4103

933724

4110

942444

4121

957872

 

920346

4104

934065

4111

946020

4122

958547

4129

920347

4105

946793

4115

948248

4124

956686

4131

922438

4106

950880

4116

949854

4125

958006

4133

923849

4107

948445

4117

959013

4126

960770

4135*

2276330

 

2698639

 

2649913

 

2260502

 

 

 

4606

Over comes SA password by startup.  KB 936892 / Disables password policy
check during server startup.

4610

When you use trace flag 4618 together with trace flag 4610, the number
of entries in the cache store is limited to 8,192. When the limit is
reached, SQL 2005 removes some entries from the TokenAndPermUserStore
cache store. KB 959823.

4612

Disable the ring buffer logging – no new entries will be made into the
ring buffer.

4613

Generate a minidump file whenever an entry is logged into the ring
buffer.

4614

Enables SQL Server authenticated logins that use Windows domain password
policy enforcement to log on to the instance even though the SQL Server
service account is locked out or disabled on the Windows domain
controller. KB 925744.

4616

Makes server-level metadata visible to application roles. In SQL Server,
an application role cannot access metadata outside its own database
because application roles are not associated with a server-level
principal. This is a change of behavior from earlier versions of SQL
Server. Setting this global flag disables the new restrictions, and
allows for application roles to access server-level metadata. Scope:
global

4618

Limits number of entries per user cache store to 1024. It may incur a
small CPU overhead as when removing old cache entries when new entries
are inserted. It performs this action to limit the size of the cache
store growth. However, the CPU overhead is spread over time. Kb 933564

4621

SQL 9 – After 4610 & 4618 you can still customize the quota for
TokenAndPermUserStore cache store that is based on the current
workload.  KB 959823

5101

Forces all I/O requests to go through engine 0. This removes the
contention between processors but could create a bottleneck if engine 0
becomes busy with non-I/O tasks. For more
information…5101/5102.

5102

Prevents engine 0 from running any non-affinitied tasks. For more
information…5101/5102.

5302

Alters default behavior of select…INTO (and other processes) that lock
system tables for the duration of the transaction. This trace flag
disables such locking during an implicit transaction. 

6527

Disables generation of a memory dump on the first occurrence of an
out-of-memory exception in CLR integration. By default, SQL
Server generates a small memory dump on the first occurrence of an
out-of-memory exception in the CLR.  

The behavior of the trace flag is as follows:

  • If this is used as a startup trace flag, a memory dump is never
    generated. However, a memory dump may be generated if other trace
    flags are used.
  • If this trace flag is enabled on a running server, a memory dump
    will not be automatically generated from that point on. However, if
    a memory dump has already been generated due to an out-of-memory
    exception in the CLR, this trace flag will have no effect.

Scope: global

7103

Disable table lock promotion for text columns.  KB 230044

7300

Retrieves extended information about any error you encounter when you
execute a distributed query.

7501

Dynamic cursors are used by default on forward-only cursors. Dynamic
cursors are faster than in earlier versions and no longer require unique
indexes. 75401 4disables dynamic cursor enhancements and reverts to
version 6.0 behavior.

7502

Disables the caching of cursor plans for extended stored procedures.

7505

Enables version 6.x handling of return codes when calling
dbcursorfetchex and the resulting cursor position follows the end of the
cursor result set.

7525

SQL 8 – Reverts to ver 7 behavior of closing nonstatic cursors
regardless of the SET CURSOR_CLOSE_ON_COMMIT state.

7601

7603

7604

7605

Helps in gathering more information in full text search by turning on
full text tracing which gathers information on indexing process using
the error log.

7608

Performance fix for slow full text population with a composite clustered
index
KB Article:
First Added: SQL 2005 SP2 CU3
Versions: SQL 2005, SQL 2008, SQL 2008 R2, SQL 2012

7613

SQL 9 – Search results are missing when performing a full-text search
operation on Win SharePoint Services 2.0 site after upgrading.
KB 927643

7614

SQL 9 – Full-text index population for the indexed view is very slow. KB
928537

7646

SQL 10 – Avoids blocking when using full text indexing. An issue we
experienced that full text can be slow when there is a high number of
updates to the index and is caused by blocking on the docidfilter
internal table.

7806

SQL 9 – Enables a dedicated administrator connection on SQL Express, DAC
resources are not reserved by default.

Scope: global.

8002

Allows changing the meaning of affinity mask so that it is treated like
a process affinity. KB 818769

8004

SQL server to create a mini-dump once you enable 2551 and a out of
memory condition is hit.

8010

SQL Server services cannot be stopped on a computer that has SQL Server
2008 R2 Express Edition installed
KB Article:
First Added: SQL 2008 R2 SP1 CU4
Versions: SQL 2008 R2

8011

Disables the collection of additional diagnostic information for
Resource Monitor. You can use the information in this ring buffer to
diagnose out-of-memory conditions. Scope GLOBAL.

8012

Records an event in the schedule ring buffer every time that one of the
following events occurs:

  • A scheduler switches context to another worker.
  • A worker is suspended or resumed.
  • A worker enters the preemptive mode or the non-preemptive mode.

You can use the diagnostic information in this ring buffer to analyze
scheduling problems. For example, you can use the information in this
ring buffer to troubleshoot problems when SQL Server stops responding.
Trace flag 8012 disables recording of events for
schedulers. Scope Startup.

8015

SQL 9 – CPU utilization of a CPU in a single node increases to 100
percent when you use SQL Server 2005 on a multiprocessor computer that
uses NUMA architecture. Scope – Startup
KB 948450

8017

upgrade version
conflict 

and 

8018

Disables the creation of the ring buffer, and no exception information
is recorded. The exception ring buffer records the last 256 exceptions
that are raised on a node. Each record contains some information about
the error and contains a stack trace. A record is added to the ring
buffer when an exception is raised. Scope Startup

8019

Disables stack collection during the record creation, has no effect if
trace flag 8018 is turned on. Disabling the exception ring buffer makes
it more difficult to diagnose problems that are related to internal
server errors. Scope Startup

8020

SQL Server uses the size of the working set when SQL Server interprets
the global memory state signals from the operating system. Trace flag
8020 removes the size of the working set from consideration when SQL
Server interprets the global memory state signals. If you use this trace
flag incorrectly, heavy paging occurs, and the performance is poor.
Therefore, contact Microsoft Support before you use. Scope Startup.

8030

SQL 9 – Occurs only on 64-bit servers with 16+ GB of physical memory. On
SQL 9 query performance may degrade with time and when you query
the sys.dm_os_wait_stats dynamic management view, you may notice
that there are many rows in which the values of
the wait_type column are SOS_RESERVEDMEMBLOCKLIST or DBCC
MEMORYSTATUS. This situation indicates that many multipage allocations
exist. KB 917035

8033

SQL 9 – Disable the reporting of CPU Drift errors in the SQL Server
error log like time stamp counter of CPU on scheduler id 1 is not
synchronized with other CPUs.

8038

SQL 9+ Database engine & SSRS use SQLOS which exposes an internal timer
which if set to a 1ms granularity, more power consumption may occur on
Windows client. 8038 avoids it affecting the output of some
DMV’s. Scope Startup
KB 972767

8048

Newer hardware with multi-core CPUs can present more than 8 CPUs within
a single NUMA node. Microsoft has observed that when you approach and
exceed 8 CPUs per node the NODE based partitioning may not scale as well
for specific query patterns. However, using trace flag 8048 (startup
parameter only requiring restart of the SQL Server process) all NODE
based partitioning is upgraded to CPU based partitioning. Remember this
requires more memory overhead but can provide performance increases on
these systems.

HOW DO I KNOW IF I NEED THE TRACE FLAG?

The issue is commonly identified by looking as the
DMVs dm_os_wait_stats and dm_os_spin_stats for types
(CMEMTHREAD and SOS_SUSPEND_QUEUE). Microsoft CSS usually sees the
spins jump into the trillions and the waits become a hot spot.

8049

SQL 9+ Startup only – Allows use of 1ms times even when patched. Check
8038 for details. KB 972767

8202

Used to replicate UPDATE as DELETE/INSERT pair at the publisher. i.e.
UPDATE commands at the publisher can be run as an “on-page
DELETE/INSERT” or a “full DELETE/INSERT”. If the UPDATE command is run
as an “on-page DELETE/INSERT,” the Logreader send UDPATE command to the
subscriber, If the UPDATE command is run as a “full DELETE/INSERT,” the
Logreader send UPDATE as DELETE/INSERT Pair. If you turn on trace flag
8202, then UPDATE commands at the publisher will be always send to the
subscriber as DELETE/INSERT pair.

8206

SQL 8 – Supports stored procedure execution with a user specified owner
name for SQL Server subscribers or without owner qualification for
heterogeneous subscribers.

8203

Display statement and transaction locks on a deadlock error.

8207

Enables singleton updates for Transactional Replication, released with
SQL Server 8 SP 1. KB 302341.

8446

Databases in SQL 8 do not have a Service Broker ID. If you restore these
databases on SQL 9 by using the WITH NORECOVERY option, these databases
will not be upgraded causing mirroring & log-shipping configurations to
fail. KB 959008.

8501

Writes detailed information about Ms-DTC context & state changes to the
log.

8599

Allows you to use a save-point within a distributed transaction.

8602

Ignore index hints that are specified in query/procedure.

8679

Prevents the SQL Server optimizer from using a Hash Match Team operator.

8687

Used to disable query parallelism.

8721

Dumps information into the error log when AutoStat has been run.

8722

Disable all other types of hints. This includes the OPTION clause.

8744

Disables pre-fetching for the Nested Loops operator. Incorrect use
of this trace flag may cause additional physical reads when SQL Server
executes plans that contain the Nested Loops operator. For more
information about the Nested Loops operator, see the “Logical and
physical operators reference” topic in SQL Server 9 BOL.
You can turn on trace flag 8744 at startup or in a user session. When
you turn on trace flag 8744 at startup, the trace flag has global scope.
When you turn on trace flag 8744 in a user session, the trace flag has
session scope.

8755

Disable any locking hints like READONLY. Allows SQL Server to
dynamically select the best locking hint for the query.

8765

Allows use of variable length data, from ODBC driver; fixes the issue of
a field returning the wrong data length.

8783

Allows DELETE, INSERT, and UPDATE statements to honor the SET ROWCOUNT
ON setting when enabled.

8816

Logs every two-digit year conversion to a four-digit year.

9059

SQL 8 – Turns back behavior to SP3 after a SP4 installation, this allows
to choose an index seek when comparing numeric columns or numeric
constants that are of different precision or scale; else would have to
change schema/code.

9082

SQL 9 – Stored procedure using views, perform slow compared to ver 8 if
views use JOIN operator and contain sub queries. KB 942906

9134

SQL 8 – Does additional reads to test if the page is allocated & linked
correctly this checks IAM & PFS. Fixes error 601 for queries under
Isolation level read uncommitted. In case performance is affected
(because of a bug) apply SP4.

9204

When enabled and a plan is compiled or recompiled there is a listing of
statistics which is being fully loaded & used to produce cardinality and
distribution estimates for some plan alternative or other.

9259

SQL 9/10 – An access violation occurs on running a query marked by the
following message and a dump in the log folder: KB 970279 / 971490

Msg 0, Level 11, State 0, Line 0 – A severe error occurred on the
current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0 – A severe error occurred on the
current command. The results, if any, should be discarded.

9292

When enabled the query analyzer provides a report of statistics objects
while compiling / recompiling the query. Only header is loaded for the
potentially useful statistics.

9268

SQL 8 – When SQL Server runs a parameterized query that contains several
IN clauses, each with a large number of values, SQL Server may return
the following error message after a minute or more of high CPU
utilization: KB 325658

Server: Msg 8623, Level 16, State 1
Internal Query Processor Error: The query processor could not produce a
query plan. Contact your primary support provider for more information.

9485

SQL 11 cu 2 – Reverts original permissions for DBCC SHOW_STATISTICS
i.e. allows for users who have SELECT permissions to run the DBCC
SHOW_STATISTICS command in case the following conditions are true
(KB 2683304):

Users have SELECT permissions on all columns in the statistics object.

Users have SELECT permissions on all columns in a filter condition if
the filter exists.

9532

SQL 11 CTP3 – to get more than 1 availability group replica in
CTP3 Scope Startup.

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图