主页 / dba / 问题 / 191825
Accepted
James Lupolt
Asked: 2017-11-28 10:31:43 +0800 CST

为什么 SELECT 查询会导致写入？

  • 772

我注意到在运行 SQL Server 2016 SP1 CU6 的服务器上，扩展事件会话有时会显示 SELECT 查询导致写入。例如：

在此处输入图像描述

执行计划没有显示写入的明显原因，例如可能溢出到 TempDB 的哈希表、假脱机或排序：

在此处输入图像描述

对 MAX 类型的变量分配或自动统计更新也可能导致这种情况，但在这种情况下也不是写入的原因。

还有什么可以写的？

sql-server sql-server-2016

3 个回答

  1. 在某些情况下，查询存储可能会导致写入作为 select 语句的效果发生，并且发生在同一会话中。

    这可以复制如下：

    USE master;
GO
CREATE DATABASE [Foo];
ALTER DATABASE [Foo] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, 
  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), 
  DATA_FLUSH_INTERVAL_SECONDS = 900, 
  INTERVAL_LENGTH_MINUTES = 60, 
  MAX_STORAGE_SIZE_MB = 100, 
  QUERY_CAPTURE_MODE = ALL, 
  SIZE_BASED_CLEANUP_MODE = AUTO);
USE Foo;
CREATE TABLE Test (a int, b nvarchar(max));
INSERT INTO Test SELECT 1, 'string';

    创建一个扩展事件会话进行监控：

    CREATE EVENT SESSION [Foo] ON SERVER 
ADD EVENT sqlserver.rpc_completed(SET collect_data_stream=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.is_system,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.sql_text)
    WHERE ([writes]>(0))),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_name,sqlserver.is_system,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_server_principal_name,sqlserver.sql_text)
    WHERE ([writes]>(0)))
ADD TARGET package0.event_file(SET filename=N'C:\temp\FooActivity2016.xel',max_file_size=(11),max_rollover_files=(999999))
WITH (MAX_MEMORY=32768 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF);

    接下来运行以下命令：

    WHILE @@TRANCOUNT > 0 COMMIT
SET IMPLICIT_TRANSACTIONS ON;
SET NOCOUNT ON;
GO
DECLARE @b nvarchar(max);
SELECT @b = b FROM dbo.Test WHERE a = 1;
WAITFOR DELAY '00:00:01.000';
GO 86400

    隐式交易可能需要也可能不需要重现此内容。

    默认情况下，查询存储的统计收集作业将在下一小时的顶部写出数据。这似乎（有时？）作为在一小时内执行的第一个用户查询的一部分发生。扩展事件会话将显示类似于以下内容：

    在此处输入图像描述

    事务日志显示已发生的写入：

    USE Foo;
SELECT [Transaction ID], [Begin Time], SPID, Operation, 
  [Description], [Page ID], [Slot ID], [Parent Transaction ID] 
FROM sys.fn_dblog(null,null) 
/* Adjust based on contents of your transaction log */
WHERE [Transaction ID] IN ('0000:0000042c', '0000:0000042d', '0000:0000042e')
OR [Parent Transaction ID] IN ('0000:0000042c', '0000:0000042d', '0000:0000042e')
ORDER BY [Current LSN];

    在此处输入图像描述

    检查页面DBCC PAGE显示写入内容是sys.plan_persist_runtime_stats_interval.

    USE Foo;
DBCC TRACEON(3604); 
DBCC PAGE(5,1,344,1); SELECT
OBJECT_NAME(229575856);

    请注意，日志条目显示三个嵌套事务，但只有两个提交记录。在生产中的类似情况下，这导致了一个可以说是错误的客户端库，它使用隐式事务意外地启动了一个写入事务，从而阻止了事务日志的清除。该库被编写为仅在运行更新、插入或删除语句后才发出提交，因此它从未发出过提交命令并保持写入事务处于打开状态。

    • 39

  2. 还有一次可能会发生这种情况，那就是自动统计更新。

    这是我们将要查看的 XE 会话：

    CREATE EVENT SESSION batches_and_stats
    ON SERVER
    ADD EVENT sqlserver.auto_stats
    ( ACTION ( sqlserver.sql_text )),
    ADD EVENT sqlserver.sql_batch_completed
    ( ACTION ( sqlserver.sql_text ))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\batches_and_stats' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 30 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = OFF,
           STARTUP_STATE = OFF );
GO

    然后我们将使用它来收集信息：

    USE tempdb

DROP TABLE IF EXISTS dbo.SkewedUp

CREATE TABLE dbo.SkewedUp (Id INT NOT NULL, INDEX cx_su CLUSTERED (Id))

INSERT dbo.SkewedUp WITH ( TABLOCK ) ( Id )
SELECT CASE WHEN x.r % 15 = 0 THEN 1
            WHEN x.r % 5 = 0 THEN 1000
            WHEN x.r % 3 = 0 THEN 10000
            ELSE 100000
       END AS Id
FROM   (   SELECT     TOP 1000000 ROW_NUMBER() OVER ( ORDER BY @@DBTS ) AS r
           FROM       sys.messages AS m
           CROSS JOIN sys.messages AS m2 ) AS x;


ALTER EVENT SESSION [batches_and_stats] ON SERVER STATE = START

SELECT su.Id, COUNT(*) AS records
FROM dbo.SkewedUp AS su
WHERE su.Id > 0
GROUP BY su.Id

ALTER EVENT SESSION [batches_and_stats] ON SERVER STATE = STOP

    XE 会议的一些有趣结果：

    坚果

    自动统计更新不会显示任何写入，但查询会在统计更新后立即显示一次写入。

    • 26
  3. Best Answer

    笨拙

    我不记得我是否将这些包含在我的原始答案中，所以这里有另外一对。

    线轴！

    SQL Server 有许多不同的假脱机，它们是存储在 tempdb 中的临时数据结构。两个示例是表和索引线轴。

    当它们出现在查询计划中时，对这些假脱机的写入将与查询相关联。

    坚果

    这些也将在 DMV、分析器、XE 等中注册为写入。

    索引线轴

    坚果

    表线轴

    坚果

    显然，执行的写入量将随着假脱机数据的大小而增加。

    溢出

    当 SQL Server 没有为某些运算符获得足够的内存时，它可能会将某些页面溢出到磁盘。这主要发生在排序和哈希上。您可以在实际执行计划中看到这一点，并且在较新版本的 SQL Server 中，还会在dm_exec_query_stats中跟踪溢出。

    SELECT deqs.sql_handle,
       deqs.total_spills,
       deqs.last_spills,
       deqs.min_spills,
       deqs.max_spills
FROM sys.dm_exec_query_stats AS deqs
WHERE deqs.min_spills > 0;

    坚果

    坚果

    追踪

    您可以使用与我上面使用的类似的 XE 会话，在您自己的演示中查看这些会话。

    CREATE EVENT SESSION spools_and_spills
    ON SERVER
    ADD EVENT sqlserver.sql_batch_completed
    ( ACTION ( sqlserver.sql_text ))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\spools_and_spills' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 1 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = OFF,
           STARTUP_STATE = OFF );
GO
    • 12

相关问题