使用语句检测和优化数据库 （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 intexec 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 intexec sp_trace_create @traceid output ,4,N'd:\TRAC_E.TRC';SELECT @traceiddeclare @on bitset @on=1exec 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
查看文件 对其进行分析
2.系统统计函数
1，查看系统登录次数
select GETDATE() as '当前时间' , @@CONNECTIONS AS '尝试登录次数'/*当前时间 尝试登录次数----------------------- -----------2012-07-25 11:21:34.923 7538494(1 行受影响)*/
2，获取网络数据包的统计信息
,@@PACK_RECEIVED上次启动后从网络中读取的输入数据包数量
,@@PACK_SENT 发送
,@@PACK_ERRORS 网络中的错误数据包
select GETDATE() as 当前时间,@@PACK_RECEIVED as 输入数据包数量,@@PACK_SENT as 发送的数据包,@@PACKET_ERRORS as 错误数据包/*当前时间 输入数据包数量 发送的数据包 错误数据包----------------------- ----------- ----------- -----------2012-07-25 11:28:09.557 27453394 39331402 0(1 行受影响)*/
3. 获取CPU工作情况
select @@CPU_BUSY*CAST(@@TIMETICKS as float) as '工作时间MS',@@IDLE*CAST(@@TIMETICKS as Float) as '空闲时间ms' ,GETDATE() as '当前时间'/*工作时间MS 空闲时间ms 当前时间---------------------- ---------------------- -----------------------74218750 13866843750 2012-07-25 11:37:17.293(1 行受影响)*/
4.获取数据库文件I/O统计信息
database_id |null 数据库编号
file_id|null 文件编号
select * from fn_virtualfilestats(null,null) /*DbId FileId TimeStamp NumberReads BytesRead IoStallReadMS NumberWrites BytesWritten IoStallWriteMS IoStallMS BytesOnDisk FileHandle------ ------ ----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------1 1 12616750 45 2834432 857 1 8192 0 857 9240576 0x0000037C1 2 12616750 13 327680 59 50 208896 3578 3637 1835008 0x000003782 1 12616750 25 1466368 1225 6 278528 0 1225 8388608 0x0000053C2 2 12616750 6 385024 333 6 92672 298 631 524288 0x000005343 1 12616750 47 2908160 1947 2 16384 139 2086 2359296 0x0000044C3 2 12616750 7 389120 737 8 40960 494 1231 786432 0x0000048C4 1 12616750 70 4415488 4540 1 8192 24 4564 161873920 0x000004684 2 12616750 15 413696 1624 5 24576 232 1856 2883584 0x000004885 1 12616750 22 1269760 1446 1 8192 25 1471 3407872 0x000004585 2 12616750 26 251392 2809 5 60416 581 3390 6553600 0x000004846 1 12616750 19 1073152 1328 1 8192 35 1363 11796480 0x000004606 2 12616750 22 394752 2496 5 15360 256 2752 5373952 0x000004A07 1 12616750 14 745472 1517 1 8192 2 1519 2359296 0x000004707 2 12616750 6 385024 623 5 12800 494 1117 589824 0x0000014812 1 12616750 3096 241000448 144381 1 8192 136 144517 416284672 0x0000045412 2 12616750 59 602112 7002 4 9728 207 7209 16973824 0x0000049013 1 12616750 15 811008 1565 1 8192 48 1613 6291456 0x0000046C13 2 12616750 8 37888 905 5 13824 339 1244 1048576 0x0000049415 1 12616750 14 745472 1396 1 8192 57 1453 3145728 0x0000044815 2 12616750 8 393216 734 5 14336 342 1076 1048576 0x0000048016 1 12616750 12 614400 1097 1 8192 446 1543 3145728 0x0000045C16 2 12616750 8 393216 727 5 14848 247 974 1048576 0x0000049817 1 12616750 29 1720320 3468 1 8192 87 3555 2490368 0x0000014C17 2 12616750 8 393216 615 5 10752 292 907 1048576 0x000004F018 1 12616750 12 614400 2116 1 8192 204 2320 3145728 0x0000013018 2 12616750 9 349184 746 4 9728 384 1130 1310720 0x000004EC19 1 12616750 19 1073152 1775 1 8192 16 1791 3407872 0x000004E819 2 12616750 15 135680 2332 5 36864 254 2586 2949120 0x0000050820 1 12616750 14 745472 2228 1 8192 49 2277 2359296 0x000004F420 2 12616750 6 355328 1493 5 15872 414 1907 589824 0x000004FC21 1 12616750 14 745472 1501 1 8192 1 1502 2359296 0x0000050421 2 12616750 6 28160 610 5 13312 1338 1948 589824 0x0000050C22 1 12616750 16 876544 1645 1 8192 48 1693 2293760 0x000004F822 2 12616750 19 438272 3684 5 17408 417 4101 4390912 0x000004DC23 1 12616750 25 1466368 1146 1 8192 24 1170 817889280 0x0000050023 2 12616750 179 1093632 13435 5 14336 316 13751 298909696 0x0000051024 1 12616750 99 6316032 3177 1 8192 6 3183 5126881280 0x000004E424 2 12616750 343 1537536 19405 5 56320 73 19478 3239968768 0x0000051825 1 12616750 13 679936 1168 1 8192 152 1320 3145728 0x0000051425 2 12616750 9 267776 724 5 15360 419 1143 1310720 0x0000052826 1 12616750 14 745472 1239 1 8192 42 1281 3145728 0x0000052426 2 12616750 8 393216 775 5 13312 442 1217 1048576 0x0000053027 1 12616750 27 1589248 1894 1 8192 186 2080 2228224 0x0000051C27 2 12616750 8 393216 763 5 17408 334 1097 1048576 0x0000052C28 1 12616750 15 811008 1602 1 8192 27 1629 2359296 0x0000052028 2 12616750 6 376832 566 5 14848 417 983 589824 0x0000054029 1 12616750 12 614400 1002 1 8192 39 1041 3145728 0x0000053829 2 12616750 8 393216 982 5 13824 325 1307 1048576 0x00000544(48 行受影响)*/
5.启动数据库以来执行输入输出操作的微秒数
select @@IO_BUSY* @@TIMETICKS as 'I/O操作的微秒数'/*I/O操作的微秒数-----------6718750(1 行受影响)*/
6.获取磁盘读写情况
select @@TOTAL_READ as '读取磁盘的次数',@@TOTAL_WRITE as '写入磁盘的次数'
,@@TOTAL_ERRORS as '磁盘写入错误数'
/*
读取磁盘的次数 写入磁盘的次数 磁盘写入错误数
----------- ----------- -----------
4983 252 0
(1 行受影响)
*/
差不多了 就这些，如有要补充 小弟感激不尽。。。！！
Author:szstephenzhou
Mail:szstephenzhou@163.com
