Server中有关追踪,从客户会话状态解析

2019-10-17 00:16 来源:未知

一. 概述

  在生产数据库运行期间,有时我们需要查看当前用户会话状态或者是说数据库当前是否运行良好, 应用的场景比如:当运行的应用系统响应突然变慢时需要分析数据库的、或想分析当前的数据库是否繁忙,是否有长时间的等待, 又或者执行一个sql的回滚状态查看,想手动kill掉一个会话时 等等。都需要从当前的会话状态去分析。

  这篇主要介绍sys.sysprocesses 函数,这里面包含了当前用户大量的信息,如会话进程有多少,等待时间,open_tran有多少事务,阻塞会话是多少等  整体内容更为详细是一个分析当前会话状态比较好的切入点。

SELECT * FROM sys.sysprocesses

图片 1图片 2

 下面先来介绍来函数的字段说明,字段很多一个个来详细介绍下,了解了含义后,后面来案例分析:

字段名称 说明
spid 会话ID(进程ID) SQL内部对一个连接的编号,一般来讲,小于50,如果用户连接的编号,大于50
blocked 阻塞ID

阻塞的进程ID, 值大于0表示阻塞,  值为本身进程ID表示io操作

如果blocked>0,但waittime时间很短,说明阻塞时间不长,不严重

 

waitresource 等待资源 格式为 fileid:pagenumber:rid 如(5:1:8235440)
kipid 线程ID

当kpid值为不0时,代表当前是活动用户

kpid=0, waittime=0     空闲连接

kpid>0, waittime=0     运行状态

kpid>0, waittime>0     需要等待某个资源,才能继续执行,一般会是suspended(等待io)

kpid=0, waittime=0    但它还是阻塞的源头,查看open_tran>0 事务没有及时提交

 

waittime 当前等待时间(以毫秒为单位)
open_tran  进程的打开事务数
hostname 建立连接的客户端工作站的名称
program_name  应用程序的名称
hostprocess 工作站进程 ID 号
loginame  登录名
status

running = 会话正在运行一个或多个批
background = 会话正在运行一个后台任务,例如死锁检测
rollback = 会话具有正在处理的事务回滚
pending = 会话正在等待工作线程变为可用
runnable = 会话中的任务在等待,由scheduler来运行的可执行队列中。(重要)
spinloop = 会话中的任务正在等待调节锁变为可用。
suspended = 会话正在等待事件(如 I/O)完成。(重要)
sleeping = 连接空闲

如果status 上有好几个runnable状态任务,需要认真对待。 cpu负荷过重没有及时处理用户的并发请求

  其它字段信息查看msdn

  1.1  查看用户会话信息

select * from  sys.sysprocesses  where spid>50

图片 3

  1.2 查看活动用户信息

SELECT spid,kpid,blocked ,waittime AS 'waitms', lastwaittype, DB_NAME(dbid),  waitresource, open_tran,hostname,[program_name],hostprocess,loginame, [status]
FROM sys.sysprocesses WITH(NOLOCK) 
WHERE    kpid>0  AND  [status]<>'sleeping'  AND spid>50
ORDER BY waittime DESC

图片 4

  1.3 查看用户阻塞会话信息

SELECT spid,kpid,blocked ,waittime AS 'waitms', lastwaittype, DB_NAME(dbid),  waitresource, open_tran,hostname,[program_name],hostprocess,loginame, [status]
FROM sys.sysprocesses WITH(NOLOCK) 
WHERE    blocked>0  AND spid>50
ORDER BY waittime DESC

图片 5

  1.4 查看系统会话信息

select * from  sys.sysprocesses  where spid<=50

图片 6

 

需要别人远程你的数据库,首先需要的是在一个局域网内,或者连接的是同一个路由器,接下来就是具体步骤:

前言

(一)首先是要检查SQLServer数据库服务器中是否允许远程链接。其具体操作为:

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

(1)打开数据库,用本地帐户登录,右击第一个选项,选择属性:

简单的举几个应用场景:

图片 7

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

(2)在打开的属性窗口,在其右边点击“连接”,然后再左侧勾上“允许远程连接到此服务器”:

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

图片 8

 

 

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

(二)为微软SQL服务器(MSSQLServer)配置相应协议。

 

 (1)依次选择:开始-〉所有程序-〉Microsoft SQL Server 2008-〉配置工具-〉SQL Server配置管理器,如下图所示:

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

图片 9

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

(2)打开SQL Server配置管理器后,选择SQL Server网络配置下面的MSSQLSERVER,然后看右边里面的TCP/IP是否为“已启用”,如下图所示:

select * from sys.configurations where configuration_id = 1568

图片 10

图片 11

 

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

(三)检查SQL服务器防火墙设置 (快捷步骤-直接关闭防火墙(不安全))

select * from sys.traces

(1)在进行完上两步操作后,用户需要做的是对SQL服务器防火墙进行重新配置。在进行这一步操作时,首先找到SQL服务器上那个端口支持TCP/IP协议。用户可以在SQL服务器防火墙已经处于运行状态下,右击“TCP/IP协议”选择“属性”:

图片 12

图片 13

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

从上图中我们可以看出,这台SQL服务器上支持TCP/IP协议的是1433端口。下一步要做的是在防火墙的配置中允许1433端口支持TCP/IP协议即可。如果服务器上运行的是Windows 7操作系统,其配置步骤为(其他微软操作系统的做法类似),打开“控制面板”选择“Windows防火墙”选项,然后点击高级选项,如下图所示:

图片 14

图片 15

--开启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

(2)选择“高级设置”后,在右边菜单栏中找出“具有高级安全选项的Windows防火墙”并将其打开。打开后会发现在左边菜单栏中有“入站规则(Inboud Rules)”选项。将该选项打开,并在右边菜单栏中选择“新建规则(New Rule)”选项:

图片 16

图片 17

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

(3)打开“新建规则”选项后,利用“新内置绑定规则向导”为1433端口配置“内部绑定协议”配置为适用于TCP/IP协议即可。(前提是,需要完成该步骤以前所述的所有步骤),根据下面的几幅图为1433端口配置适用于1433端口即可:

select * from ::fn_trace_getinfo(0)

图片 18

图片 19

图片 20

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

图片 21

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

图片 22

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

图片 23

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

最后点击“完成”即可。测试方法:在局域网内找一台电脑,打开SQLServer数据库,然后输入你的Ip和密码,如果能连接上,就说明已经配置好了,如下图所示:

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

图片 24

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

 

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

图片 25

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

 

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

图片 26

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

图片 27

--获取跟踪文件中前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:dataDefaultFileMangerMSSQL10.MSSQLSERVERMSSQLLoglog_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

图片 28

图片 29

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

 

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

 

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

图片 30

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

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

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

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

TAG标签:
版权声明:本文由澳门金莎娱乐网站发布于数据库,转载请注明出处:Server中有关追踪,从客户会话状态解析