低统计逻辑读取和高分析器读取 [英] Low Statistics Logical Reads and High Profiler Reads

问题描述

我在SSMS中执行了以下程序：

I had executed the following procedure in SSMS:

CREATE Procedure [dbo].[MyTableLoadByPK]  
(  
    @MyTableID int  
)  
AS  
BEGIN  
    SET NOCOUNT ON  
    SELECT  
    *  
    FROM [MyTable]  
    WHERE  
    del = 0 AND MyTableID = @MyTableID 
END  

exec MyTableLoadByPK @MyTableID=1001

MyTable 有40000条记录， MyTableID 上有聚集索引。

MyTable is having 40000 records and there is a clustered index on MyTableID.

每次调用上述程序时，STATISTICS IO都相同逻辑读取2，物理读取2 ：

The STATISTICS IO were same every time I called the above procedure logical reads 2, physical reads 2:

Table 'MyTable'. Scan count 0, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

但当我在Profiler中检查跟踪相同的程序时，我发现读数差异很大甚至差异。一次是248次，三次是228次。

But when I checked the trace in Profiler for the same procedure, I found a huge difference in reads and even difference in difference. One time 248 and three times 228.

以下是从跟踪中读取的内容：

Following are reads from the trace:

TextData                                    CPU  Reads   Writes    Duration

exec MyTableLoadByPK @MyTableID=1001        0    228     0         223
exec MyTableLoadByPK @MyTableID=1001        0    228     0         230
exec MyTableLoadByPK @MyTableID=1001        0    248     0         751
exec MyTableLoadByPK @MyTableID=1001        0    228     0         360

注意：我已经执行了 DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE; 每四次执行之前。

Note: I had executed DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE; before each four executions.

SSMS和Profiler中的执行计划相同。只有一个Clustered Index Seek。成本，行数，执行次数，大小在两者中都是一样的，那么为什么读数有这么大差异。

The execution plan is same in both SSMS and Profiler. There is only one Clustered Index Seek. Cost, rows, executions, size everything is same in both, then why there is so much difference in reads.

编辑：

MyTable create语句如下：

MyTable create statement is as following:

CREATE TABLE [dbo].[MyTable](
    [MyTableID] [int] IDENTITY(1,1) NOT NULL,
    [na] [nvarchar](255) NOT NULL,
    [conID] [int] NOT NULL,
    [coID] [int] NOT NULL,
    [sID] [int] NOT NULL,
    [coID_O] [int] NOT NULL,
    [sID_O] [int] NOT NULL,
    [ufmts] [bit] NOT NULL,
    [Lte] [float] NOT NULL,
    [Late] [float] NOT NULL,
    [tz] [nvarchar](20) NOT NULL,
    [dm] [int] NOT NULL,
    [ca] [nvarchar](20) NOT NULL,
    [Tt] [nvarchar](50) NOT NULL,
    [Ct] [nvarchar](2048) NOT NULL,
    [pub] [bit] NOT NULL,
    [do] [int] NOT NULL,
    [cuID] [int] NOT NULL,
    [ia] [nvarchar](50) NOT NULL,
    [con] [datetime] NOT NULL,
    [uon] [datetime] NOT NULL,
    [upc] [int] NOT NULL,
    [del] [bit] NOT NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [MyTableID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MyTable]  WITH CHECK ADD  CONSTRAINT [FK_MyTable_con] FOREIGN KEY([conID])
REFERENCES [dbo].[MYcon] ([conID])
GO

ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_Mycon]
GO

ALTER TABLE [dbo].[MyTable]  WITH CHECK ADD  CONSTRAINT [FK_MyTable_Myco] FOREIGN KEY([coID])
REFERENCES [dbo].[Myco] ([coID])
GO

ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_Myco]
GO

ALTER TABLE [dbo].[MyTable]  WITH CHECK ADD  CONSTRAINT [FK_MyTable_Mys] FOREIGN KEY([sID])
REFERENCES [dbo].[Mys] ([sID])
GO

ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_Mys]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_upc]  DEFAULT ((0)) FOR [upc]
GO

推荐答案

您不应期望看到逻辑读取的值相同在这两种情况下（ SET STATISTICS IO ON 和 SQL事件探查器：批量完成）因为它们有不同的衡量标准：

You should not expect to see the same values for logical reads in these two cases (SET STATISTICS IO ON and SQL Profiler:Batch Completed) because they have measure different things:

• SET STATISTICS IO ON 将返回逻辑读取仅用于语句[s]，


• SQL事件探查器：批处理完成列读取将返回整批包括这里通过编译生成的逻辑读取（您的批处理具有强制编译的DBCC FREEPROCCACHE语句，这意味着逻辑读取，因为SQL Server必须读取元数据信息）。此外，如果您激活查询> SQL Server Management Studio中的实际执行计划您将看到读取列的增加。）

• SET STATISTICS IO ON will return logical reads for statement[s] only,
• SQL Profiler:Batch Completed column Reads will return logical reads for entire batch including here logical reads generated by compilation (your batch has a DBCC FREEPROCCACHE statement which force a compilation and this means logical reads because SQL Server have to read metadata information). Also, if you activate Query > Actual execution plan in SQL Server Management Studio you will see an increase in Reads column).

例如，当我在SSMS中运行这两个批次时

For example, when I run these the two batches in SSMS

-- Without Query > Actual Execution Plan
DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE;
exec MyTableLoadByPK @MyTableID=1001
GO
-- With Query > Actual Execution Plan
DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE;
exec MyTableLoadByPK @MyTableID=1001
GO

<$ c的输出$ c> SET STATISTICS IO ON 0逻辑读取

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'MyTable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'MyTable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

但是 SQL Profiler 显示批量完成事件另一个结果在读取列：

but SQL Profiler shows for Batch completed event another results in Reads column:

TLDR：
简单的解释是你试图比较不同的东西。

TLDR: Simple explanation is that you're trying to compare different things.

这篇关于低统计逻辑读取和高分析器读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！