例如:
执行计划显示没有明显的写原因,例如哈希表,假脱机或可能溢出到TempDB的排序:
对MAX类型的变量赋值或自动更新统计信息也可能导致这种情况,但在这种情况下,写入的原因都不是。
写入的其他来源是什么?
#1 楼
笨拙我不记得我是否在原始答案中包含了这些内容,所以这是另一对。
假脱机!
SQL Server有许多不同的假脱机,它们是临时数据结构,存储在tempdb中。表和索引假脱机是两个示例。
当它们出现在查询计划中时,对这些假脱机的写入将与查询相关联。
这些也将在DMV,概要分析器,XE等中被注册为写操作。 />
显然,执行的写操作量与假脱机数据的大小有关。
溢出
当SQL Server没有为某些操作员获得足够的内存时,它可能会将某些页面溢出到磁盘上。这主要发生在排序和散列上。您可以在实际的执行计划中看到这一点,并且在SQL Server的较新版本中,dm_exec_query_stats中也会跟踪溢出情况。
跟踪
您可以使用与上面在我自己的演示中看到的类似的XE会话。
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;
#2 楼
在某些情况下,查询存储可能会由于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
<
默认情况下,Query Store的统计信息收集作业将在下一个小时的顶部写出数据。这似乎(有时?)是在一个小时内执行的第一个用户查询的一部分。扩展事件会话将显示类似以下内容的内容:
事务日志显示已发生的写操作:
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);
请注意,日志条目显示三个嵌套的事务,但只有两个提交记录。在生产中的类似情况下,这导致了一个可能有问题的客户端库,该客户端库使用隐式事务意外启动了写事务,从而阻止了事务日志的清除。该库被编写为仅在运行更新,插入或删除语句后才发出提交,因此它从不发出提交命令,并且未打开写事务。
#3 楼
还有一次可能会发生这种情况,并且会自动更新统计信息。这是我们要关注的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会议的一些有趣结果:
自动统计信息更新不会显示任何写入,但查询会在统计信息更新后立即显示一个写入。