La distribution des tables sur SQL Data Warehouse

SQL Data Warehouse a été abordé dans les grandes lignes dans un article précédent. Ce service PaaS permet d’avoir un système distribué pour la partie calcul et stockage sur un entrepôt de données décisionnel. Le but de ce post est de revenir plus en détail sur cette notion de distribution.

Pour rappel, une table peut être distribuée selon les modèles suivant :

Hachage
Tourniquet
Réplication

Idéalement, les tables d’historisation et de faits devraient être distribuées sur plusieurs nœuds de calculs pour exploiter au mieux les capacités de SQL Data Warehouse. De manière plus pragmatique, quelle option de distribution est à privilégier pour limiter les déplacements de données via le DMS (Data Mouvement Service) pour la résolution d’une requête ? Voici quelques éléments de réponse.

Hachage

Une fonction de hash détermine la distribution des données sur les nœuds de calcul. Le Hachage est une bonne option, dans les cas suivants :

Tables volumineuses
Porter sur les colonnes qui font l’objet de jointures et/ou de groupements
Avoir une répartition uniforme des lignes, le cas échéant cela créé un goulot d’étranglement sur les nœuds où sont concentrées les données

L’éligibilité d’une colonne comme clé de hash doit se faire sur cette base :

Pas ou peu de valeurs NULL
Colonne de hash ne faisant pas l’objet de mise à jour
S’assurer que le nombre de valeurs distinct pour cette colonne de hash est supérieur ou égale à 60, soit le nombre de distributions
Répartition de cette colonne de hash non asymétrique
Colonne de hash utilisée dans les jointures et/ou groupements
Relever le nombre de tables portant aussi la colonne éligible comme clé de hash et les aligner autant que possible

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FactInternetSales]
(
	[ProductKey] [int] NOT NULL,
	[OrderDateKey] [int] NOT NULL,
	[DueDateKey] [int] NOT NULL,
	[ShipDateKey] [int] NOT NULL,
	[CustomerKey] [int] NOT NULL,
	[PromotionKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[SalesTerritoryKey] [int] NOT NULL,
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [tinyint] NOT NULL,
	[RevisionNumber] [tinyint] NOT NULL,
	[OrderQuantity] [smallint] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[ExtendedAmount] [money] NOT NULL,
	[UnitPriceDiscountPct] [float] NOT NULL,
	[DiscountAmount] [float] NOT NULL,
	[ProductStandardCost] [money] NOT NULL,
	[TotalProductCost] [money] NOT NULL,
	[SalesAmount] [money] NOT NULL,
	[TaxAmt] [money] NOT NULL,
	[Freight] [money] NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[CustomerPONumber] [nvarchar](25) NULL
)
WITH
(
	DISTRIBUTION = HASH ( [CustomerKey] ),
	CLUSTERED COLUMNSTORE INDEX
)
GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[FactInternetSales]

(

[ProductKey] [int] NOT NULL,

[OrderDateKey] [int] NOT NULL,

[DueDateKey] [int] NOT NULL,

[ShipDateKey] [int] NOT NULL,

[CustomerKey] [int] NOT NULL,

[PromotionKey] [int] NOT NULL,

[CurrencyKey] [int] NOT NULL,

[SalesTerritoryKey] [int] NOT NULL,

[SalesOrderNumber] [nvarchar](20) NOT NULL,

[SalesOrderLineNumber] [tinyint] NOT NULL,

[RevisionNumber] [tinyint] NOT NULL,

[OrderQuantity] [smallint] NOT NULL,

[UnitPrice] [money] NOT NULL,

[ExtendedAmount] [money] NOT NULL,

[UnitPriceDiscountPct] [float] NOT NULL,

[DiscountAmount] [float] NOT NULL,

[ProductStandardCost] [money] NOT NULL,

[TotalProductCost] [money] NOT NULL,

[SalesAmount] [money] NOT NULL,

[TaxAmt] [money] NOT NULL,

[Freight] [money] NOT NULL,

[CarrierTrackingNumber] [nvarchar](25) NULL,

[CustomerPONumber] [nvarchar](25) NULL

)

WITH

(

DISTRIBUTION = HASH ( [CustomerKey] ),

CLUSTERED COLUMNSTORE INDEX

)

Tourniquet

Les données sont réparties de manière égale sur différents nœuds de calcul, à tour de rôle. Le Touniquet est une bonne option, dans les cas suivants :

Tables de staging (dédiée au chargement)
Tables volumineuses non éligible au hachage
Dimensions dont le nombre de lignes ne dépasse pas un segment d’index columnstore (SQL Data Warehouse créé un index clutered columnstore par défaut)

Les mouvements de données sont requis pour toutes les opérations mais ce modèle permet une distribution équitable.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FactInternetSales]
(
	[ProductKey] [int] NOT NULL,
	[OrderDateKey] [int] NOT NULL,
	[DueDateKey] [int] NOT NULL,
	[ShipDateKey] [int] NOT NULL,
	[CustomerKey] [int] NOT NULL,
	[PromotionKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[SalesTerritoryKey] [int] NOT NULL,
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [tinyint] NOT NULL,
	[RevisionNumber] [tinyint] NOT NULL,
	[OrderQuantity] [smallint] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[ExtendedAmount] [money] NOT NULL,
	[UnitPriceDiscountPct] [float] NOT NULL,
	[DiscountAmount] [float] NOT NULL,
	[ProductStandardCost] [money] NOT NULL,
	[TotalProductCost] [money] NOT NULL,
	[SalesAmount] [money] NOT NULL,
	[TaxAmt] [money] NOT NULL,
	[Freight] [money] NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[CustomerPONumber] [nvarchar](25) NULL
)
WITH
(
	DISTRIBUTION = ROUND_ROBIN,
	CLUSTERED COLUMNSTORE INDEX
)
GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[FactInternetSales]

(

[ProductKey] [int] NOT NULL,

[OrderDateKey] [int] NOT NULL,

[DueDateKey] [int] NOT NULL,

[ShipDateKey] [int] NOT NULL,

[CustomerKey] [int] NOT NULL,

[PromotionKey] [int] NOT NULL,

[CurrencyKey] [int] NOT NULL,

[SalesTerritoryKey] [int] NOT NULL,

[SalesOrderNumber] [nvarchar](20) NOT NULL,

[SalesOrderLineNumber] [tinyint] NOT NULL,

[RevisionNumber] [tinyint] NOT NULL,

[OrderQuantity] [smallint] NOT NULL,

[UnitPrice] [money] NOT NULL,

[ExtendedAmount] [money] NOT NULL,

[UnitPriceDiscountPct] [float] NOT NULL,

[DiscountAmount] [float] NOT NULL,

[ProductStandardCost] [money] NOT NULL,

[TotalProductCost] [money] NOT NULL,

[SalesAmount] [money] NOT NULL,

[TaxAmt] [money] NOT NULL,

[Freight] [money] NOT NULL,

[CarrierTrackingNumber] [nvarchar](25) NULL,

[CustomerPONumber] [nvarchar](25) NULL

)

WITH

(

DISTRIBUTION = ROUND_ROBIN,

CLUSTERED COLUMNSTORE INDEX

)

Réplication

Chaque nœud de calcul dispose d’une copie des données.

Petites tables (moins de 2Go)
Dimensions dont le nombre de lignes ne dépasse pas un segment d’index columnstore
Avec ou sans jointure, prédicats d’égalité ou d’inégalité

Ce modèle de distribution n’occasionne pas de déplacement de données. En revanche le stockage requis peut être relativement important, selon la formule qui suit : taille de la table répliquée multipliée par le nombre de nœuds de calcul.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DimCustomer]
(
	[CustomerKey] [int] NOT NULL,
	[GeographyKey] [int] NULL,
	[CustomerAlternateKey] [nvarchar](15) NOT NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL
)
WITH
(
	DISTRIBUTION = REPLICATE,
	CLUSTERED COLUMNSTORE INDEX
)
GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[DimCustomer]

(

[CustomerKey] [int] NOT NULL,

[GeographyKey] [int] NULL,

[CustomerAlternateKey] [nvarchar](15) NOT NULL,

[Title] [nvarchar](8) NULL,

[FirstName] [nvarchar](50) NULL,

[MiddleName] [nvarchar](50) NULL,

[LastName] [nvarchar](50) NULL

)

WITH

(

DISTRIBUTION = REPLICATE,

CLUSTERED COLUMNSTORE INDEX

)

Analyse d’une table pour le choix d’une distribution

Pour cette analyse, nous allons nous appuyer sur la base de données AdventureWorkDW et plus spécifiquement sur la table dbo.FactInternetSales. Pour rappel, le nombre de distribution est de 60.

Dans l’optique d’une distribution par hachage, commençons par lister les colonnes pour la table dbo.FactInternetSales. Seules les ProductKey et CustomerKey seront retenues.

SELECT	ColumnName = c.name
		, TypeName = t.name
FROM	sys.columns c
INNER	JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE	c.object_id = OBJECT_ID('FactInternetSales');

SELECT ColumnName = c.name

, TypeName = t.name

FROM sys.columns c

INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

WHERE c.object_id = OBJECT_ID('FactInternetSales');

Faisons une projection de la distribution par hachage avec la colonne ProductKey :

SELECT	COUNT(DISTINCT ProductKey)
FROM	dbo.FactInternetSales;
-- 158

SELECT distributionID
	, rowCnt = SUM(rowCnt)
FROM (
	SELECT DENSE_RANK() OVER (
			ORDER BY ProductKey
			) rowNum
		,COUNT(*) rowCnt
		,distributionID = NTILE(60) OVER (
			ORDER BY (SELECT GETDATE())
			)
	FROM dbo.FactInternetSales
	GROUP BY ProductKey
	) t
GROUP BY distributionID
ORDER BY distributionID;

SELECT COUNT(DISTINCT ProductKey)

FROM dbo.FactInternetSales;

-- 158

SELECT distributionID

, rowCnt = SUM(rowCnt)

FROM (

SELECT DENSE_RANK() OVER (

ORDER BY ProductKey

) rowNum

,COUNT(*) rowCnt

,distributionID = NTILE(60) OVER (

ORDER BY (SELECT GETDATE())

)

FROM dbo.FactInternetSales

GROUP BY ProductKey

) t

GROUP BY distributionID

ORDER BY distributionID;

Faisons maintenant une projection de la distribution par hachage avec la colonne CustomerKey :

SELECT	COUNT(DISTINCT CustomerKey)
FROM	dbo.FactInternetSales;
-- 18484

SELECT distributionID
	,rowCnt = SUM(rowCnt)
FROM (
	SELECT DENSE_RANK() OVER (
			ORDER BY CustomerKey
			) rowNum
		,rowCnt = COUNT(*)
		,distributionID = NTILE(60) OVER (
			ORDER BY GETDATE()
			)
	FROM dbo.FactInternetSales
	GROUP BY CustomerKey
	) t
GROUP BY distributionID
ORDER BY distributionID;

SELECT COUNT(DISTINCT CustomerKey)

FROM dbo.FactInternetSales;

-- 18484

SELECT distributionID

,rowCnt = SUM(rowCnt)

FROM (

SELECT DENSE_RANK() OVER (

ORDER BY CustomerKey

) rowNum

,rowCnt = COUNT(*)

,distributionID = NTILE(60) OVER (

ORDER BY GETDATE()

)

FROM dbo.FactInternetSales

GROUP BY CustomerKey

) t

GROUP BY distributionID

ORDER BY distributionID;

On observe que dans le cas d’une distribution de hachage en passant par la colonne ProductKey, on a une distribution asymétrique des données contrairement à la colonne CustomerKey où les lignes sont réparties beaucoup plus équitablement.

Autre pendant à prendre en compte pour une distribution de hachage, c’est l’alignement des tables annexes.

SELECT	TableName = OBJECT_NAME(object_id)
FROM	sys.columns
WHERE	name = 'CustomerKey';

SELECT TableName = OBJECT_NAME(object_id)

FROM sys.columns

WHERE name = 'CustomerKey';

Et enfin qu’aurait donner une simple distribution par tourniquet ? Une répartition garantie uniforme sur la base des 60 distributions.

SELECT distributionID
	,rowCnt = COUNT(rowNum)
FROM (
	SELECT ROW_NUMBER() OVER (
			ORDER BY (
					SELECT NULL
					)
			) rowNum
		,distributionID = NTILE(60) OVER (
			ORDER BY (
					SELECT GETDATE()
					)
			)
	FROM dbo.FactInternetSales
	) t
GROUP BY distributionID
ORDER BY distributionID;

SELECT distributionID

,rowCnt = COUNT(rowNum)

FROM (

SELECT ROW_NUMBER() OVER (

ORDER BY (

SELECT NULL

)

) rowNum

,distributionID = NTILE(60) OVER (

ORDER BY (

SELECT GETDATE()

)

FROM dbo.FactInternetSales

) t

GROUP BY distributionID

ORDER BY distributionID;

Il n’y a pas de conclusion à tirer, l’objectif de cette démonstration étant de donner quelques éléments d’analyse pour faire un choix de distribution pertinent.

Informations de distribution

Cette requête permet de connaitre le type de distribution sur chaque table :

SELECT	TableName = OBJECT_NAME(dt.[object_id])
		, DistributuonType = dt.[distribution_policy_desc]
		, HashColumn
FROM [sys].[pdw_table_distribution_properties] dt
LEFT JOIN (
	SELECT	p.[object_id]
			, HashColumn = cs.name
	FROM [sys].[pdw_column_distribution_properties] p
	INNER JOIN sys.columns cs ON p.[column_id] = cs.[column_id] AND p.[object_id] = cs.[object_id]
	WHERE distribution_ordinal = 1
	) hc ON hc.[object_id] = dt.[object_id]

SELECT TableName = OBJECT_NAME(dt.[object_id])

, DistributuonType = dt.[distribution_policy_desc]

, HashColumn

FROM [sys].[pdw_table_distribution_properties] dt

LEFT JOIN (

SELECT p.[object_id]

, HashColumn = cs.name

FROM [sys].[pdw_column_distribution_properties] p

INNER JOIN sys.columns cs ON p.[column_id] = cs.[column_id] AND p.[object_id] = cs.[object_id]

WHERE distribution_ordinal = 1

) hc ON hc.[object_id] = dt.[object_id]

Dans un premier temps, la création de la vue dbo.vTablesSizes devrait nous permettre d’extraire de manière moins verbeuse d’autres informations…

CREATE VIEW dbo.vTableSizes
AS
WITH base
AS
(
SELECT 
 GETDATE()                                                             AS  [execution_time]
, DB_NAME()                                                            AS  [database_name]
, s.name                                                               AS  [schema_name]
, t.name                                                               AS  [table_name]
, QUOTENAME(s.name)+'.'+QUOTENAME(t.name)                              AS  [two_part_name]
, nt.[name]                                                            AS  [node_table_name]
, ROW_NUMBER() OVER(PARTITION BY nt.[name] ORDER BY (SELECT NULL))     AS  [node_table_name_seq]
, tp.[distribution_policy_desc]                                        AS  [distribution_policy_name]
, c.[name]                                                             AS  [distribution_column]
, nt.[distribution_id]                                                 AS  [distribution_id]
, i.[type]                                                             AS  [index_type]
, i.[type_desc]                                                        AS  [index_type_desc]
, nt.[pdw_node_id]                                                     AS  [pdw_node_id]
, pn.[type]                                                            AS  [pdw_node_type]
, pn.[name]                                                            AS  [pdw_node_name]
, di.name                                                              AS  [dist_name]
, di.position                                                          AS  [dist_position]
, nps.[partition_number]                                               AS  [partition_nmbr]
, nps.[reserved_page_count]                                            AS  [reserved_space_page_count]
, nps.[reserved_page_count] - nps.[used_page_count]                    AS  [unused_space_page_count]
, nps.[in_row_data_page_count] 
    + nps.[row_overflow_used_page_count] 
    + nps.[lob_used_page_count]                                        AS  [data_space_page_count]
, nps.[reserved_page_count] 
 - (nps.[reserved_page_count] - nps.[used_page_count]) 
 - ([in_row_data_page_count] 
         + [row_overflow_used_page_count]+[lob_used_page_count])       AS  [index_space_page_count]
, nps.[row_count]                                                      AS  [row_count]
from 
    sys.schemas s
INNER JOIN sys.tables t
    ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.indexes i
    ON  t.[object_id] = i.[object_id]
    AND i.[index_id] <= 1
INNER JOIN sys.pdw_table_distribution_properties tp
    ON t.[object_id] = tp.[object_id]
INNER JOIN sys.pdw_table_mappings tm
    ON t.[object_id] = tm.[object_id]
INNER JOIN sys.pdw_nodes_tables nt
    ON tm.[physical_name] = nt.[name]
INNER JOIN sys.dm_pdw_nodes pn
    ON  nt.[pdw_node_id] = pn.[pdw_node_id]
INNER JOIN sys.pdw_distributions di
    ON  nt.[distribution_id] = di.[distribution_id]
INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps
    ON nt.[object_id] = nps.[object_id]
    AND nt.[pdw_node_id] = nps.[pdw_node_id]
    AND nt.[distribution_id] = nps.[distribution_id]
LEFT OUTER JOIN (select * from sys.pdw_column_distribution_properties where distribution_ordinal = 1) cdp
    ON t.[object_id] = cdp.[object_id]
LEFT OUTER JOIN sys.columns c
    ON cdp.[object_id] = c.[object_id]
    AND cdp.[column_id] = c.[column_id]
)
, size
AS
(
SELECT
   [execution_time]
,  [database_name]
,  [schema_name]
,  [table_name]
,  [two_part_name]
,  [node_table_name]
,  [node_table_name_seq]
,  [distribution_policy_name]
,  [distribution_column]
,  [distribution_id]
,  [index_type]
,  [index_type_desc]
,  [pdw_node_id]
,  [pdw_node_type]
,  [pdw_node_name]
,  [dist_name]
,  [dist_position]
,  [partition_nmbr]
,  [reserved_space_page_count]
,  [unused_space_page_count]
,  [data_space_page_count]
,  [index_space_page_count]
,  [row_count]
,  ([reserved_space_page_count] * 8.0)                                 AS [reserved_space_KB]
,  ([reserved_space_page_count] * 8.0)/1000                            AS [reserved_space_MB]
,  ([reserved_space_page_count] * 8.0)/1000000                         AS [reserved_space_GB]
,  ([reserved_space_page_count] * 8.0)/1000000000                      AS [reserved_space_TB]
,  ([unused_space_page_count]   * 8.0)                                 AS [unused_space_KB]
,  ([unused_space_page_count]   * 8.0)/1000                            AS [unused_space_MB]
,  ([unused_space_page_count]   * 8.0)/1000000                         AS [unused_space_GB]
,  ([unused_space_page_count]   * 8.0)/1000000000                      AS [unused_space_TB]
,  ([data_space_page_count]     * 8.0)                                 AS [data_space_KB]
,  ([data_space_page_count]     * 8.0)/1000                            AS [data_space_MB]
,  ([data_space_page_count]     * 8.0)/1000000                         AS [data_space_GB]
,  ([data_space_page_count]     * 8.0)/1000000000                      AS [data_space_TB]
,  ([index_space_page_count]  * 8.0)                                   AS [index_space_KB]
,  ([index_space_page_count]  * 8.0)/1000                              AS [index_space_MB]
,  ([index_space_page_count]  * 8.0)/1000000                           AS [index_space_GB]
,  ([index_space_page_count]  * 8.0)/1000000000                        AS [index_space_TB]
FROM base
)
SELECT * 
FROM size
;

100

101

102

103

104

105

106

107

108

109

CREATE VIEW dbo.vTableSizes

WITH base

(

SELECT

GETDATE() AS [execution_time]

, DB_NAME() AS [database_name]

, s.name AS [schema_name]

, t.name AS [table_name]

, QUOTENAME(s.name)+'.'+QUOTENAME(t.name) AS [two_part_name]

, nt.[name] AS [node_table_name]

, ROW_NUMBER() OVER(PARTITION BY nt.[name] ORDER BY (SELECT NULL)) AS [node_table_name_seq]

, tp.[distribution_policy_desc] AS [distribution_policy_name]

, c.[name] AS [distribution_column]

, nt.[distribution_id] AS [distribution_id]

, i.[type] AS [index_type]

, i.[type_desc] AS [index_type_desc]

, nt.[pdw_node_id] AS [pdw_node_id]

, pn.[type] AS [pdw_node_type]

, pn.[name] AS [pdw_node_name]

, di.name AS [dist_name]

, di.position AS [dist_position]

, nps.[partition_number] AS [partition_nmbr]

, nps.[reserved_page_count] AS [reserved_space_page_count]

, nps.[reserved_page_count] - nps.[used_page_count] AS [unused_space_page_count]

, nps.[in_row_data_page_count]

+ nps.[row_overflow_used_page_count]

+ nps.[lob_used_page_count] AS [data_space_page_count]

, nps.[reserved_page_count]

- (nps.[reserved_page_count] - nps.[used_page_count])

- ([in_row_data_page_count]

+ [row_overflow_used_page_count]+[lob_used_page_count]) AS [index_space_page_count]

, nps.[row_count] AS [row_count]

from

sys.schemas s

INNER JOIN sys.tables t

ON s.[schema_id] = t.[schema_id]

INNER JOIN sys.indexes i

ON t.[object_id] = i.[object_id]

AND i.[index_id] <= 1

INNER JOIN sys.pdw_table_distribution_properties tp

ON t.[object_id] = tp.[object_id]

INNER JOIN sys.pdw_table_mappings tm

ON t.[object_id] = tm.[object_id]

INNER JOIN sys.pdw_nodes_tables nt

ON tm.[physical_name] = nt.[name]

INNER JOIN sys.dm_pdw_nodes pn

ON nt.[pdw_node_id] = pn.[pdw_node_id]

INNER JOIN sys.pdw_distributions di

ON nt.[distribution_id] = di.[distribution_id]

INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps

ON nt.[object_id] = nps.[object_id]

AND nt.[pdw_node_id] = nps.[pdw_node_id]

AND nt.[distribution_id] = nps.[distribution_id]

LEFT OUTER JOIN (select * from sys.pdw_column_distribution_properties where distribution_ordinal = 1) cdp

ON t.[object_id] = cdp.[object_id]

LEFT OUTER JOIN sys.columns c

ON cdp.[object_id] = c.[object_id]

AND cdp.[column_id] = c.[column_id]

)

, size

(

SELECT

[execution_time]

, [database_name]

, [schema_name]

, [table_name]

, [two_part_name]

, [node_table_name]

, [node_table_name_seq]

, [distribution_policy_name]

, [distribution_column]

, [distribution_id]

, [index_type]

, [index_type_desc]

, [pdw_node_id]

, [pdw_node_type]

, [pdw_node_name]

, [dist_name]

, [dist_position]

, [partition_nmbr]

, [reserved_space_page_count]

, [unused_space_page_count]

, [data_space_page_count]

, [index_space_page_count]

, [row_count]

, ([reserved_space_page_count] * 8.0) AS [reserved_space_KB]

, ([reserved_space_page_count] * 8.0)/1000 AS [reserved_space_MB]

, ([reserved_space_page_count] * 8.0)/1000000 AS [reserved_space_GB]

, ([reserved_space_page_count] * 8.0)/1000000000 AS [reserved_space_TB]

, ([unused_space_page_count] * 8.0) AS [unused_space_KB]

, ([unused_space_page_count] * 8.0)/1000 AS [unused_space_MB]

, ([unused_space_page_count] * 8.0)/1000000 AS [unused_space_GB]

, ([unused_space_page_count] * 8.0)/1000000000 AS [unused_space_TB]

, ([data_space_page_count] * 8.0) AS [data_space_KB]

, ([data_space_page_count] * 8.0)/1000 AS [data_space_MB]

, ([data_space_page_count] * 8.0)/1000000 AS [data_space_GB]

, ([data_space_page_count] * 8.0)/1000000000 AS [data_space_TB]

, ([index_space_page_count] * 8.0) AS [index_space_KB]

, ([index_space_page_count] * 8.0)/1000 AS [index_space_MB]

, ([index_space_page_count] * 8.0)/1000000 AS [index_space_GB]

, ([index_space_page_count] * 8.0)/1000000000 AS [index_space_TB]

FROM base

)

SELECT *

FROM size

;

Telles que le nombre de lignes présentes dans chaque distribution pour la table dbo.FactInternetSales :

SELECT	database_name
		,[schema_name]
		,[table_name]
		,[distribution_id]
		,[row_count]
		,[reserved_space_GB]
		,[unused_space_GB]
		,[data_space_GB]
		,[index_space_GB]
FROM [dbo].[vTableSizes]
WHERE [table_name] = 'FactInternetSales'
ORDER BY [row_count] DESC

SELECT database_name

,[schema_name]

,[table_name]

,[distribution_id]

,[row_count]

,[reserved_space_GB]

,[unused_space_GB]

,[data_space_GB]

,[index_space_GB]

FROM [dbo].[vTableSizes]

WHERE [table_name] = 'FactInternetSales'

ORDER BY [row_count] DESC

Et en bonus, les informations de volumétrie basiques :

SELECT database_name
	,schema_name
	,table_name
	,distribution_policy_name
	,distribution_column
	,index_type_desc
	,COUNT(DISTINCT partition_nmbr) AS nbr_partitions
	,SUM(row_count) AS table_row_count
	,SUM(reserved_space_GB) AS table_reserved_space_GB
	,SUM(data_space_GB) AS table_data_space_GB
	,SUM(index_space_GB) AS table_index_space_GB
	,SUM(unused_space_GB) AS table_unused_space_GB
FROM dbo.vTableSizes
GROUP BY database_name
	,schema_name
	,table_name
	,distribution_policy_name
	,distribution_column
	,index_type_desc
ORDER BY table_reserved_space_GB DESC;

SELECT database_name

,schema_name

,table_name

,distribution_policy_name

,distribution_column

,index_type_desc

,COUNT(DISTINCT partition_nmbr) AS nbr_partitions

,SUM(row_count) AS table_row_count

,SUM(reserved_space_GB) AS table_reserved_space_GB

,SUM(data_space_GB) AS table_data_space_GB

,SUM(index_space_GB) AS table_index_space_GB

,SUM(unused_space_GB) AS table_unused_space_GB

FROM dbo.vTableSizes

GROUP BY database_name

,schema_name

,table_name

,distribution_policy_name

,distribution_column

,index_type_desc

ORDER BY table_reserved_space_GB DESC;

Auteur

Sarah Béquet

Experte SQL Server Prod/Etude avec un bonus sur la BI, speaker aux SQL Saturday et Journées SQL Server et enfin formatrice Orsys, je dispose d'une vision d'ensemble des systèmes d'information, d'une appétence pour l'industrialisation et d'un souci permanent de la performance.