SqlServer异常处理常用步骤

2024-03-26 05:12:03 35

SQLServer常见的问题主要是SQL问题造成，常见的主要是CPU过高和阻塞。

一、CPU过高的问题

1、查询系统动态视图查询执行时间长的sql语句

WITHProcessCTE(blocked)AS
(
SELECTspidFROMsys.sysprocessesWHEREcpu>500
)
SELECTdistincta.*
FROM(
SELECTTEXT,AA.*FROMsys.sysprocessesAA
CROSSAPPLYsys.dm_exec_sql_text(AA.sql_handle)
)a
JOINProcessCTEbucteWITH(NOLOCK)
ONbucte.blocked=a.spid
--whereloginame='TCScenery'
ORDERBYa.CPU

二、阻塞问题

1、查询系统动态视图查询阻塞的sql语句

WITHProcessCTE(blocked)AS
(
SELECTblockedFROMsys.sysprocessesWHEREblocked>0
union
SELECTblockedFROMsys.sysprocessesWHEREblocked>0
)
SELECTdistincta.*
FROM(
SELECTTEXT,AA.*FROMsys.sysprocessesAA
CROSSAPPLYsys.dm_exec_sql_text(AA.sql_handle)
)a
JOINProcessCTEbucteWITH(NOLOCK)
ONbucte.blocked=a.spid
ORDERBYa.blocked

2、使用系统自带的存储过程

Sp_who2和sp_lock以及使用dbccinputbuffer(spid)也可以用来分析阻塞

sp_who可以返回如下信息：(可选参数LoginName,或active代表活动会话数)
Spid (系统进程ID)
status (进程状态)
loginame (用户登录名)
hostname(用户主机名)
blk (阻塞进程的SPID)
dbname (进程正在使用的数据库名)
Cmd (当前正在执行的命令类型)

sp_who2除了显示上面sp_who的输出信息外，还显示下面的信息： (可选参数LoginName,或active代表活动会话数)
CPUTime (进程占用的总CPU时间)
DiskIO (进程对磁盘读的总次数)
LastBatch (客户最后一次调用存储过程或者执行查询的时间)
ProgramName (用来初始化连接的应用程序名称，或者主机名)

下面是sp_who的用法，sp_who2与此类似

A.列出全部当前进程

以下示例使用没有参数的sp_who来报告所有当前用户。

USEmaster;
GO
EXECsp_who;
GO

B.列出特定用户的进程

以下示例显示如何通过登录名查看有关单个当前用户的信息。

USEmaster;
GO
EXECsp_who'janetl';
GO

C.显示所有活动进程

USEmaster;
GO
EXECsp_who'active';
GO

D.显示会话ID标识的特定进程

USEmaster;
GO
EXECsp_who'10'--specifiestheprocess_id;
GO

sp_lock用法说明

sp_lock[[@spid1=]'sessionID1'][,[@spid2=]'sessionID2'][;]
[@spid1=]'sessionID1'

来自用户想要锁定其信息的sys.dm_exec_sessions的数据库引擎会话ID号。sessionID1的数据类型为int，默认值为NULL。执行sp_who可获取有关该会话的进程信息。如果未指定会话ID1，则显示有关所有锁的信息。

[@spid2=]'sessionID2'

来自sys.dm_exec_sessions的另一个数据库引擎会话ID号，该会话ID号可能与sessionID1同时具有锁，并且用户也需要其有关信息。sessionID2的数据类型为int，默认值为NULL。

在sp_lock结果集中，由@spid1和@spid2参数指定的会话所持有的每个锁都对应一行。如果既未指定@spid1又未指定@spid2，则结果集将报告当前在数据库引擎实例中处于活动状态的所有会话的锁。

列名

数据类型

说明

spid

smallint

请求锁的进程的数据库引擎会话ID号。

dbid

smallint

保留锁的数据库的标识号。可以使用DB_NAME()函数来标识数据库。

ObjId

int

持有锁的对象的标识号。可以在相关数据库中使用OBJECT_NAME()函数来标识对象。值为99时是一种特殊情况，表示用于记录数据库中页分配的其中一个系统页的锁。

IndId

smallint

持有锁的索引的标识号。

类型

nchar(4)

锁的类型：

RID=表中单个行的锁，由行标识符(RID)标识。

KEY=索引内保护可串行事务中一系列键的锁。

PAG=数据页或索引页的锁。

EXT=对某区的锁。

TAB=整个表（包括所有数据和索引）的锁。

DB=数据库的锁。

FIL=数据库文件的锁。

APP=指定的应用程序资源的锁。

MD=元数据或目录信息的锁。

HBT=堆或B树索引的锁。在SQLServer中此信息不完整。

AU=分配单元的锁。在SQLServer中此信息不完整。

Resource

nchar(32)

标识被锁定资源的值。值的格式取决于 Type 列标识的资源类型：

Type 值：Resource 值

RID：格式为fileid:pagenumber:rid的标识符，其中fileid标识包含页的文件，pagenumber标识包含行的页，rid标识页上的特定行。fileid与sys.database_files 目录视图中的 file_id 列相匹配。

KEY：数据库引擎内部使用的十六进制数。

PAG：格式为fileid:pagenumber的数字，其中fileid标识包含页的文件，pagenumber标识页。

EXT：标识区中的第一页的数字。该数字的格式为fileid:pagenumber。

TAB：没有提供信息，因为已在 ObjId 列中标识了表。

DB：没有提供信息，因为已在 dbid 列中标识了数据库。

FIL：文件的标识符，与 sys.database_files 目录视图中的 file_id 列相匹配。

APP：被锁定的应用程序资源的唯一标识符。格式为DbPrincipleId:<资源字符串的前2个到16个字符><哈希运算值>。

MD：随资源类型而变化。有关详细信息，请参阅sys.dm_tran_locks(Transact-SQL)中resource_description列的说明。

HBT：没有提供任何信息。请改用 sys.dm_tran_locks 动态管理视图。

AU：没有提供任何信息。请改用 sys.dm_tran_locks 动态管理视图。

模式

nvarchar(8)

所请求的锁模式。可以是：

NULL=不授予对资源的访问权限。用作占位符。

Sch-S=架构稳定性。确保在任何会话持有对架构元素（例如表或索引）的架构稳定性锁时，不删除该架构元素。

Sch-M=架构修改。必须由要更改指定资源架构的任何会话持有。确保没有其他会话正在引用所指示的对象。

S=共享。授予持有锁的会话对资源的共享访问权限。

U=更新。指示对最终可能更新的资源获取的更新锁。用于防止一种常见的死锁，这种死锁在多个会话锁定资源以便稍后对资源进行更新时发生。

X=排他。授予持有锁的会话对资源的独占访问权限。

IS=意向共享。指示有意将S锁放置在锁层次结构中的某个从属资源上。

IU=意向更新。指示有意将U锁放置在锁层次结构中的某个从属资源上。

IX=意向排他。指示有意将X锁放置在锁层次结构中的某个从属资源上。

SIU=共享意向更新。指示对有意在锁层次结构中的从属资源上获取更新锁的资源进行共享访问。

SIX=共享意向排他。指示对有意在锁层次结构中的从属资源上获取排他锁的资源进行共享访问。

UIX=更新意向排他。指示对有意在锁层次结构中的从属资源上获取排他锁的资源持有的更新锁。

BU=大容量更新。用于大容量操作。

RangeS_S=共享键范围和共享资源锁。指示可串行范围扫描。

RangeS_U=共享键范围和更新资源锁。指示可串行更新扫描。

RangeI_N=插入键范围和Null资源锁。用于在将新键插入索引前测试范围。

RangeI_S=键范围转换锁。由RangeI_N和S锁的重叠创建。

RangeI_U=由RangeI_N和U锁的重叠创建的键范围转换锁。

RangeI_X=由RangeI_N和X锁的重叠创建的键范围转换锁。

RangeX_S=由RangeI_N和RangeS_S锁的重叠创建的键范围转换锁。

RangeX_U=由RangeI_N和RangeS_U锁的重叠创建的键范围转换锁。

RangeX_X=排他键范围和排他资源锁。这是在更新范围中的键时使用的转换锁。

状态

nvarchar(5)

锁的请求状态：

CNVRT：锁正在从另一种模式进行转换，但是转换被另一个持有锁（模式相冲突）的进程阻塞。

GRANT：已获取锁。

WAIT：锁被另一个持有锁（模式相冲突）的进程阻塞。

DBCCINPUTBUFFER

显示从客户端发送到Microsoft®SQLServer™的最后一个语句。

语法

DBCCINPUTBUFFER(spid)

参数

spid

是sp_who系统存储过程的输出中所显示的用户连接系统进程ID(SPID)。

结果集

DBCCINPUTBUFFER返回包含如下列的行集。

列名

数据类型

描述

EventType

nvarchar(30)

事件类型，例如：RPC、语言或无事件。

Parameters

Int

0=文本
1-n=参数

EventInfo

nvarchar(255)

对于RPC的EventType，EventInfo仅包含过程名。对于语言或无事件的EventType，仅显示事件的头255个字符。

例如，当缓冲区中的最后事件是DBCCINPUTBUFFER(11)时，DBCCINPUTBUFFER将返回以下结果集。

EventType ParametersEventInfo
---------------------------------------------
LanguageEvent0 DBCCINPUTBUFFER(11)
(1row(s)affected)

SqlServer异常处理常用步骤

热门推荐

随机推荐