`
backspace
  • 浏览: 137175 次
文章分类
社区版块
存档分类
最新评论

SQL SERVER 查询性能优化——分析事务与锁(二)

 
阅读更多

 

上接 SQL SERVER 查询性能优化——分析事务与锁(一)

 

第二步,通过SQL语句分析锁定情况

由于SQL SERVER 2008相比SQL SERVER 2005中的“活动监视器”有了比较大的改变,所以下而我们通过SQL语句进行分析,使用SQL语句进行分析需要通过SP_WHOSP_WHO2SP_LOCK等系统存储过程、Master.sys.sysprocesses系统视图,或从SQL 20052008)新提供的动态视图管理(DMVsys.dm_exec_sessionsys.dm_tran_locks等获取相关信息。

通过master.sys.sysprocesses 视图找出最初锁住资源及导致后面一连串进程被迫停止的等待源头。

下面我们举一个例子来具体说明,以下代码在SQL SERVER 2005/2008中都可以使用

--1.创建测试表

CREATE TABLE [dbo].[Book](

[bookid] [int] NOT NULL,

[Name] [varchar](60) NULL,

[category] [varchar](10) NULL,

[numberofcopies] [int] NULL,

[AuthorID] [int] NULL,

 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED 

(

[bookid] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (1, N'SQL 2008', N'MS', 4, 1)

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (2, N'SharePoint 2007', N'MS', 3, 2)

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (3, N'SharePoint 2010', N'MS', 5, 2)

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (5, N'DB2', N'IBM', 10, 3)

INSERT [dbo].[Book] ([bookid], [Name], [category], [numberofcopies], [AuthorID]) VALUES (7, N'SQL 2012', N'MS', 7, 1)

 

--2.测试示例

--列出最初锁定资源,导致一连串其他进程被锁住的起始源头。

例一

if exists(select * from master.sys.sysprocesses where spid in (select blocked from master.sys.sysprocesses))

begin

---确定进程被锁住的其他资源

select spid 进程,STATUS 状态, 登录帐号=SUBSTRING(SUSER_SNAME(sid),1,30)

,用户机器名称=SUBSTRING(hostname,1,12)

,是否被锁住=convert(char(3),blocked)

,数据库名称=SUBSTRING(db_name(dbid),1,20),cmd 命令,waittype as 等待类型

,last_batch 最后批处理时间,open_tran 未提交事务的数量

from master.sys.sysprocesses

--列出锁住别人(在别的进程中blocked字段中出现的值)但自己未被锁住(blocked=0)

Where spid in (select blocked from master.sys.sysprocesses) and blocked=0

end

else 

begin

select '没有被锁住的进程'

end 

 

---接下来我们通过以下代码来构造一条进程被另外一条进程锁住的情况。

--例二

Use test

Go

Begin tran

update book set Name='MS SQL 2008'

where bookid=1

---切换到另一个查询界面,执行以下代码

--例三

Use test

Go

select * from Book where bookid=1

go

 

 3. SQL SERVER 2005/2008中的Microsoft SQL Server Management Studio打开一个新的查询界面,执行 exec SP_LOCK。结果如下图。

 

图1

从图1中可以观察到两个进程的相互作用,其中进程“53”要求模式为“独占(X)”,已经被获取允许“GRANT”;进程“56”要求模式为“共享(S)”正等候(WAIT)处理。

 

如上图1中进程“56”(执行SELECT语句的查询连接)被进程“53”(执行UPDATE语句的查询连接)封锁的现象,并从Book数据表锁定可以看出是因为“独占”锁定某一条索引键值(要求类型为KEY),导致进程“56”放置共享锁定(要求模式为“S”),而在等待状态(要求状态为WAIT)。

4. SQL SERVER 2005/2008中的Microsoft SQL Server Management Studio打开一个新查询界面,通过另外一条连接来执行程序代码(例一),执行结果如下图。

 

2

在上图2中可以看出例二查询代码开启事务之后,未关闭事务,因此状态(status)sleeping,但并未被其他进程锁住(blk),所以“是否被锁住”列的数据为0,没有执行命令,也没有等待某种资源。另外由于该查询的数据库连接是Test,所以数据库名称为Test。

5. 查询sysprocesses系统视图呈现有问题的交易的现象可能有许多种,但最常见的一种就是status字段等于sleepingwaittype字段等于0x0000last_batch字段表示离最后一次批处理执行的时候已经有一段距离了,以及open_tran字段大于0。例如,直接执行代码例二,这时事务已经开启,但是迟迟没有结束,就可能是程序没有做好事务管理。

可以在在SQL SERVER 2005/2008中的Microsoft SQL Server Management Studio打开一个新查询界面中执行下面的语句,以查询有问题的连接

select spid 进程,STATUS 状态, 登录帐号=SUBSTRING(SUSER_SNAME(sid),1,30)

,用户机器名称=SUBSTRING(hostname,1,12)

,是否被锁住=convert(char(3),blocked)

,数据库名称=SUBSTRING(db_name(dbid),1,20),cmd 命令,waittype as 等待类型

,last_batch 最后批处理时间,open_tran 未提交事务的数量

from master.sys.sysprocesses

Where  status='sleeping' and waittype=0x0000 and open_tran>0

 

 

如下图。

 

3

status字段等于sleeping表示没有指示符正在执行,waittype字段等于0x0000代表此连接没有等待任何资源,last_batch字段表示最后一次SQL语句执行的时间,如果此时间离现在有一段时间了,以及open_tran字段大于0,就有问题了。一段时间过去了,没有等待任何资源,也没有执行任何SQL语句,那么为什么还要开启事务?

除了上述查询sysprocesses系统视图之外,SQL SERVER 2005/2008 可以通过“sys.dm_tran_locks动态管理视图”呈现目前使用中相关的锁定信息。返回的每一条记录都代表一个已经授权或等待授权的锁定。在结果集的数据行中,主要分成“资源”与“请求”两类,其字段分别以resourcerequest为前缀。资源群组描述已经锁定或等待的资源,而请求群组则描述已经获取或等待中的锁定请求。

 

 

--例四

select t1.resource_type [资源锁定类型],DB_NAME(resource_database_id) as 数据库名

,t1.resource_associated_entity_id 锁定对象,t1.request_mode as 等待者请求的锁定模式

,t1.request_session_id 等待者SID

,t2.wait_duration_ms 等待时间

,(select TEXT from sys.dm_exec_requests r cross apply

sys.dm_exec_sql_text(r.sql_handle) where r.session_id=t1.request_session_id) as 等待者要执行的SQL

,(select SUBSTRING(qt.text,r.statement_start_offset/2+1,

(case when r.statement_end_offset=-1 then DATALENGTH(qt.text) else r.statement_end_offset end -r.statement_start_offset)/2+1

)

from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle)qt

where r.session_id=t1.request_session_id) 等待者正要执行的语句

,t2.blocking_session_id [锁定者SID]

,(select TEXT from sys.sysprocesses p cross apply

sys.dm_exec_sql_text(p.sql_handle)

where p.spid=t2.blocking_session_id

) 锁定者执行语句

from sys.dm_tran_locks t1,sys.dm_os_waiting_tasks t2

where t1.lock_owner_address=t2.resource_address

 

在Sql 2005 的TEST执行上面的代码,结果如下图。(这是针对Wbk_pde_list数据表)

 

图4

在Sql 2008 的TEST数据库上执行上面的代码,结果如下图。(这是针对BOOK数据表)

 

 

图5

 

备注:

以上第二步是通过在Microsoft SQL Server Management Studio中执行代码进行查询与分析加锁情况,而在SQL SERVER 2005中则可以通过Microsoft SQL Server Management Studio管理获取相当多的信息,方便让你决定当前应该采取什么样步骤。

上而第二步中获取的信息都可以在SQL SERVER 2005中通过Microsoft SQL Server Management Studio中的“活动监视器”获取。

例如通过“活动监视器--按对象分类的锁”,下拉菜单中选择相应的对象。(如下图)

 

 

1
4
分享到:
评论

相关推荐

    SQLServer2008查询性能优化 2/2

    书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...

    事务管理(二)——SQL SERVER的事务管理

    这篇博客“事务管理(二)——SQL SERVER的事务管理”可能详细介绍了SQL Server如何处理事务,包括事务的特性、隔离级别以及如何处理事务中的并发问题,如死锁。 首先,事务有四个基本特性,即原子性(Atomicity)...

    SQLServer2008查询性能优化 1/2

    书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...

    sql server 数据库文件——图书订购

    在SQL Server中,数据库文件是存储数据和元数据的核心组件,它们构成了数据库的基础。本案例提供的"图书订购"数据库文件包含两个主要部分:mdf(主数据文件)和ldf(日志文件)。这两个文件是SQL Server数据库不可或...

    SQL Server 2005数据库实践教程——开发与设计篇-电子教案

    《SQL Server 2005数据库实践教程——开发与设计篇》是一份全面介绍SQL Server 2005数据库系统使用的电子教案。这份教程共分为7个章节,旨在帮助学习者掌握SQL Server 2005的核心概念、功能以及实际应用技巧。下面,...

    《数据库原理与应用——SQL Server 2000》电子教案

    9. **事务与并发控制**:理解事务的概念,ACID属性(原子性、一致性、隔离性、持久性),以及SQL Server 2000中的锁机制和并发控制策略。 10. **报表服务**:介绍SQL Server 2000的报表服务,如何创建、管理和部署...

    sql server性能的优化 参考文档

    #### 二、SQL Server性能优化的主要策略 ##### 1. 使用索引优化查询 - **索引的作用**:索引是一种特殊的数据结构,用于提高查询效率。它可以快速定位数据,减少数据库查找的时间。 - **索引类型**:SQL Server...

    适用SQL Server 2016版本的数据库加载驱动包——sqljdbc42.jar

    它提供了高效的数据存储、查询、分析和安全功能,广泛应用于各种业务系统。在Java应用程序中与SQL Server 2016交互,通常需要依赖特定的数据库驱动程序,这就是`sqljdbc42.jar`的角色。 `sqljdbc42.jar`是一个Java...

    数据库应用技术——SQL Server 2008篇(第3版)

    6. **索引与查询优化**:探讨如何创建和管理索引以提高查询效率,以及使用查询分析器进行性能调优。 7. **备份与恢复策略**:介绍SQL Server 2008的备份选项,包括完整备份、差异备份、日志备份,以及如何制定恢复...

    数据库应用技术——SQL Server 2000简明教程电子教案

    《数据库应用技术——SQL ...通过《数据库应用技术——SQL Server 2000简明教程》的学习,你将具备使用SQL Server 2000管理数据库、编写T-SQL脚本、优化性能和构建报表的能力,为实际工作或进一步学习打下坚实的基础。

    SQL Server 2005数据库实践教程——开发与设计篇-案例数据库

    在SQL Server 2005数据库实践中,开发者和设计师会遇到各种挑战,包括但不限于数据存储、查询优化、安全性管理以及数据库设计。本实践教程聚焦于这些关键领域,以帮助学习者掌握SQL Server 2005的核心功能和最佳实践...

    [信息系统开发教程——使用C#和SQL+Server+2005]

    《信息系统开发教程——使用C#和SQL Server 2005》是一份深入探讨如何结合C#编程语言与Microsoft SQL Server 2005数据库管理系统进行信息系统开发的教程。本教程旨在帮助开发者全面掌握这两项关键技术,从而构建高效...

    《大型数据库系统 管理、设计实例分析--基于SQL Server 》课件

    《大型数据库系统 管理、设计实例分析--基于SQL Server》这门大学课程的课件,涵盖了SQL Server 2000的核心概念和技术。在深入理解这些知识点之前,我们首先要明白SQL Server作为一款关系型数据库管理系统(RDBMS)...

    SQL_Server_2000图解教程——下

    《SQL Server 2000 图解教程——下》是一本专门为初学者设计的数据库管理教程,通过丰富的图像和简洁的文字,深入浅出地讲解了SQL Server 2000的核心概念和技术。在这个部分,我们将重点探讨SQL Server 2000在数据...

    asp+sql+server+程序代码系统——图书管理

    【ASP+SQL+Server+程序代码系统——图书管理】是一个基于Web的图书管理应用程序,它利用Active Server Pages(ASP)技术、SQL Server数据库管理和Microsoft Server Web服务器的强大功能,实现高效、安全的图书信息...

    SQL Server 2012数据库技术与应用(微课版)-综合实训及答案.zip

    《SQL Server 2012数据库技术与应用》是一门深度探讨关系型数据库管理系统SQL Server 2012的课程,旨在帮助学习者掌握数据库设计、管理、优化以及实际应用等核心技能。作为微课版,它可能包含了视频教程、实践案例、...

    数据库技术及应用——SQL Server 2005-电子教案

    SQL Server 2005提供了许多工具和功能,如查询分析器、索引优化顾问和性能计数器,帮助监控和改善系统性能。理解查询计划、索引策略和资源调优是提升系统响应速度的关键。 备份和恢复策略是保障数据安全的重要环节...

    SQL Server管理基础——SQL Server速成最后一课,SQL Server必备工具,数据库安全与备份策略.pdf

    管理员可以通过它来管理SQL Server Agent、分布式事务协调器(DTC)、Microsoft Search和Analysis Services服务。 2. 企业管理器:一个图形界面工具,用于管理和配置SQL Server实例。它允许管理员注册服务器,并执行...

Global site tag (gtag.js) - Google Analytics