顺德网站制作公司,广州市 网站建设 有限公司,百度地图网站开发,黑帽seo怎么做网站排名基数预估是SQL Server里一颗隐藏的宝石。一般而言#xff0c;基数预估指的是#xff0c;在查询编译期间#xff0c;查询优化器尝试找出在执行计划里从各个运算符平均返回的行数。这个估计用来驱动计划本身生成并选择正确的计划运算符——例如像Nested Loop, Merge Join,还是… 基数预估是SQL Server里一颗隐藏的宝石。一般而言基数预估指的是在查询编译期间查询优化器尝试找出在执行计划里从各个运算符平均返回的行数。这个估计用来驱动计划本身生成并选择正确的计划运算符——例如像Nested Loop, Merge Join,还是Hash Join的物理连接。当这些估计错误时查询优化器就会选择错误的计划运算符相信我——你的查询就会非常非常非常慢 查询优化器使用称为统计信息对象作为基数预估。每次当你创建一个索引SQL Server在下面也会创建一个统计对象。这个对象描述了那个索引的数据分布。另外在查询执行时SQL Server也能创建统计信息对象在必须的时候自动创建统计信息。数据分布本身复合索引键的第一列被描述为所谓的直方图Histogram。 直方图最痛苦之一就是最大只有200的步长。步长是对于你所给定列数据一部分的数据分布情况描述。你的表变得越大你的直方图就越不准确因为你有最大200的步长直方图必须尽可能紧凑它必须复核8kb的页。 在复合索引键里其他列SQL Server在统计信息对象里用所谓的密度向量Density Vector来保存它是复合索引键唯一值是如何的情况描述彼此结合在一起。例如在某列里有3个不同值那列的密度向量是0.333331/3。 从SQL Server 2008开始SQL Server支持所谓的过滤统计信息Filtered Statistics和过滤索引对应。使用过滤统计信息你可以为数据的子集创建统计信息对象。对于那个数据子集你也会有直方图和密度向量。如果在你的数据里有极端值你可以对那个范围的数据创建过滤统计信息对象当那个范围的数据被查询时就可以让查询优化器更好的估计返回的行数。因此使用过滤统计信息你就提高了基数预估的准确性SQL Server就会给更好的执行计划性能。下面代码显示在SQL Server 2008及后续版本里如何创建过滤统计信息对象 1 CREATE STATISTICS Country_Austria ON Country(ID)
2 WHERE Name Austria
3 GO 从上面代码可以看到你用WHERE子句限制表数据的子集那会通过新的过滤统计信息对象来描述这些数据。但也只有的你的查询也包含这个where条件查询优化器才可以只用这个新的统计信息对象就像这样 1 SELECT SalesAmount FROM Country
2 INNER JOIN Orders ON Country.ID Orders.ID
3 WHERE Name Austria
4 GO 如果在的查询里并不包含同样的WHERE子句查询优化期在执行计划里访问的索引的统计信息还是原来默认的。如果你对刚才的查询启用9204的跟踪标记你就可以看到在基数预估时那个统计信息被查询优化器使用 1 SELECT SalesAmount FROM Country2 INNER JOIN Orders ON Country.ID Orders.ID3 WHERE Name Austria4 OPTION5 (6 RECOMPILE,-- Used to see the Statistics Output7 QUERYTRACEON 3604,-- Redirects the output to SSMS8 QUERYTRACEON 9204 -- Returns the Statistics that were used during Cardinality Estimation (Stats loaded)9 )
10 GO 查询本身也会编译因为RECOMPLIE查询提示即使查询计划已被缓存因此在SSMS的消息窗你就可以看到拿个统计信息被用做基数预估。 以过滤统计信息的简单介绍为基础我想给你通过实例展示下过滤统计信息是如何提高执行计划质量的。 1 -- Create a new database2 CREATE DATABASE FilteredStatistics3 GO4 5 -- Use it6 USE FilteredStatistics7 GO8 9 -- Create a new table
10 CREATE TABLE Country
11 (
12 ID INT PRIMARY KEY,
13 Name VARCHAR(100)
14 )
15 GO
16
17 -- Create a new table
18 CREATE TABLE Orders
19 (
20 ID INT,
21 SalesAmount DECIMAL(18, 2)
22 )
23 GO 我们在表上建立相应的索引 1 -- Create a Non-Clustered Index
2 CREATE NONCLUSTERED INDEX idx_Name ON Country(Name)
3 GO
4
5 -- Create a Clustered Index
6 CREATE CLUSTERED INDEX idx_ID_SalesAmount ON Orders(ID, SalesAmount)
7 GO 最后往2个表里插入初始数据 1 -- Insert a few records into the Lookup Table2 INSERT INTO Country VALUES(0, Austria) 3 INSERT INTO Country VALUES(1, UK)4 INSERT INTO Country VALUES(2, France) 5 GO6 7 -- Insert uneven distributed order data8 INSERT INTO Orders VALUES(0, 0)9
10 DECLARE i INT 1
11
12 WHILE i 1000
13 BEGIN
14 INSERT INTO Orders VALUES (1, i)
15 SET i 1
16 END
17 GO 为了保证所有的统计信息都已经是最新的我用全扫描更新了统计信息 1 -- Update the Statistics on both tables
2 UPDATE STATISTICS Country WITH FULLSCAN
3 UPDATE STATISTICS Orders WITH FULLSCAN
4 GO 点击工具栏的显示包含实际的执行计划。我们来执行下列的查询 1 SELECT SalesAmount FROM Country2 INNER JOIN Orders ON Country.ID Orders.ID3 WHERE Name UK4 OPTION5 (6 RECOMPILE,-- Used to see the Statistics Output7 QUERYTRACEON 3604,-- Redirects the output to SSMS8 QUERYTRACEON 9204-- Returns the Statistics that were used during Cardinality Estimation (Stats loaded)9 )
10 GO 从执行计划里可以看到基数预估出现了大问题。 SQL Server 估计行数是501聚集索引查找运算符的实际行数是1000。SQL Server这里使用idx_ID_SalesAmount统计信息对象的密度向量来做那个估计密度向量是0.5在那列我们只有2个不同值因此估计行数是5011001 * 0.5。 当你用Austria参数值执行同样的查询SQL Server又一次估计行数是501但是查询本身值返回1行……当其他运算符使用这些估计做运算时这个行为在执行计划里会有巨大的副作用。例如Sort和Hash运算符根据这些估计作为内存授予需要的大小。如果低估你的查询会涌向TempDb如果高估你就在浪费内存当你有大量的并发查询是就会导致竞争问题查询内存的最大数量是有资源管理器限制的…… 你可以使用过滤统计信息来帮助这些特殊场景。这个会给SQL Server关于数据本身分布的更多信息也会在基数预估里得到帮助。对于那个特殊场景我创建2个不同的过滤统计信息对于每个国家我都创建各自的过滤统计信息对象 1 -- Fix the problem by creating Filtered Statistics Objects
2 CREATE STATISTICS Country_UK ON Country(ID)
3 WHERE Name UK
4
5 CREATE STATISTICS Country_Austria ON Country(ID)
6 WHERE Name Austria
7 GO 现在当你重新执行查询时最后你会看到基数预估是正确的 当你在你表上上创建了过滤统计信息时你也要注意维护。从整个表本身——如果有20%的数据改变时SQL Server会自动更新统计信息 假设你有10000行的表你在表的子集上创建了过滤统计信息就定子集行数是500条。在这个情况下当指定列有2000行改变时SQL Server会更新过滤统计信息对象。因此你要更新过滤统计信息对象里4倍的数据才会使统计信息失效然后它被更新在过滤统计信息区间外没有数据发生改变。这是很糟糕的情况当你使用过滤统计信息时要记住这个。 希望这篇文章给你过滤统计信息的很好概述对于给出的查询你知道如何使用过滤统计信息帮助SQL Server提高基数预估。 感谢关注 参考文章 https://www.sqlpassion.at/archive/2013/10/29/fixing-cardinality-estimation-errors-with-filtered-statistics/ 转载于:https://www.cnblogs.com/woodytu/p/4618818.html
