如何查看SQLSERVER中某个查询用了多少TempDB空间，

如何查看SQLSERVER中某个查询用了多少TempDB空间，sqlservertempdb

    在SQL Server中，TempDB主要负责供下述三类情况使用：

内部使用（排序、hash join、work table等)
外部使用（临时表，表变量等）
行版本控制（乐观并发控制）
 
    而对于内部使用，一些比较复杂的查询中由于涉及到了大量的并行、排序等操作时就需要大量的内存空间，每一个查询在开始时都会由SQL Server预估需要多少内存，在具体的执行过程中，如果授予的内存不足，则需要将多出来的部分由TempDB处理，这也就是所谓的Spill to TempDB。

    通过下述语句可以观察到某个查询对TempDB造成了多少读写：

DECLARE @read  BIGINT, 
    @write BIGINT
;    
SELECT @read = SUM(num_of_bytes_read), 
    @write = SUM(num_of_bytes_written) 
FROM  tempdb.sys.database_files AS DBF
JOIN  sys.dm_io_virtual_file_stats(2, NULL) AS FS
    ON FS.file_id = DBF.file_id
WHERE  DBF.type_desc = 'ROWS'

--这里放入需要测量的语句

SELECT tempdb_read_MB = (SUM(num_of_bytes_read) - @read) / 1024. / 1024., 
    tempdb_write_MB = (SUM(num_of_bytes_written) - @write) / 1024. / 1024.,
    internal_use_MB = 
      (
      SELECT internal_objects_alloc_page_count / 128.0
      FROM  sys.dm_db_task_space_usage
      WHERE  session_id = @@SPID
      )
FROM  tempdb.sys.database_files AS DBF
JOIN  sys.dm_io_virtual_file_stats(2, NULL) AS FS
    ON FS.file_id = DBF.file_id
WHERE  DBF.type_desc = 'ROWS'

    最近在一个客户那里看到的烂查询所导致的TempDB使用结果如下:

 
    使用该查询就可以帮助了解某个语句使用了多少TempDB。

SQL Server 2000对于TempDB的处理是采用SQL Server Cache Buffer Manager来管理，而不再是象SQL Server 7.0一样可以使用TempDB In RAM的，这样，在高并发情况下，由于SQL Server的后台进程不能及时调度，造成TempDB的容量迅速增大（由初始的8MB增长到性能测试时的800MB），从而导致 Cache Hit Ration迅速下降造成对TempDB的 Pages Reads/Sec和Pages Writes/Sec狂飙造成的。 TEMPDB增长的同时也会导致内存使用的增长，由于内存以及物理空间的资源使用，从而导致系统的整体性能下降。 解决方法： 定期收缩TEMPDB数据库，可以采用下面几种方式，最好做一个定期执行计划 方法1、重启SQL SERVER服务 ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 需要收缩的大小) ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE =需要收缩的大小) 方法2、 sp_spaceused @updateusage=true dbcc shrinkdatabase (tempdb, '百分比') 方法3、 dbcc shrinkfile (tempdev, '需要收缩的大小') dbcc shrinkfile (templog, '需要收缩的大小')

 

SQL Server会自动创建一个名为tempdb的数据库作为工作空间使用，当您在存储过程中创建一个临时表格时，比如(CREATE TABLE #MyTemp)，无论您正在使用哪个数据库，SQL数据库引擎都会将这个表格创建在tempdb数据库中。
而且，当您对大型的结果集进行排序，比如使用ORDER BY或GROUP BY或UNION或执行一个嵌套的SELECT时，如果数据量超过了系统内存容量，SQL数据库引擎就会在tempdb中创建工作表格。在您运行DBCC REINDEX或者向现有的表格中添加集群序列时， SQL数据库引擎同样会使用tempdb。实际上，任何针对大型表格的ALTER TABLE命令都会在tempdb中吃掉大量的磁盘空间。
在理想状态下，SQL会在完成指定操作后自动清理，并销毁这些临时表格，但是，很多问题都会导致错误。比如，您的代码创建了一个事务，但是却没能执行或重新运行，那么这些孤儿对象将遗留在tempdb中。而且，对大型数据库运行DBCC CHECK时，它还会消耗掉大量的空间，您往往会发现tempdb比设想的要大很多，甚至还会收到SQL即将用完磁盘空间的出错信息。
您有很多方法可以来修正这一情况，但从长远看来，您需要执行其它的步骤来保证正常使用。
为tempdb“减肥”最简单的办法就是关闭SQL数据库引擎然后重新启动，但是在重要的任务中，这样做可能难度很大；另一方面，如果您已经处于无法承受的状态，那么我的建议就是将这个坏消息告知您的上司，然后开始操作。
如果您幸运拥有另外一块磁盘可以用来放置tempdb，可以进行如下的操作：
USE master
GO
ALTER DATABASE tempdb modify file (name = tempdev, filename ='NewDrive:Path empdb.mdf')
GO
ALTER DATABASE tempdb modify file (name = templog, filename ='NewDrive:Path emplog.ldf')
GO
还有三项关于tempdb的属性应该检查：自动增长标记，初始大小和恢复模式，以下是关于这些属性的小窍门：
自动增长标记：记住将这个标记设为True。
初始大小：tempdb的初始大小要根据常用的工作负载来设定，如果有很多用户在使用GROUP BY、ORDER BY或者对大型表格进行聚合操作，那么您的常用工作负载会相当大。如果服务器脱机时，您可能需要检查日志文件与数据文件是否位于同一磁盘，如果这样的话，应当将需要将它们转移到新的磁盘上，您只需指明相应的数据库并使用相同的命令即可。
恢复模式：将恢复模式设定为True意味着让SQL自动截去tempdb的日志文件（在使用了每个表格之后），要找出tempdb所使用的恢复模式，可以使用如下命令：
SELECT DATABASEPROPERTYEX('tempdb','recovery')
恢复模式有三种选择：简单、完整或大量记录（bulk-logged），如要改变设置，可以使用以下命令：
ALTER DATABASE tempdb SET RECOVERY SIMPLE
这些步骤可以优化您系统中使用的tempdb，除了解决磁盘空间问题外，您还会发现SQL Server系统性能的提升。...余下全文>>