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

在最近为客户进行SQL优化的旅程中，我注意到了TempDB承受着巨大的压力。经过一番深入调查，我发现某些特定的查询语句是引起这一问题的罪魁祸首。那么，如何找出这些导致TempDB过载的查询语句呢？让我们一起来一下。

在SQL Server中，TempDB承载着三大主要功能：内部使用、外部使用以及行版本控制。对于内部使用，当执行复杂的查询，涉及大量并行处理和排序操作时，需要大量的内存空间。SQL Server会在查询开始时预估所需的内存量，如果实际分配的内存不足，就会将超出部分的内存数据转移到TempDB进行处理，这就是所谓的“Spill to TempDB”。

要监控哪些查询对TempDB造成了多大的读写压力，可以使用以下查询语句：

我们需要定义两个大整数变量来存储读写字节的累计值：

```sql

DECLARE @read_bytes BIGINT, @write_bytes BIGINT;

```

接着，我们可以从系统表中获取每个文件的读写统计信息：

```sql

SELECT @read_bytes = SUM(num_of_bytes_read), @write_bytes = 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';

```

然后执行你想要监控的查询语句。之后再次运行上述查询语句来获取查询后的读写统计信息。通过比较两次的读写值，我们可以得到该查询对TempDB的读写量。以下是一个示例查询：

```sql

SELECT

tempdb_read_MB = (SUM(num_of_bytes_read) - @read_bytes) / 1024 / 1024,

tempdb_write_MB = (SUM(num_of_bytes_written) - @write_bytes) / 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的高负载，从而进行针对性的优化。在最近的一个客户案例中，我们发现一个不良的查询导致了惊人的TempDB使用量。使用上述查询语句，我们可以轻松识别并优化这类问题。