主页 / dba / 问题 / 184706
Accepted
user1716729
Asked: 2017-08-31 06:03:58 +0800 CST

矛盾检测

  • 772

我正在尝试学习表分区。但是，我很难理解为什么 SQL Server 会执行一个简单查询的阶段。

测试数据集

   /* --------------------------------------------------
-- Create helper function GetNums by Itzik Ben-Gan
-- http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
-- GetNums is used to insert test data
-------------------------------------------------- */

-- Drop helper function if it already exists
IF OBJECT_ID('GetNums') IS NOT NULL
    DROP FUNCTION GetNums;
GO

-- Create helper function
CREATE FUNCTION GetNums(@n AS BIGINT) RETURNS TABLE AS RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
  SELECT TOP (@n) n FROM Nums ORDER BY n;
GO

/* ------------------------------------------------------------
-- Create example Partitioned Table (Heap)
-- The Partition Column is a DATE column
-- The Partition Function is RANGE RIGHT
-- The Partition Scheme maps all partitions to [PRIMARY]
------------------------------------------------------------ */

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'Sales')
    DROP TABLE Sales;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
    DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
    DROP PARTITION FUNCTION pfSales;

-- Create the Partition Function 
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE right FOR VALUES 
('2013-01-01', '2014-01-01', '2015-01-01');

-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales 
ALL TO ([Primary]);

-- Create the Partitioned Table (Heap) on the Partition Scheme
CREATE TABLE Sales 
(
    SalesDate DATE constraint ck_date check(SalesDate<'2016-01-01'),
    Quantity INT
) ON psSales(SalesDate);

-- Insert test data
INSERT INTO Sales(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

查询

select count(1) from sales where SalesDate>'20160101'

请看下面的执行计划。

执行计划

问题

  1. 为什么 SQL 做 CONVERT_IMPLICIT
  2. 为什么 SQL 没有做矛盾检测来避免表扫描。

非常感谢

sql-server sql-server-2016

2 个回答

  1. 您将获得一个用于简单查询的简单参数化计划。

    添加AND 1 = (SELECT 1);到查询的末尾，您将获得持续的扫描/矛盾检测。

    在此处输入图像描述

    如果目的也是为了避免琐碎的计划，那么使用 justAND 1 = 1并不总是有效。请参阅Erik Darling 的查询计划：简单优化与简单参数化。

    您还应该养成注意数据类型的习惯。字符串不是日期，并且没有特殊的 T-SQL 语法来指定日期文字。您可以使用CONVERT明确的方式，例如SalesDate > CONVERT(date, '20160101', 112).

    相关阅读：为什么分区消除不起作用？保罗怀特。

    • 9
  2. Best Answer

    为什么 SQL 做 CONVERT_IMPLICIT

    因为您在查询中有一个文字需要解析并转换为日期。这不是性能问题。

    SQL 没有进行矛盾检测以避免表扫描。

    没有索引，表扫描是不可避免的。但是，正如您从表扫描运算符属性中看到的那样，使用了查找，因此仅SalesDate扫描包含指定值的分区。STATISTICS IO ON使用和不使用子句运行查询WHERE以查看分区消除的影响：

    SELECT COUNT(1) FROM sales WHERE SalesDate>'20160101';
Table 'Sales'. Scan count 1, logical reads 722, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT COUNT(1) FROM sales;
Table 'Sales'. Scan count 4, logical reads 2890, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server 2008 中引入了分区感知查找操作。以前（SQL 2005），分区消除是通过单独的执行计划运算符完成的。

    编辑 我刚刚意识到主要问题主要是关于检查约束而不是分区边界。我认为这个答案仍然提供了一些价值，所以我不会删除它。

    • 4

相关问题