安卓10可用的性能监视器_SQL Server tempdb性能问题导致的业务故障

故障分析： SQL Server tempdb性能问题导致的业务故障

故障现象：SQL Server 数据库服务器内存使用率高接近100%；数据库主机出现ping丢包现象，业务反馈有异常。

分析过程：

通过SSMS数据库资源活动监视器，发现内存已经占用约30G，其中超过25G的进程为sqlserver，确定生产数据库主机内存资源消耗过大与数据库有直接关系；

观察数据库连接，未存在长时间阻塞的情况，但是数据库存在大量LOCK，锁数量达到2000+。，在数据库资源活动监视器中直接根据会话进程可查看所有会话对应的信息。

定位到相关的锁定进程是由以下SQL产生，该会话数非常高:

Declare @tabTaskID Table

(

TaskID NVARCHAR(32)

)

insert into @tabTaskID (TaskID) select top 10 TaskID from [tblCPPrintTask]

where [Status] = 2 and SwingCardStatus = 'True' and IsDoubleSidePrint = 'True'

and DoubleSidePrintSign =DoubleSidePrintSign and IsScan = 'False' order by CreateTime,DoubleSidePrintSign

update [tblCPPrintTask] Set IsScan = 'True' where TaskID in (select TaskID from @tabTaskID)

select TaskID,a.FileID,[Status],IsSwingCard,IsWaterMark,PrinterName,PrintType,PaperType,IsDoubleSidePrint,PaperDirection,PrintRange,StartPage,EndPage,PrintNumber

,b.[FileName],b.XpsPath,FilePath ,FilePostfix,ClientType,DoubleSidePrintSign,Creator

from [tblCPPrintTask] as a,[tblCPPrintFile] as b Where TaskID in(select TaskID from @tabTaskID) and a.FileID = b.FileID

Declare @tabTaskID Table

(

TaskID NVARCHAR(32)

)

insert into @tabTaskID (TaskID) select top 10 TaskID from [tblCPPrintTask]

where [Status] = 2 and SwingCardStatus = 'True' and IsDoubleSidePrint = 'True'

and DoubleSidePrintSign =DoubleSidePrintSign and IsScan = 'False' order by CreateTime,DoubleSidePrintSign

update [tblCPPrintTask] Set IsScan = 'True' where TaskID in (select TaskID from @tabTaskID)

查看数据库的运行时间，发现数据库服务有重启过。

与其他类型的SQL Server数据库不同的是,tempdb在SQL Server停掉,重启时会自动的drop,re-create. 根据model数据库会默认建立一个新的8MB(mdf file:8MB;ldf file:1MB, autogtouth设置为10%)大小recovery model为simple的tempdb数据库.

tempdb:是连接到 SQL Server 实例的所有用户都可用的全局资源，它保存所有临时表,临时工作表,临时存储过程,临时存储大的类型,中间结果集,表变量和游标等。另外，它还用来满足所有其他临时存储要求.

tempdb数据库建立之后,DBA可以在其他的数据库中建立数据对象,临时表,临时存储过程,表变量等会加到tempdb中.在tempdb活动很频繁时,能够自动的增长,因为是simple的recovery model,会最小化日志记录,日志也会不断的截断.

SQL Server中很多的会话活动都活发生在tempdb中,所以在某种情况可以减少多对tempdb的过度使用,以提高SQL Server的整体性能.用户建立的临时表.如果能够避免不用更新临时表数据,就尽量避免.因为UPDATE数据时会产生闩锁。

对于小型的数据库来说,8MB大小已经足够了.但是对于并发会话数较高的数据库来说,8MB远远不能满足SQL Server频繁活动的需要,因此会按照10%的比例增加,则会需要较长的时间,此段时间会严重影响SQL Server的性能.

与一般的数据库相比，tempdb作为临时存储区的使用使得工作负载模式可能包含数量不成比例的很多小对象的创建及摧毁。这种类型的工作负载能够导致一个数据库中需要分配对象的页上的Latch竞争。Latch是一个短期同步锁，用于SQL Server保护物理页。你不能像普通的锁一样通过改变隔离级别或使用提示来影响Latch，Latch是幕后自动被使用的，当内容或结构正在被改变或从磁盘读取时，它保护内存中的页免遭其他任务的修改。

当你在tempdb创建一个对象，如临时表的时候，它需分配空间，这和在正常数据库中创建表完全一样。你需要注意分配过程中的3种页：Page Free Space, Global Allocation Map, and Shared Global Allocation Map。

会话在TEMPDB中建一个临时表，SQL Server会读取SGAM页(2:1:3)找到一个和可用空间混合的范围分配给该表。SQL Server在更新SGAM页时会在其上面加了一个EX Latch，然后去读取PFS页，在范围里找可用的页去分配给对象。EX Latch也会加到PFS页上，以确保没其他对象分配到同一数据页。当更新完成后，Latch会释放掉。这是一个非常简单的过程，运行很好，直到tempdb因为分配请求变得超负载。

如果访问很频繁,loading就会加重,tempdb的性能就会对整个DB产生重要的影响。

如果使用临时表储存大量的数据且频繁访问,考虑添加index或增加tempdb数据库文件分组以增加查询效率.

与此同时使用TEMPDB还应该注意以下几项：

(1)Schedule jobs.如DBCC CHECKDB会占用系统较多的资源,较多的使用tempdb.最好在SQL Server loading比较轻的时候做.

(2)Cursors.游标会严重影响性能应当尽量避免使用.

(3)CTE(Common Table Expression).也会在tempdb中执行.

(4)SORT_INT_TEMPDB.建立index时会有此选项.

(5)Index online rebuild.

(6)临时工作表及中间结果集.如JOIN时产生的.

(7)排序的结果.

(8)AFTER and INSTEAD OF triggers.

单个tempdb数据文件要承受并发量超过1000的数据库会话，如果是设置是适当的TEMPDB数据库初始大小，性能问题不会太明显。

但是当数据库大小为初始值8M时再去承受并发量超过1000+ 的会话，并且还大量的读写更新临时表，此时数据库就会严重的性能问题。

TEMPDB会不断的分配空间，在此过程中的锁数据会不断的增加。内存资源消耗会非常严重。

TEMPDB性能问题处理方法：

1、 增加Tempdb的数据库分组文件数量，提高Tempdb并发性能，减少tmpdb数据单个文件的IO资源争用

2、设置Tempdb数据库初始大小。避免数据库反复分配数据文件大小。减少tempdb分配资源过程中的资源消耗。