SQLServerでのクエリプランのテーブル分割とコンパイル時間

「MSSQLServer Developer」コースの将来の学生のために、役立つ記事の翻訳



を用意しました。また、「ポリベース：前後の生活」というトピックに関する公開ウェビナーにすべての人を招待します。ウェビナーでは、Polybaseの前に他のデータベースと対話することがどのように可能であったか、そしてそれが現在どのように機能するかを見ていきます。

---

: « SQL Server , ?»

, , , . . , : « — ».

, - Stack Overflow , :

USE StackOverflow;
GO

/* Create date partition function by day since Stack Overflow's origin,
modified from Microsoft Books Online: 
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver15#BKMK_examples
 
DROP PARTITION SCHEME [DatePartitionScheme];
DROP PARTITION FUNCTION [DatePartitionFunction];
*/
DECLARE @DatePartitionFunction nvarchar(max) = 
    N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime) 
    AS RANGE RIGHT FOR VALUES (';  
DECLARE @i datetime = '2008-06-01';
WHILE @i <= GETDATE()
BEGIN  
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(20)) + '''' + N', ';  
SET @i = DATEADD(DAY, 1, @i);  
END  
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(20))+ '''' + N');';  
EXEC sp_executesql @DatePartitionFunction;  
GO  
 
/* Create matching partition scheme, but put everything in Primary: */
CREATE PARTITION SCHEME DatePartitionScheme  
AS PARTITION DatePartitionFunction  
ALL TO ( [PRIMARY] ); 
GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Users, CreationDate:

DROP TABLE IF EXISTS dbo.Users_partitioned;
GO
CREATE TABLE [dbo].[Users_partitioned](
	[Id] [int] NOT NULL,
	[AboutMe] [nvarchar](max) NULL,
	[Age] [int] NULL,
	[CreationDate] [datetime] NOT NULL,
	[DisplayName] [nvarchar](40) NOT NULL,
	[DownVotes] [int] NOT NULL,
	[EmailHash] [nvarchar](40) NULL,
	[LastAccessDate] [datetime] NOT NULL,
	[Location] [nvarchar](100) NULL,
	[Reputation] [int] NOT NULL,
	[UpVotes] [int] NOT NULL,
	[Views] [int] NOT NULL,
	[WebsiteUrl] [nvarchar](200) NULL,
	[AccountId] [int] NULL
) ON [PRIMARY];
GO
 
CREATE CLUSTERED INDEX CreationDate_Id ON 
	dbo.Users_partitioned (Id)
	ON DatePartitionScheme(CreationDate);
GO
 
INSERT INTO dbo.Users_partitioned (Id, AboutMe, Age,
	CreationDate, DisplayName, DownVotes, EmailHash,
	LastAccessDate, Location, Reputation, UpVotes,
	Views, WebsiteUrl, AccountId)
SELECT Id, AboutMe, Age,
	CreationDate, DisplayName, DownVotes, EmailHash,
	LastAccessDate, Location, Reputation, UpVotes,
	Views, WebsiteUrl, AccountId
	FROM dbo.Users;
GO
Let’s c
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Users Users_partitioned. , Users_partitioned , , , :

CREATE INDEX DisplayName ON dbo.Users(DisplayName);
CREATE INDEX DisplayName ON dbo.Users_partitioned(DisplayName);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, :

SET STATISTICS TIME, IO ON;
SELECT * FROM dbo.Users WHERE DisplayName = N'Brent Ozar';
GO
SELECT * FROM dbo.Users_partitioned WHERE DisplayName = N'Brent Ozar';
GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, , , , 0% , — 100%:

, 0,001, — 15. , (compile time), (execution time) (logical reads) . , — ( ):

27 . , : « 27 ?» — ! , 250 . , . - .

, , ETL- . , 250 .

, ?

Users_partitioned . , ON PRIMARY , .

CREATE INDEX DisplayName ON dbo.Users(DisplayName);
CREATE INDEX DisplayName ON dbo.Users_partitioned(DisplayName) ON [PRIMARY];
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

SET STATISTICS TIME, IO ON;
SELECT * FROM dbo.Users WHERE DisplayName = N'Brent Ozar';
GO
SELECT * FROM dbo.Users_partitioned WHERE DisplayName = N'Brent Ozar';
GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

- . , — :

, -

, , . (, rowstore- 100 ), , , . , , .

— , : «, , !» , : « , ».

---

"MS SQL Server Developer".

«Polybase: ».