Sql-Server

查找表中的分區列值沒有分區消除？

  • August 27, 2019

我創建了一個分區表（如下所示），並播種了 4.8 億行——每個帳戶大約 181 行。

我在添加索引之前執行基線查詢。我驚訝地發現，即使在添加option(recompile). 分區表是這樣的嗎？在我看來，這更像是現實生活，而不是硬編碼謂詞的分區列值。

最後，如果我對此有任何疑問，我將添加索引並在此處回复。在我對這篇文章中給出的答案感到滿意之前，我不想繼續。

   --step 2 (after creating db)
   ALTER DATABASE partitionresearch
   ADD FILEGROUP January
   GO
   ALTER DATABASE partitionresearch
   ADD FILEGROUP February
   GO
   ALTER DATABASE partitionresearch
   ADD FILEGROUP March
   GO
   ALTER DATABASE partitionresearch
   ADD FILEGROUP April
   GO
   ALTER DATABASE partitionresearch
   ADD FILEGROUP May
   GO
   ALTER DATABASE partitionresearch
   ADD FILEGROUP June
   GO
   ALTER DATABASE partitionresearch
   ADD FILEGROUP July
   GO
   ALTER DATABASE partitionresearch
   ADD FILEGROUP August
   GO
   ALTER DATABASE partitionresearch
   ADD FILEGROUP September
   GO
   ALTER DATABASE partitionresearch
   ADD FILEGROUP October
   GO
   ALTER DATABASE partitionresearch
   ADD FILEGROUP November
   GO
   ALTER DATABASE partitionresearch
   ADD FILEGROUP December
   GO
   --step 3
   -- Table Partitioning in SQL Server
       ALTER DATABASE [Partitionresearch]
       ADD FILE 
       (
       NAME = [PartJan],
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartJan.ndf',
           SIZE = 5080 KB, 
           MAXSIZE = UNLIMITED, 
           FILEGROWTH = 2040 KB
       ) TO FILEGROUP [January]

       ALTER DATABASE [Partitionresearch]
       ADD FILE 
       (
       NAME = [PartFeb],
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartFeb.ndf',
           SIZE = 5080 KB, 
           MAXSIZE = UNLIMITED, 
           FILEGROWTH = 2040 KB
       ) TO FILEGROUP [February]

       ALTER DATABASE [Partitionresearch]
       ADD FILE 
       (
       NAME = [PartMar],
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartMar.ndf',
           SIZE = 5080 KB, 
           MAXSIZE = UNLIMITED, 
           FILEGROWTH = 2040 KB
       ) TO FILEGROUP [March]

       ALTER DATABASE [Partitionresearch]
       ADD FILE 
       (
       NAME = [PartApr],
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartApr.ndf',
           SIZE = 5080 KB, 
           MAXSIZE = UNLIMITED, 
           FILEGROWTH = 2040 KB
       ) TO FILEGROUP [April]

       ALTER DATABASE [Partitionresearch]
       ADD FILE 
       (
       NAME = [PartMay],
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartMay.ndf',
           SIZE = 5080 KB, 
           MAXSIZE = UNLIMITED, 
           FILEGROWTH = 2040 KB
       ) TO FILEGROUP [May]

       ALTER DATABASE [Partitionresearch]
       ADD FILE 
       (
       NAME = [PartJun],
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartJun.ndf',
           SIZE = 5080 KB, 
           MAXSIZE = UNLIMITED, 
           FILEGROWTH = 2040 KB
       ) TO FILEGROUP [June]

       ALTER DATABASE [Partitionresearch]
       ADD FILE 
       (
       NAME = [PartJul],
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartJul.ndf',
           SIZE = 5080 KB, 
           MAXSIZE = UNLIMITED, 
           FILEGROWTH = 2040 KB
       ) TO FILEGROUP [July]

       ALTER DATABASE [Partitionresearch]
       ADD FILE 
       (
       NAME = [PartAug],
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartAug.ndf',
           SIZE = 5080 KB, 
           MAXSIZE = UNLIMITED, 
           FILEGROWTH = 2040 KB
       ) TO FILEGROUP [August]

       ALTER DATABASE [Partitionresearch]
       ADD FILE 
       (
       NAME = [PartSep],
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartSep.ndf',
           SIZE = 5080 KB, 
           MAXSIZE = UNLIMITED, 
           FILEGROWTH = 2040 KB
       ) TO FILEGROUP [September]

       ALTER DATABASE [Partitionresearch]
       ADD FILE 
       (
       NAME = [PartOct],
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartOct.ndf',
           SIZE = 5080 KB, 
           MAXSIZE = UNLIMITED, 
           FILEGROWTH = 2040 KB
       ) TO FILEGROUP [October]

       ALTER DATABASE [Partitionresearch]
       ADD FILE 
       (
       NAME = [PartNov],
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartNov.ndf',
           SIZE = 5080 KB, 
           MAXSIZE = UNLIMITED, 
           FILEGROWTH = 2040 KB
       ) TO FILEGROUP [November]

       ALTER DATABASE [Partitionresearch]
       ADD FILE 
       (
       NAME = [PartDec],
       FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.mycompany2\MSSQL\DATA\PartDec.ndf',
           SIZE = 5080 KB, 
           MAXSIZE = UNLIMITED, 
           FILEGROWTH = 2040 KB
       ) TO FILEGROUP [December]

   --step 4
   -- Table Partitioning in SQL Server
   USE Partitionresearch
   GO

   CREATE PARTITION FUNCTION [MonthlyPartition] (date)
   AS RANGE RIGHT FOR VALUES ('20190201', '20190301', '20190401',
                  '20190501', '20190601', '20190701', '20190801', 
                  '20190901', '20191001', '20191101', '20191201');

   --step 5
   -- Table Partitioning in SQL Server
   USE Partitionresearch
   GO

   CREATE PARTITION SCHEME MonthWisePartition
   AS PARTITION MonthlyPartition
           TO (January, February, March, April, May, June, July, 
               August, September, October, November, December
               );
   --step 6
   create table dbo.partitionresearch 
   (
   tranid int identity(1,1),
   [Date] date,

   Account int,
   SeqNumber tinyint,
   AlertType int,
   IsFirst tinyint,
   Indicator1 int,
   [time] time
   )
   on monthwisepartition([date])

   --with partitioning help - 40 seconds (as opposed to 3 min 46 sec) , hovered over table scan and didnt see partition count, but clearly partitions (elimination) were used
   --did see scalar operators with values 5 and 10 which happens to be where these accounts are partition wise (may and october)
   use partitionresearch
   select * from dbo.partitionresearch --hoverd over and closest thing to partn help i saw were scalar operators 5 and 10
   where (date between '5/1/2019' and '5/31/2019' or date between '10/1/2019' and '10/31/2019') and
         account in (1000000,2000000) 
   ------------------------------------------------------------------------------------------------------------------------
   --with "partition help" from a lookup table--3 minutes 33 seconds
   use partitionresearch
   select a.* from dbo.partitionresearch a--hovered over and believe partns wont be used
   join [dbo].[monthlookup] b
   on a.date=b.date
   where account in (1000000,2000000) 
   ------------------------------------------------------------------------------------------------------------------------
--this is the date lookup table which isnt partitioned, thus not aligned
USE [partitionresearch]
GO

/****** Object:  Table [dbo].[monthlookup]    Script Date: 7/12/2019 6:24:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[monthlookup](
   [monthid] [int] IDENTITY(1,1) NOT NULL,
   [Date] [date] NOT NULL
) ON [PRIMARY]
GO

這在行儲存分區堆的產品中不可用。如果您將表更改為具有分區的聚集列儲存索引，那麼您有時可以通過點陣圖過濾器的行組消除來消除分區，這似乎是您所追求的。

我在這裡寫了一篇部落格。引用一小段：

我們知道，根據維度表中的數據，SQL Server 只需要從事實表中讀取兩個分區。理論上查詢優化器能比它做得更好嗎？考慮一個分區表最多有 15000 個分區的事實。所有分區值都不能重疊，並且在沒有 DDL 操作的情況下它們不會更改。在建構雜湊表時，查詢優化器可以跟踪哪些分區中至少有一行。在雜湊建構結束時，我們將確切知道哪些分區可以包含數據，因此可以在探測階段跳過其餘分區。

也許這沒有實現，因為雜湊建構獨立於探測很重要。與重新分區流操作符相反，可能無法保證在正確的時間將點陣圖操作符一直推送到掃描。也許這不是一個常見的情況，優化是不值得的。畢竟，您多久加入一次分區列而不是按它過濾？

引用自：https://dba.stackexchange.com/questions/242582

相關問答

Sql-Server

為什麼新插入的行會出現在 SQL Server 分區表的兩個文件組中？

  • March 17, 2021
檢視問答
Sql-Server

遷移前縮小數據文件以節省磁碟空間

  • February 26, 2021
檢視問答
Sql-Server

為什麼我將 Int / Smallint 隱式轉換為 Varchar，它真的會影響基數估計嗎？

  • October 18, 2020
檢視問答
Sql-Server

表分區功能空分區

  • February 6, 2020
檢視問答
Sql-Server

當建構端為空時，SQL Server 為什麼/何時評估內部散列連接的探測端？

  • December 6, 2019
檢視問答
Sql-Server

可以選擇使用 OLA 的腳本排除某些分區

  • September 1, 2019
檢視問答