sql - 如何在不显示查询结果的情况下测量查询持续时间？

Question

我需要测量多个查询的持续时间，如下所示:

declare @dtStart1 as datetime;
declare @dtStart2 as datetime;
declare @dtStart3 as datetime;
declare @dtStart4 as datetime;
declare @dtStart5 as datetime;
declare @dtStart6 as datetime;
declare @dtStart7 as datetime;
declare @dtStart8 as datetime;
declare @dtStart9 as datetime;
declare @dtStart10 as datetime;
declare @duration1 as int;
declare @duration2 as int;
declare @duration3 as int;
declare @duration4 as int;
declare @duration5 as int;
declare @duration6 as int;
declare @duration7 as int;
declare @duration8 as int;
declare @duration9 as int;
declare @duration10 as int;

set @dtStart1 = (select getutcdate());
--query1
set @duration1 = (select datediff(millisecond, @dtStart1, GETUTCDATE()));

set @dtStart2 = (select getutcdate());
--query2
set @duration2 = (select datediff(millisecond, @dtStart2, GETUTCDATE()));

set @dtStart3 = (select getutcdate());
--query3
set @duration3 = (select datediff(millisecond, @dtStart3, GETUTCDATE()));

set @dtStart4 = (select getutcdate());
--query4
set @duration4 = (select datediff(millisecond, @dtStart4, GETUTCDATE()));

set @dtStart5 = (select getutcdate());
--query5
set @duration5 = (select datediff(millisecond, @dtStart5, GETUTCDATE()));

set @dtStart6 = (select getutcdate());
--query6
set @duration6 = (select datediff(millisecond, @dtStart6, GETUTCDATE()));

set @dtStart7 = (select getutcdate());
--query7
set @duration7 = (select datediff(millisecond, @dtStart7, GETUTCDATE()));

set @dtStart8 = (select getutcdate());
--query8
set @duration8 = (select datediff(millisecond, @dtStart8, GETUTCDATE()));

set @dtStart9 = (select getutcdate());
--query9
set @duration9 = (select datediff(millisecond, @dtStart9, GETUTCDATE()));

set @dtStart10 = (select getutcdate());
--query10
set @duration10 = (select datediff(millisecond, @dtStart10, GETUTCDATE()));

select @duration1 / 1000.0 as q1,
       @duration2 / 1000.0 as q2,
       @duration3 / 1000.0 as q3,
       @duration4 / 1000.0 as q4,
       @duration5 / 1000.0 as q5,
       @duration6 / 1000.0 as q6,
       @duration7 / 1000.0 as q7,
       @duration8 / 1000.0 as q8,
       @duration9 / 1000.0 as q9,
       @duration10 / 1000.0 as q10;

问题是除了我真正感兴趣的结果之外，我还得到了查询的结果。我曾尝试为每个查询使用 cursor，但即使对于长查询也是即时的。可能它只是被定义但没有被执行。我已经尝试将 FMTONLY 设置为 ON 然后设置为 OFF，但这对于长查询也是即时的，甚至在中显示了列名结果。我想实现查询的执行，获得持续时间，就像查询通常没有执行的情况一样，但不将其返回给我的应用程序服务器，在那里处理查询可能返回的数百万条记录会出现问题，而不是与只获得我感兴趣的一行的理想结果相比，内存的巨大浪费，即结果。

Answer 1

想到的选项很少。

1. 抑制查询结果集的一种明显方法是将查询结果插入#temp 表，然后删除临时表。这会影响查询的运行时间，但比较容易实现。只需在查询的 SELECT 之后添加 INTO #temp 子句。调用应用程序不需要更改。

2. 更改调用应用程序并使其期待这些结果集。测量“time-to-first-row”，一旦应用程序收到第一行就停止查询。这将是一项相当重要的任务。

3. 更改查询，使其结果存储在变量中，而不是临时表中。每列一个变量。

---

注:如Martin Smith在评论中指出，将列值分配给变量可能会改变计划的形状，如他的 answer 所示。问题:sql execution latency when assign to a variable , 因此您应该谨慎使用选项 3。

---

例如，如果你有一个查询

SELECT 
    Col1
    ,Col2
    ,Col3
FROM YourTable
... some complex logic
;

将其更改为以下内容:

DECLARE @VarCol1 bigint;
DECLARE @VarCol2 int;
DECLARE @VarCol3 datetime2(0);
-- use appropriate types that match the query columns

SELECT 
    @VarCol1 = Col1
    ,@VarCol2 = Col2
    ,@VarCol3 = Col3
FROM YourTable
... some complex logic
;

此类查询将完整运行(与将查询包装在 SELECT COUNT(*) 中相反)，但其结果将存储在局部变量中。每个新行都会覆盖变量值，但它应该比使用#temp table 的开销更少。

您可以通过添加轻松验证和比较方法 1 和 3

SET STATISTICS TIME ON;
SET STATISTICS IO ON; 

查询前和

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

在查询之后。

尝试运行您的原始查询，将结果保存到#temp 表中进行查询，将结果保存到变量中进行查询，然后比较 CPU 和读取。

在我的测试中，普通查询和将结果保存到变量中的查询的读取次数相同。带有变量的查询在耗用时间 中要快得多，但具有相似的CPU 时间，因为没有网络流量。

将结果保存到临时表中的查询比将结果保存到变量中的查询读取次数更多，但速度稍慢。

我有一个大表，我的测试查询只从中读取 100 万行:

SELECT 
TOP (1000000)
[ID]
,[ElevatorID]
,[TimestampUTC]
FROM [dbo].[ArchivePlaybackStatsDay];


DECLARE @VarID bigint;
DECLARE @VarElevatorID int;
DECLARE @VarTimestampUTC int;

SELECT 
TOP (1000000)
@VarID = [ID]
,@VarElevatorID = [ElevatorID]
,@VarTimestampUTC = [TimestampUTC]
FROM [dbo].[ArchivePlaybackStatsDay];


SELECT 
TOP (1000000)
[ID]
,[ElevatorID]
,[TimestampUTC]
INTO #Temp
FROM [dbo].[ArchivePlaybackStatsDay];

DROP TABLE #Temp;

我在 SQL Sentry Plan Explorer 中运行它并获得了这些统计数据:

可以看到第一行和第二行的Reads是一样的，CPU很接近，但是Duration却相差很大，因为第一次query实际上传输了1M行给client。与使用变量的第二个查询相比，使用#temp 表的第三个查询有一些额外的开销。

---

我添加了另一个变体，将所有列转换为 varbinary 变量以统一变量声明。不幸的是，转换为 varbinary 尤其是 varbinary(max) 会产生明显的开销。

DECLARE @VarBin varbinary(8000);

SELECT 
TOP (1000000)
@VarBin = [ID]
,@VarBin = [ElevatorID]
,@VarBin = [TimestampUTC]
FROM [dbo].[ArchivePlaybackStatsDay];

DECLARE @VarBinMax varbinary(max);

SELECT 
TOP (1000000)
@VarBinMax = [ID]
,@VarBinMax = [ElevatorID]
,@VarBinMax = [TimestampUTC]
FROM [dbo].[ArchivePlaybackStatsDay];