如何查看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的性能。