SQL Performance是谁占用 tempdb

作者:五餅二魚工作室  发布日期:2012-12-04 17:48:49

最近因为刚好要年底了,一些朋友和客户开始规划数据库年度的例行处理,不少都是利用虚拟化的环境来做个演练测试,而大部分的也都没有甚么状况,顶多就是忘记处理步骤或者是遗忘相关指令,讨论一下就可以解决了,但就是有个朋友遇到的问题比较棘手一点,就花点时间整理一下相关处理步骤。

 

由于该朋友所管理的单一数据库差不多有 400GB 的大小,但是在仿真年度相关作业处理的时候,居然会把 Tempdb 搞到挂掉,造成 SQL Server 无法正常启动。对我来说这个实在不是很常见到的状况,于是联机查看一下,原来是不知道甚么样的原因,造成 SQL Server 的 tempdb 长大到 150GB ,导致磁盘驱动器 C 都被吃光了。知道问题那就容易处理多了,此部分可以利用 MSDN 上的方式,利用最低组态启动 SQL Server 之后,便可以下指令 ALTER DATABASE tempdb MODIFY FILE 的方式,将 tempdb 指定存放到其它目录下并且重新指定大小,再将原本的参数取消之后,就可以顺利的重新启动 SQL Server 了。在这里为了也能让效能有所改善,在考虑数据库主机有八个核心的状况下,我们也同时将 tempdb 再多增加 3 个 ndf 档案,这样当有需要使用 tempdb 的时候,可以有较佳的效能。

 

虽然解决了问题,但朋友还是希望能找到问题的根源,以免下次又再发生同样的状况,所以我们用一些方法来找可能占用 tempdb 的相关处理:

暂时性对象 : 由于部分程序或者是处理的时候,可能会利用 # 或者是 ## 的方式建立一些暂时性的 Table 对象。如果是使用 # 则表示这个对象只存留在 Session 来存留的时候,如果 Session 关闭则该对象也会自动删除;但如果是使用 ## 的话,则这个 Table 除非自己下删除指令,否则会保留到 SQL Server 关闭。为了查看是否有这类型的对象,我们利用以下的指令来处理:


SELECT
   o.Name table_name, p.used_page_count * 8 used_size, p.reserved_page_count * 8 reserved_size, p.row_count
  FROM sys.dm_db_partition_stats p
  INNER JOIN sys.objects AS o  ON o.object_id = p.object_id
  WHERE o.type_desc = 'USER_TABLE' AND o.is_ms_shipped = 0


 

如果这个部分如果发觉在系统上会有大量使用这类 temporary 的物件时,其实可以视情况将这些给换成 User-defined Table Variable 来取代会更较为合适。

查询指令:当我们在下一些 SQL 指令的时候,像是又加入 Order By , Group By 之类的语法,SQL Server 必须捞大量的数据放到内存中来处理,但如果并没有足够内存的时候,便会使用 tempdb 的空间来做处理,此时我们会希望来找到这些指令,因此在 SQL Server 2005 之外,我们可以配合两个 DMV 来做处理:www.it165.net
sys.dm_db_session_space_usage
sys.dm_db_task_space usage
由于这两个 DMV 所能抓到的是 Session 还存在的时候,当 SQL 命令还在执行的时候,则这时候可以配合 sys.dm_db_task_space_usage 来取得所使用的 Page,因此我们可以用类似以下的语法来进行处理:

 

;WITH task_space_usage AS (
    SELECT session_id, request_id, SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages / 128.0 AS [internal object MB space],
       TSU.dealloc_pages / 128.0 AS [internal object dealloc MB space],
       EST.text
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ ON TSU.session_id = ERQ.session_id AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
WHERE EST.text IS NOT NULL
ORDER BY 3 DESC

如果命令已经执行完毕,上面这个指令就无法取得信息,因此这个时候可以利用 sys.dm_db_session_space_uage,可以参考以下的使用与法来处理:

 

SELECT 
  session_id, 
  internal_objects_alloc_page_count/128.0 internal_alloc_size, 
  internal_objects_dealloc_page_count/128.0 internal_dealloc_size
FROM sys.dm_db_session_space_usage AS s
WHERE s.session_id <> @@SPID and s.session_id > 50

上述两个 DMV 可能因为执行的时间差取到不同的数据,因此可以建立几个监控的 Table,在可能有异常的时段内利用这两个 DMV 的配合,定时将相关的数据给记录下来之后(建议可以 3~5分钟)再做比对,一般来说这种需要花比较长时间的处理,会比较有机会占用大量的空间。

目前透过这些方式,再来配合效能监视器,让我们可以找到一些特殊处理在短时间内耗用大量的 tempdb,希望如果有类似状况的朋友,这些方法对你们有帮助。如果想要更清楚知道相关处理,可以前往参考数据所列的网址,里面也都有许多详细的说明。

 

Tag标签: SQL   Performance   tempdb  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规