首页 > 代码库 > tempdb 数据文件暴涨

tempdb 数据文件暴涨

2024-07-18 07:53:29   213人阅读

上周公司的生产库的tempdb瞬间暴涨，导致磁盘剩余空间为0，估计是相关人员运行不合理的sql查询导致。

tempdb在以下情况会用到:

(1)用户建立的临时表.如果能够避免不用,就尽量避免. 如果使用临时表储存大量的数据且频繁访问,考虑添加index以增加查询效率。
(2)Schedule jobs.如DBCC CHECKDB会占用系统较多的资源,较多的使用tempdb.最好在SQL Server loading比较轻的时候做。
(3)Cursors.游标会严重影响性能应当尽量避免使用。
(4)CTE(Common Table Expression).也会在tempdb中执行。

(5)SORT_INT_TEMPDB.建立index时会有此选项。
(6)Index online rebuild。
(7)临时工作表及中间结果集.如JOIN时产生的。
(8)排序的结果。
(9)AFTER and INSTEAD OF triggers。

因此如果有人在数据库中用笛卡尔乘积的方法查询多个表，并且排序的话，会造成tempdb的暴涨，因为大量的排序就是在tempdb中进行的。

为了监控tempdb的文件或者tempdb的log的大小，现写了以下一个存储过程:

 

首先建立三个表:

 1 --保存当前tempdb的数据文件和LOG文件的大小 2 if exists (select * from sysobjects where id = object_id(N‘[Check_tempdb_Size]‘) and OBJECTPROPERTY(id, N‘IsUserTable‘) = 1)     3 drop table [Check_tempdb_Size]   4 GO 5 Create table Check_tempdb_Size( 6 ID INT IDENTITY, 7 FileId int, 8 size int, 9 Name varchar(10),10 InsertdateTime datetime default getdate()11 )12 13 --保存当前dbcc opentran的信息14 if exists (select * from sysobjects where id = object_id(N‘[Check_tempdb]‘) and OBJECTPROPERTY(id, N‘IsUserTable‘) = 1)    15 drop table Check_tempdb  16 go17 create table Check_tempdb(18 ID INT IDENTITY,19 TypeName varchar(100),20 TypeValue varchar(100),21 InsertDate datetime default getdate()22 )23 24 --保存当前运行的相关进程的信息(数据库,登陆账号,登陆机器,运行的sql)25 if exists (select * from sysobjects where id = object_id(N‘[Check_tempdb_script]‘) and OBJECTPROPERTY(id, N‘IsUserTable‘) = 1)    26 drop table Check_tempdb_script  27 Create table Check_tempdb_script(28  ID INT IDENTITY(1,1),29  spid int,30  ecid int,31  [Database] varchar(20),32  nt_username nchar(256),33  [Status] varchar(20),34  [Wait] varchar(20),35  [IndividualQuery] varchar(max),36  [ParentQuery]  varchar(max),37  Program nchar(256),38  Hostname nchar(256),39  loginame nchar(256),40  nt_domain nchar(256),41  start_time datetime,42  InsertDateTime datetime default getdate()43  )

 将当前tempdb的数据文件盒LOG文件的大小保存在Check_tempdb_Size表中

insert into Check_tempdb_Sizeselect fileid,size,name,GETDATE()from tempdb..sysfiles

建立以下存储过程

 1 if exists (select * from sysobjects where id = object_id(N‘[Check_tempdb_Monitor]‘) and OBJECTPROPERTY(id, N‘IsProcedure‘) = 1)     2 drop procedure [Check_tempdb_Monitor]    3 go 4 Create procedure Check_tempdb_Monitor 5 as 6 begin  7 declare  8 @tempdb_mdf_size int, 9 @tempdb_log_size int,10 @last_tempdev int,11 @last_templog int12 end13 14 begin15 select @tempdb_mdf_size=size from tempdb..sysfiles where fileid=116 select @tempdb_log_size=size from tempdb..sysfiles where fileid=217 select @last_tempdev=size from Check_tempdb_Size 18 where InsertdateTime =(select MAX(InsertdateTime) from Check_tempdb_Size) 19 and id=120 select @last_templog=size from Check_tempdb_Size 21 where InsertdateTime =(select MAX(InsertdateTime) from Check_tempdb_Size) 22 and id=223 24 if(@tempdb_mdf_size>(@last_tempdev*1.05) or @last_templog>(@last_templog*1.05))25     begin 26         insert into Check_tempdb_Size(FileId,size,Name)27         select fileid,size,name28         from tempdb..sysfiles29 30         Insert into Check_tempdb(TypeName,TypeValue)31         exec(‘dbcc opentran(‘‘tempdb‘‘) with tableresults‘)32 33         insert into Check_tempdb_script(spid,ecid,[Database],nt_username,[Status],34 Wait,IndividualQuery,ParentQuery,Program,Hostname,loginame,nt_domain,start_time)35 SELECT[Spid] = session_Id ,36       ecid ,37       [Database] = DB_NAME(sp.dbid) ,38       [User] = nt_username ,39       [Status] = er.status ,40       [Wait] = wait_type ,41       [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2,42                                      ( CASE WHEN er.statement_end_offset = -143                                             THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))44                                                  * 245                                             ELSE er.statement_end_offset46                                        END - er.statement_start_offset ) / 2) ,47       [Parent Query] = qt.text ,48       Program = program_name ,49       Hostname ,50       loginame,51       nt_domain ,52       start_time53   FROM sys.dm_exec_requests er54       INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid55       CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt56  WHERE session_Id >= 5157     end58 end

上面的存储过程中，如果当前的tempdb的数据文件大小或者LOG文件大小比上一次记录的文件大小大5%(该值可以根据情况自己设定)，则会记录当前的信息到三张表中，否则结束。

对于以上存储过程，建一个作业，可以根据需要来决定多久跑一次，我的设定是每30s运行一次。

再遇到temp变大的时候，可以查询三张表：

可以在第二张表中看到当前运行的事务是68进程，可以在第三张表中查到68进程的详细信息。

----------------------------------

记录个CHECKIDENT的使用方法。。。

在这个示例中，我们检查表当前的IDENTITY值：

  DBCC CHECKIDENT(‘HumanResources.CompanyAuditHistory‘, NORESEED)

把种子值重设为一个更大的数字：

  DBCC CHECKIDENT (‘HumanResources.CompanyAuditHistory‘, RESEED, 50)

 


联系
我们