使用语句检测和优化数据库 （MSSQL个人笔记之数据库优化之路 二）

1.使用系统存储过程管理SQL Server Profiler

 

 

      1>使用sp_trace_create创建跟踪

          sp_trace_create [@traceid= ] trace_id output , 

          [@options = ] option_value, 

          [@tracefile = ]  'trace_file'

          [,[@maxfilesize = ] max_file_size ]

          [,[@stoptime =] 'stop_time' ]

          [,[@filecount = ] 'max_rollover_files' ]

--@traceid 新建跟踪的编号，可以是整型数据 默认是null

-- @options 设置跟踪的选项，可以设置成 TRACE_FILE_ROLLOVER 表示当跟踪文件的大小超过MAX_FILE_SIZE 系统关闭当前跟踪 并创建新的文件

--设置成SHUTDOWN_ON_ERROR如果在不能写入跟踪文件时候系统就关闭sqlserver。

--设置成TRACE_PRODUCE_BLACKBOX 服务器产生的最后5MB跟踪信息由服务器保存

--[@tracefile = ]  'trace_file' trace_file 文件

--[,[@maxfilesize = ] max_file_size ] 指定跟踪文件的最大MB

--[,[@stoptime =] 'stop_time' ]指定停止跟踪的日期时间

--[,[@filecount = ] 'max_rollover_files' ]指定最大的跟踪文件数量

 

EXp： 创建一个跟踪，编号为1 跟踪文件为D:\TRACE3.TRC 跟踪最大为5MB

declare @traceid int
exec sp_trace_create @traceid output ,4,N'd:\TRACE3.TRC';
SELECT @traceid

变量@traceid接受跟踪编号  作为参数传到其他的存储过程对跟踪进行管理

      

          2>.设置事件和事件列 sp_trace_setvent
存储过程sp_trace_setevent可以添加删除跟踪中的要监测的事件和事件列。

sp_trace_setevent [@traceid= ]  trace_id,

       [@eventid= ] event_id,

      [@columnid= ] column_id ,

      [@on = ] on

 

       [@traceid= ]  trace_id,设置事件和事件列的应用跟踪编号

       [@eventid= ] event_id,设置要监测的事件编号。12表示完成Transact SQL批处理发生该事件

       [@columnid= ] column_id ,指定事件添加的列编号。 1表示与事件相关的文本  8表示发送请求的客户端计算机名称  10创建与SQLSERVER 实例连接的客户端应用程序名    称    11 表示客户端数据库登录名 12表示与客户端相关的服务器进程。 13表示事件所花的事件ms 35表示事件中设计的数据库名称

       [@on = ] on指定筛选的值

 

 

3》筛选跟踪数据

sp_trace_setfilter  [@traceid =]  trace_id,

[@columnid =] column_id,

[@logical_operator =] logical_operator,

[@comparison_operator = ] comparison_operator,

[@value = ] value

 

  [@traceid= ]  trace_id,设置事件和事件列的应用跟踪编号

[@columnid =] column_id, 应用筛选器的列编号。

[@logical_operator =] logical_operator,指定关系运算符

[@comparison_operator = ] comparison_operator,指定比较运算符

[@value = ] value  指定进行筛选的值

 

 

 4.> 设置跟踪状态

sp_trace_setstatus [@traceid= ] trace_id,

[@status=] status

 

 

--创建跟踪 ，查看编号
declare @traceid int
exec sp_trace_create @traceid output ,4,N'd:\TRAC_E.TRC';
SELECT @traceid


declare @on bit
set @on=1
exec sp_trace_setevent @traceid,12,1, @on 
exec sp_trace_setevent @traceid,12,8, @on 
exec sp_trace_setevent @traceid,12,10, @on 
exec sp_trace_setevent @traceid,12,11, @on 
exec sp_trace_setevent @traceid,12,12, @on 
exec sp_trace_setevent @traceid,12,13, @on 
exec sp_trace_setevent @traceid,12,35, @on 
 ---s设置跟踪的过滤条件，只监测对数据库N'cssm'的相关操作
 exec sp_trace_setfilter @traceid ,35,0,0,N'cssm';
 --启动编号为1的跟踪
 exec sp_trace_setstatus @traceid ,1