在性能调优和查询优化中，了解SQL Server查询的执行时间和资源消耗是至关重要的。SQL Server提供了SET STATISTICS TIME和SET STATISTICS IO这两个命令，它们可以帮助我们分析查询的执行时间和IO操作。

SET STATISTICS TIME

当你开启SET STATISTICS TIME时，SQL Server会在执行查询后显示处理和执行查询所需的时间信息。这包括编译时间和执行时间，分别以毫秒为单位。

语法

SQL
SET STATISTICS TIME { ON | OFF }

示例

SQL
SET STATISTICS TIME ON;

-- 执行查询
SELECT * FROM Sales.Orders;

SET STATISTICS TIME OFF;

查询执行后，你会在消息窗口看到类似以下的输出：

C#
SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 20 ms.

SET STATISTICS IO

开启SET STATISTICS IO后，SQL Server会在查询执行完毕后显示每个表的磁盘IO统计信息。这包括扫描的页数、逻辑读取次数、物理读取次数等。

语法

SQL
SET STATISTICS IO { ON | OFF }

示例

SQL
SET STATISTICS IO ON;

-- 执行查询
SELECT * FROM Sales.Orders;

SET STATISTICS IO OFF;

执行后，消息窗口会显示如下信息：

C#
Table 'Orders'. Scan count 1, logical reads 123, physical reads 0, read-ahead reads 0.

实例数据和脚本

为了演示SET STATISTICS TIME和SET STATISTICS IO的使用，我们将创建一个简单的数据库环境，并插入一些测试数据。

数据库环境准备

假设我们有一个销售数据库SalesDB，其中包含两个表：Customers和Orders。

Customers表结构：

SQL
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    Region NVARCHAR(50)
);

Orders表结构：

SQL
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID),
    OrderDate DATETIME,
    TotalAmount MONEY
);

插入测试数据

我们将插入一些测试数据来模拟真实的业务场景。

SQL
-- 插入Customers数据
INSERT INTO Customers (CustomerID, CustomerName, Region)
VALUES
(1, 'Acme Corporation', 'North'),
(2, 'Globex Corporation', 'South'),
(3, 'Initech', 'East'),
(4, 'Umbrella Corporation', 'West'),
(5, 'Vandelay Industries', 'North');

-- 插入Orders数据
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES
(1, 1, '2023-01-01', 500.00),
(2, 1, '2023-01-02', 1500.00),
(3, 2, '2023-01-03', 200.00),
(4, 3, '2023-01-04', 750.00),
(5, 3, '2023-01-05', 250.00);

分析查询

现在我们来分析一个查询，该查询计算每个客户的订单总额。

SQL
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT CustomerID, SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

执行上述查询后，你将在消息窗口看到关于查询执行时间和IO的信息。

分析结果

执行计划和统计信息将帮助我们了解查询的性能。比如，我们可能会看到Orders表的逻辑读取次数很高，这可能意味着需要一个索引来改善查询性能。

创建索引

SQL
CREATE NONCLUSTERED INDEX idx_CustomerID ON Orders(CustomerID);

重新分析查询

SQL
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT CustomerID, SUM(TotalAmount) AS TotalSpent
FROM Orders
GROUP BY CustomerID;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

创建索引后，再次执行查询，我们应该能看到IO成本有所下降。

`SET STATISTICS TIME ON` 返回的参数：

CPU time：查询执行过程中，SQL Server进程在CPU上花费的时间，单位是毫秒。
Elapsed time：查询执行的总时间，从开始到结束，包括处理查询、等待资源（如I/O）和实际执行查询的时间，单位是毫秒。

`SET STATISTICS IO ON` 返回的参数：

Scan count：对表或索引进行扫描的次数。高的扫描次数可能意味着查询优化的空间。
Logical reads：从数据缓存中读取的页数，而不是直接从磁盘读取。这是衡量查询消耗内存资源的一个指标。
Physical reads：直接从磁盘读取的页数。如果这个数字很高，可能意味着数据没有被缓存，或者缓存效率不高。
Read-ahead reads：SQL Server预读机制读取的页数。这个机制会在处理当前数据的同时预先从磁盘读取可能接下来需要的数据页。
LOB logical/physical/read-ahead reads：针对大型对象（如text, ntext, image, xml, large varchar, nvarchar等）的逻辑读取、物理读取和预读操作的数量。

对于SET STATISTICS IO ON返回的参数，如果Logical reads的数字较高，但Physical reads较低或为零，这通常意味着查询得到了很好的缓存，并且没有产生过多的磁盘I/O操作，这对性能来说是一个好消息。如果Physical reads的数字很高，这可能意味着需要考虑查询优化或索引优化，或者可能是内存不足导致缓存效率低下。

结论

通过使用SET STATISTICS TIME和SET STATISTICS IO，我们可以获取关于查询执行的详细时间和IO统计信息，这些信息对于查询优化是非常有用的。在实践中，我们应该利用这些工具来诊断性能问题，并通过适当的索引和查询重写来提高性能。

目录