在聚集列存储表上构建统计信息似乎总是读取整个表,即使我要求一个小样本。为什么是这样?
Paul White's questions
当我在 SQL Server 中使用大对象 (LOB) 数据类型的变量时,整个内容是否始终保存在内存中?即使它是2GB的大小?
在阅读了 Josh Darnell 的Unusual THREADPOOL Waits之后,一位 Twitter 用户提到有一个未记录的跟踪标志来防止修剪空闲的工作人员:
这个想法是,一旦 SQL Server 创建了足够的线程来为峰值工作负载提供服务,它不应该在 15 分钟左右不需要它们之后修剪工作线程(将它们释放到操作系统)。
空闲的工作线程将继续使用资源(例如内存),THREADPOOL
但当突然需要更多工作人员时不会出现等待的爆发。显然,这在使用 Always On 可用性组时会有所帮助。
这个未记录的跟踪标志是什么,它是如何工作的?
如果在可用性组中的主服务器上强制执行计划,它是否应用于在辅助服务器上运行的查询?
我正在寻找涵盖计划强制的两种可能性的答案:
我已阅读以下内容,这些内容表明 QS 强制计划不会延续,但在文档中找不到任何权威性内容或有关计划指南的任何内容。
- Erin Stellato 的查询存储和可用性组
- Vikas Rana在 AlwaysOn 可读辅助节点上查询数据存储强制计划行为
强制的确凿证据将是辅助节点的执行计划中存在Use Plan
orPlanGuideName
和PlanGuideDB
属性。
聊天讨论中出现的一个问题:
我知道哈希连接救助会在内部切换到一种嵌套循环的东西。
SQL Server 对散列聚合救助做了什么(如果它可能发生的话)?
在回答SQL 计算不同分区时, Erik Darling 发布了此代码以解决以下问题COUNT(DISTINCT) OVER ()
:
SELECT *
FROM #MyTable AS mt
CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc
FROM #MyTable AS mt2
WHERE mt2.Col_A = mt.Col_A
-- GROUP BY mt2.Col_A
) AS ca;
查询使用CROSS APPLY
(not OUTER APPLY
) 那么为什么执行计划中有外连接而不是内连接?
另外,为什么取消注释 group by 子句会导致内部联接?
我认为数据并不重要,但复制了 kevinwhat 在另一个问题上给出的数据:
create table #MyTable (
Col_A varchar(5),
Col_B int
)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)
给定以下具有 400 行编号从 1 到 400 的堆表:
DROP TABLE IF EXISTS dbo.N;
GO
SELECT
SV.number
INTO dbo.N
FROM master.dbo.spt_values AS SV
WHERE
SV.[type] = N'P'
AND SV.number BETWEEN 1 AND 400;
以及以下设置:
SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
以下SELECT
语句在大约6 秒内完成(demo、plan):
DECLARE @n integer = 400;
SELECT
c = COUNT_BIG(*)
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE
N.number <= @n
AND N2.number <= @n
AND N3.number <= @n
OPTION
(OPTIMIZE FOR (@n = 1));
注意:@OPTIMIZE FOR
该条款只是为了生成一个合理大小的重现,以捕获实际问题的基本细节,包括可能由于各种原因而出现的基数错误估计。
DECLARE @T table (c bigint NOT NULL);
DECLARE @n integer = 400;
INSERT @T
(c)
SELECT
c = COUNT_BIG(*)
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE
N.number <= @n
AND N2.number <= @n
AND N3.number <= @n
OPTION
(OPTIMIZE FOR (@n = 1));
除了插入一行之外,执行计划看起来完全相同。
所有额外的时间似乎都被 CPU 使用率消耗掉了。
为什么INSERT
声明这么慢?
以下是查询存储遇到的性能问题的简化:
CREATE TABLE #tears
(
plan_id bigint NOT NULL
);
INSERT #tears (plan_id)
VALUES (1);
SELECT
T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
ON QSP.plan_id = T.plan_id;
该plan_id
列被记录为 的主键sys.query_store_plan
,但执行计划没有像预期的那样使用连接消除:
- DMV 没有投影任何属性。
- DMV 主键
plan_id
不能复制临时表中的行 - 使用了 A ,因此无法删除
LEFT JOIN
任何行。T
这是为什么,如何才能在此处获得连接消除?
以纤程模式(轻量池)运行 SQL Server 会禁用 SQL CLR:
轻量级池不支持公共语言运行时 (CLR) 执行。禁用以下两个选项之一:“clr enabled”或“lightweight pooling”。依赖 CLR 且在纤程模式下无法正常工作的功能包括层次结构数据类型、复制和基于策略的管理。
另一方面,单独禁用 SQL CLR(不启用轻量级池)不会禁用内置 CLR 类型,如geometry
, 和geography
(尽管hierarchyid
上面提到过),如禁用“CLR”时“HierarchyID”类型如何工作中所示?
现在一些新的语言特性依赖于 CLR ,FORMAT
例如函数:
FORMAT 依赖于 .NET Framework 公共语言运行时 (CLR) 的存在。
以光纤模式运行 SQL Server 是否会禁用该FORMAT
功能和/或使用 CLR 类型?
对于下面的AdventureWorks示例数据库查询:
SELECT
P.ProductID,
CA.TransactionID
FROM Production.Product AS P
CROSS APPLY
(
SELECT TOP (1)
TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE
TH.ProductID = P.ProductID
ORDER BY
TH.TransactionID DESC
) AS CA;
执行计划显示Index Seek的Estimated Operator Cost为0.0850383 (93%) :
成本与使用的基数估计模型无关。
它不是Estimated CPU Cost和Estimated I/O Cost的简单相加。它也不是一次执行Index Seek的成本乘以Estimated Number of Executions。
这个成本数字是如何得出的?
以有效并行度 x
运行的 SQL Server 查询是否有可能将并行工作程序分配给多个x
不同的调度程序?即使执行计划有很多并行区域?
给定下表、唯一聚集索引和统计信息:
CREATE TABLE dbo.Banana
(
pk integer NOT NULL,
c1 char(1) NOT NULL,
c2 char(1) NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX pk ON dbo.Banana (pk);
CREATE STATISTICS c1 ON dbo.Banana (c1);
CREATE STATISTICS c2 ON dbo.Banana (c2);
INSERT dbo.Banana
(pk, c1, c2)
VALUES
(1, 'A', 'W'),
(2, 'B', 'X'),
(3, 'C', 'Y'),
(4, 'D', 'Z');
-- Populate statistics
UPDATE STATISTICS dbo.Banana;
统计行修改计数器在任何更新之前显然显示为零:
-- Show statistics modification counters
SELECT
stats_name = S.[name],
DDSP.stats_id,
DDSP.[rows],
DDSP.modification_counter
FROM sys.stats AS S
CROSS APPLY sys.dm_db_stats_properties(S.object_id, S.stats_id) AS DDSP
WHERE
S.[object_id] = OBJECT_ID(N'dbo.Banana', N'U');
pk
将每行的每列值递增1:
-- Increment pk in every row
UPDATE dbo.Banana
SET pk += 1;
使用执行计划:
它产生以下统计修改计数器:
问题
- 拆分、排序和折叠运算符有什么作用?
- 为什么
pk
统计数据显示 2 个修改,但c1
显示c2
5 个?
在 SQL Server 中,行存储表上的非唯一非聚集索引在非聚集索引结构的所有级别合并了基础对象的书签(RID 或聚集键)。书签作为非聚集索引键的一部分存储在所有索引级别。
另一方面,如果非聚集索引是唯一的,则书签仅存在于索引的叶级别 - 不作为键的一部分(实际上,书签作为一个或多个包含列存在)。
在 SQL Server 2016 中,可以在面向列的表(具有聚集列存储索引的表)上构建非聚集 b 树索引。
- 用于聚集列存储表上的非聚集 b 树索引的“书签”是什么?
- 上面描述的唯一和非唯一非聚集索引之间的差异是否仍然适用?
多语句表值函数在表变量中返回其结果。
这些结果是否曾经被重用,或者每次调用时总是对函数进行全面评估?
众所周知,SCHEMABINDING
一个函数可以避免更新计划中不必要的假脱机:
如果您使用不涉及任何表(即不访问数据)的简单 T-SQL UDF,请确保
SCHEMABINDING
在创建 UDF 期间指定选项。这将使 UDF 绑定模式并确保查询优化器不会为涉及这些 UDF 的查询计划生成任何不必要的假脱机运算符。
SCHEMABINDING
即使函数不访问数据,还有其他优点吗?
以下是读取相同数据但报告非常不同的逻辑读取的三个简单测试:
设置
以下脚本创建了一个包含 100 个相同行的测试表,每个行都包含一个xml列,其中包含足够的数据以确保它存储在行外。在我的测试数据库中,生成的xml的长度为每行 20,204 字节。
-- Conditional drop
IF OBJECT_ID(N'dbo.XMLTest', N'U') IS NOT NULL
DROP TABLE dbo.XMLTest;
GO
-- Create test table
CREATE TABLE dbo.XMLTest
(
ID integer IDENTITY PRIMARY KEY,
X xml NULL
);
GO
-- Add 100 wide xml rows
DECLARE @X xml;
SET @X =
(
SELECT TOP (100) *
FROM sys.columns AS C
FOR XML
PATH ('row'),
ROOT ('root'),
TYPE
);
INSERT dbo.XMLTest
(X)
SELECT TOP (100)
@X
FROM sys.columns AS C;
-- Flush dirty buffers
CHECKPOINT;
测试
以下三个测试读取xml列:
- 一个简单的
SELECT
声明 - 将xml分配给变量
- 用于
SELECT INTO
创建临时表
-- No row count messages or graphical plan
-- Show I/O statistics
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SET STATISTICS IO ON;
GO
PRINT CHAR(10) + '=== Plain SELECT ===='
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT XT.X
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== Assign to a variable ===='
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
DECLARE @X xml;
SELECT
@X = XT.X
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== SELECT INTO ===='
IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL
DROP TABLE #T;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT
XT.X
INTO #T
FROM dbo.XMLTest AS XT
GO
SET STATISTICS IO OFF;
结果
输出是:
=== 普通选择 ==== 表“XMLTest”。扫描计数 1,逻辑读取 3,物理读取 1,预读读取 0, lob 逻辑读取 795,lob 物理读取 37,lob 预读读取 796。 === 赋值给一个变量 ==== 表“XMLTest”。扫描计数 1,逻辑读取 3,物理读取 1,预读读取 0, lob 逻辑读取 0,lob 物理读取 0,lob 预读读取 0。 === 选择进入 ==== 表“XMLTest”。扫描计数 1,逻辑读取 3,物理读取 1,预读读取 0, lob 逻辑读取 300,lob 物理读取 37,lob 预读读取 400。
问题
- 为什么 LOB 读取如此不同?
- 肯定在每次测试中都读取了完全相同的数据吗?
我需要计算一个日期范围内的滚动总和。为了说明,使用AdventureWorks 示例数据库,以下假设语法将完全满足我的需要:
SELECT
TH.ProductID,
TH.TransactionDate,
TH.ActualCost,
RollingSum45 = SUM(TH.ActualCost) OVER (
PARTITION BY TH.ProductID
ORDER BY TH.TransactionDate
RANGE BETWEEN
INTERVAL 45 DAY PRECEDING
AND CURRENT ROW)
FROM Production.TransactionHistory AS TH
ORDER BY
TH.ProductID,
TH.TransactionDate,
TH.ReferenceOrderID;
遗憾的是,RANGE
窗框范围当前不允许 SQL Server 中的间隔。
我知道我可以使用子查询和常规(非窗口)聚合来编写解决方案:
SELECT
TH.ProductID,
TH.TransactionDate,
TH.ActualCost,
RollingSum45 =
(
SELECT SUM(TH2.ActualCost)
FROM Production.TransactionHistory AS TH2
WHERE
TH2.ProductID = TH.ProductID
AND TH2.TransactionDate <= TH.TransactionDate
AND TH2.TransactionDate >= DATEADD(DAY, -45, TH.TransactionDate)
)
FROM Production.TransactionHistory AS TH
ORDER BY
TH.ProductID,
TH.TransactionDate,
TH.ReferenceOrderID;
给定以下索引:
CREATE UNIQUE INDEX i
ON Production.TransactionHistory
(ProductID, TransactionDate, ReferenceOrderID)
INCLUDE
(ActualCost);
执行计划是:
虽然不是非常低效,但似乎应该可以仅使用 SQL Server 2012、2014 或 2016(到目前为止)支持的窗口聚合和分析函数来表达此查询。
为清楚起见,我正在寻找一种对数据执行单次传递的解决方案。
在 T-SQL 中,这很可能意味着子句将OVER
完成工作,而执行计划将包含 Window Spools 和 Window Aggregates。所有使用该OVER
子句的语言元素都是公平的游戏。SQLCLR 解决方案是可以接受的,只要它保证产生正确的结果。
对于 T-SQL 解决方案,执行计划中的哈希、排序和窗口假脱机/聚合越少越好。随意添加索引,但不允许使用单独的结构(例如,没有预先计算的表与触发器保持同步)。允许参考表(数字表、日期表等)
理想情况下,解决方案将以与上述子查询版本相同的顺序产生完全相同的结果,但任何可以说是正确的也是可以接受的。性能始终是一个考虑因素,因此解决方案至少应该是相当有效的。
专用聊天室:我创建了一个公共聊天室,用于与此问题及其答案相关的讨论。任何拥有至少 20 个声望点的用户都可以直接参与。如果您的代表少于 20 并且想参加,请在下面的评论中联系我。
我经常需要从结果集中的每个组中选择一些行。
例如,我可能想列出每个客户最近的“n”个最高或最低的订单值。
在更复杂的情况下,要列出的行数可能因组而异(由分组/父记录的属性定义)。这部分绝对是可选的/额外的功劳,并不是为了阻止人们回答。
在 SQL Server 2005 及更高版本中解决这些类型问题的主要选项是什么?每种方法的主要优点和缺点是什么?
AdventureWorks 示例(为清楚起见,可选)
- 列出表中最近的五个交易日期和 ID
TransactionHistory
,每个产品以字母从 M 到 R(含)开头。 - 再次相同,但
n
每个产品都有历史行,其中是Product 属性n
的五倍。DaysToManufacture
- 同样,对于每个产品都需要一个历史行的特殊情况(最近的单个条目
TransactionDate
, tie-break onTransactionID
.