在SQL Server事件探查器中记录数据传输时间

在我看来,您似乎想要识别返回大型结果集的查询。再深入一层,您真正想做的是确定哪些查询消耗的数据量最大。这可以从逻辑读取和物理读取两个方面看出来。

Performance Dashboard Reports 或者使用SQL Server DMV。

例如,以下查询取自 SQL Server Performance Blog 作者:Glenn Berry:

/* Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure */
SELECT TOP(25) p.name AS [SP Name], qs.total_logical_reads AS [TotalLogicalReads], 
qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second], 
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC;

你通常会从客户的角度来分析。

'SELECT *' 通常不能使用覆盖索引,所以请查找书签查找。