A filegroup is a named SQL Server collection of database files used to organize and allocate database objects. In table partitioning, each partition can be placed on a different filegroup, allowing storage layout to support reporting workloads. section 10.7
Mistake: treating filegroups as a performance fix by themselves. Their benefit depends on the underlying storage layout: filegroups placed on different disks can let partitions be read in parallel, while partition elimination reduces unnecessary reads. section 10.7
Reporting queries over a large table
A date-partitioned impressions archive can assign partitions to separate filegroups and let a reporting query read only the relevant date range. The demonstrated partitioned query reduced logical reads from 46,979 to 15,732. section 10.7
Listing 10.12 Creating a partitioned table
CREATE PARTITION FUNCTION ImpressionDatesPF (DATETIME) #1
AS RANGE RIGHT FOR VALUES ('20200101', '20210101', '20220101', '20230101');
GO
CREATE PARTITION SCHEME ImpressionDatesPS #2
AS PARTITION ImpressionDatesPF
ALL TO ([PRIMARY]) ;
GO
CREATE TABLE dbo.ImpressionsArchivePartitioned(
ImpressionID BIGINT NOT NULL IDENTITY(1,1),
ImpressionUID UNIQUEIDENTIFIER NOT NULL,
ReferralURL VARCHAR(512) NOT NULL,
CookieID UNIQUEIDENTIFIER NOT NULL,
CampaignID BIGINT NOT NULL,
RenderingID BIGINT NOT NULL,
CountryCode TINYINT NULL,
StateID TINYINT NULL,
BrowserVersion BIGINT NOT NULL,
OperatingSystemID BIGINT NOT NULL,
BidPrice MONEY NOT NULL,
CostPerMille MONEY NOT NULL,
EventTime DATETIME NOT NULL,
) ON ImpressionDatesPS(EventTime) ; #3
GO
ALTER TABLE dbo.ImpressionsArchivePartitioned ADD CONSTRAINT #4
PK_ImpressionsArchivePartitioned PRIMARY KEY (ImpressionID, EventTime) ;
GO
INSERT INTO dbo.ImpressionsArchivePartitioned ( #5
ImpressionUID,
ReferralURL,
CookieID,
CampaignID,
RenderingID,
CountryCode,
StateID,
BrowserVersion,
OperatingSystemID,
BidPrice,
CostPerMille,
EventTime
)
SELECT
ImpressionUID
, ReferralURL
, CookieID
, CampaignID
, RenderingID
, CountryCode
, StateID
, BrowserVersion
, OperatingSystemID
, BidPrice
, CostPerMille
, EventTime
FROM dbo.ImpressionsArchive ;
Use the partitioned-table definition to inspect how a partition scheme maps partitions to storage; the example maps all partitions to PRIMARY. section 10.7
