如何查看SQLSERVER中某个查询用了多少TempDB空间-猿码集

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

为了更好地理解、优化SQL Server的运行性能，我们需要了解TempDB的使用情况。这篇文章将介绍如何查看SQL Server中某个查询使用了多少TempDB空间。我们将分步骤演示以下内容：

1. 确认TempDB使用情况

查询TempDB的使用情况，可以执行如下SQL语句：

SELECT 
[name] AS [File Name], 
[physical_name] AS [Physical Name], 
[size]/128.0 AS [Total Size in MB],
[size]/128.0 - CAST(FILEPROPERTY([name],'SpaceUsed') AS INT)/128.0 AS [Available Space In MB], 
CAST(FILEPROPERTY([name],'SpaceUsed') AS INT)/128.0 AS [Used Space In MB], 
[filename] AS [Path] 
FROM sys.master_files 
WHERE [database_id] = DB_ID('tempdb');

这将返回TempDB的使用情况，并告诉您可用空间、已用空间、文件路径等内容。这将使您能够查看TempDB使用情况的总体情况：

TempDB使用情况如下:

总体上来说，需要确保TempDB使用情况正常，比如磁盘空间足够、文件大小合适、性能不低等。

2. 确认某个查询的TempDB使用情况

检查某个查询的TempDB使用情况，我们将运用Extended Events技术来捕获TempDB使用情况。首先，我们需要创建一个扩展事件会话。运行如下代码：

CREATE EVENT SESSION [TempDB_Usage] 
ON SERVER 
ADD EVENT sqlserver.sp_statement_completed 
(
    ACTION(sqlserver.session_id)
    WHERE (([statement] like 'SELECT%' or [statement] like 'INSERT%' or [statement] like 'UPDATE%' or [statement] like 'DELETE%') and 
                (database_id = 2))
)
ADD TARGET package0.asynchronous_file_target
(
    SET FILENAME='C:\tempdb_useage.xel', 
    METADATAFILE='C:\tempdb_useage.xem'
) 
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF); 
ALTER EVENT SESSION [TempDB_Usage] 
ON SERVER 
STATE = START;

在这里，我们创建了一个名为TempDB_Usage的扩展事件会话，事件选择的是sqlserver.sp_statement_completed。我们还添加了一些限制条件，以仅针对TempDB中的查询捕获数据。然后我们将事件流写入一个异步文件目标。此类文件将包含Virutal Class File（VCF）文件，它们可以用作XEvent查看器中的解码模板。

在运行查询时，我们最好等待足够的时间来收集数据。然后我们可以关闭事件会话并检索执行的查询所使用的TempDB数据。运行以下代码来关闭扩展事件：

ALTER EVENT SESSION TempDB_Usage ON SERVER STATE = STOP

现在我们可以使用XEvent查看器来分析我们收集的数据。首先，我们需要选择捕获的文件。单击“文件”菜单，然后单击“打开”，导航到包含捕获的XEL文件的文件夹。选择文件然后单击“打开”。此时，捕获的事件将显示在“XEvent Viewer”窗格中。

我们使用如下的XQuery脚本来找到查询执行所使用的TempDB数据：

SELECT 
object_name(event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(256)')) AS DB_NAME, 
event_data.value('(event/action[@name="session_id"]/value)[1]', 'nvarchar(64)') AS session_id, 
event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint')/1000 AS [duration], 
event_data.value('(event/data[@name="stmt_text"]/value)[1]', 'nvarchar(max)') AS [statement], 
event_data.value('(event/data[@name="tempdb_space_usage"]/value)[1]', 'bigint') AS [space_used]
FROM 
(SELECT CAST(event_data AS XML) AS event_data FROM sys.fn_xe_file_target_read_file('C:\tempdb_useage*.xel', null, null, null)) AS event_data
WHERE object_name(event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(256)')) = 'tempdb'
ORDER BY [duration] DESC OPTION (RECOMPILE);

运行上述脚本，将显示查询和TempDB使用情况的信息。您可以使用此信息来分析TempDB使用情况，以确定是否需要增加TempDB大小和磁盘空间。

3. 结论

本文介绍了如何查看SQL Server中某个查询使用了多少TempDB空间。我们可以使用扩展事件技术捕获TempDB使用情况，并使用XEvent查看器来分析数据。此外，我们还演示了如何检查TempDB使用情况的总体状况。这将帮助您了解TempDB的使用情况，并确定是否需要采取改进措施来优化SQL Server的性能。

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