Versão curta: existe uma maneira de obter o esquema de partição para os dados FILESTREAM usados por um índice clusterizado criado em uma tabela particionada?
Versão mais longa: digamos que você queira particionar uma tabela que armazena dados FILESTREAM, então a documentação diz:
Se a tabela for particionada, a cláusula FILESTREAM_ON deve ser incluída e deve especificar um esquema de partição de grupos de arquivos FILESTREAM que use a mesma função de partição e colunas de partição que o esquema de partição para a tabela. Caso contrário, um erro será gerado.
Então você cria grupos de arquivos para linhas e dados de fluxo de arquivos, uma função de partição e dois esquemas de partição (novamente um para as linhas e dados de fluxo de arquivos, respectivamente), assim:
USE [master]
GO
CREATE DATABASE [FSPartitionTest]
ON PRIMARY
( NAME = N'FSPartitionTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FSPartitionTest.mdf'),
FILEGROUP [DataPartitionA]
( NAME = N'Data_A', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Data_A.ndf'),
FILEGROUP [DataPartitionB]
( NAME = N'Data_B', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Data_B.ndf'),
FILEGROUP [FSPartitionA] CONTAINS FILESTREAM DEFAULT
( NAME = N'FS_A', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FS_A'),
FILEGROUP [FSPartitionB] CONTAINS FILESTREAM
( NAME = N'FS_B', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FS_B')
LOG ON
( NAME = N'FSPartitionTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FSPartitionTest_log.ldf')
GO
USE [FSPartitionTest]
GO
CREATE PARTITION FUNCTION [APartitionFunction] (INT)
AS RANGE LEFT FOR VALUES (1);
GO
CREATE PARTITION SCHEME [DataPartitionScheme]
AS PARTITION [APartitionFunction]
TO ([DataPartitionA], [DataPartitionB]);
GO
CREATE PARTITION SCHEME [FSPartitionScheme]
AS PARTITION [APartitionFunction]
TO ([FSPartitionA], [FSPartitionB]);
GO
CREATE TABLE [FilestreamTable] (
[Partition] INT NOT NULL
, [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL
CONSTRAINT [UX_FilestreamTable_Id] UNIQUE NONCLUSTERED ON [PRIMARY]
, [FilestreamData] VARBINARY(MAX) FILESTREAM NULL
, INDEX [UX_FilestreamTable_Partition_Id] UNIQUE CLUSTERED (
[Partition],
[Id]
) ON [DataPartitionScheme]([Partition]) FILESTREAM_ON [FSPartitionScheme]
) ON [DataPartitionScheme]([Partition]) FILESTREAM_ON [FSPartitionScheme]
GO
Então você pode consultar os esquemas de partição e os índices assim:
SELECT *
FROM sys.partition_schemes
SELECT T.[name], I.[name], I.[data_space_id]
FROM sys.tables AS T
JOIN sys.indexes AS I
ON T.[object_id] = I.[object_id]
Ambos os esquemas de partição aparecem, ambos usam a mesma função de partição e o índice clusterizado faz referência ao DataPartitionSchemein sys.indexes. No entanto, o esquema de partição usado para os dados FILESTREAM não é referenciado. Neste caso, há apenas um outro esquema de partição usando a mesma função de partição e tendo grupos de arquivos filestream como alvos. Para qualquer cenário prático, estaríamos prontos. Ninguém adicionaria outro esquema de partição, certo?
CREATE PARTITION SCHEME [FSPartitionSchemeB]
AS PARTITION [APartitionFunction]
TO ([FSPartitionB], [FSPartitionA]);
GO
CREATE TABLE [FilestreamTableB] (
[Partition] INT NOT NULL
, [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL
CONSTRAINT [UX_FilestreamTableB_Id] UNIQUE NONCLUSTERED ON [PRIMARY]
, [FilestreamData] VARBINARY(MAX) FILESTREAM NULL
, INDEX [UX_FilestreamTableB_Partition_Id] UNIQUE CLUSTERED (
[Partition],
[Id]
) ON [DataPartitionScheme]([Partition]) FILESTREAM_ON [FSPartitionSchemeB]
) ON [DataPartitionScheme]([Partition]) FILESTREAM_ON [FSPartitionSchemeB]
GO
CREATE PARTITION SCHEME [FSPartitionSchemeC]
AS PARTITION [APartitionFunction]
ALL TO ([FSPartitionA]);
GO
CREATE TABLE [FilestreamTableC] (
[Partition] INT NOT NULL
, [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL
CONSTRAINT [UX_FilestreamTableC_Id] UNIQUE NONCLUSTERED ON [PRIMARY]
, [FilestreamData] VARBINARY(MAX) FILESTREAM NULL
, INDEX [UX_FilestreamTableC_Partition_Id] UNIQUE CLUSTERED (
[Partition],
[Id]
) ON [DataPartitionScheme]([Partition]) FILESTREAM_ON [FSPartitionSchemeC]
) ON [DataPartitionScheme]([Partition]) FILESTREAM_ON [FSPartitionSchemeC]
GO
Como podemos determinar qual tabela usa qual esquema de partição? Há alguma view que faça referência direta ao data_space_idusado por um índice para dados FILESTREAM?
Solução alternativa:
Há uma coisa que notei, mas não tenho certeza se funciona 100% do tempo. A sys.partitionsvisualização tem uma filestream_filegroup_idcoluna:
SELECT T.[name]
, I.[name]
, I.[data_space_id]
, P.[partition_number]
, FG.[name]
FROM sys.tables AS T
JOIN sys.indexes AS I
ON T.[object_id] = I.[object_id]
JOIN sys.partitions AS P
ON I.[object_id] = P.[object_id]
AND I.[index_id] = P.[index_id]
JOIN sys.filegroups AS FG
ON P.[filestream_filegroup_id] = FG.[data_space_id]
A visualização destination_data_spacestambém tem uma coluna destination_id, que - suponho - corresponde a partition_number:
SELECT PS.[name]
, DDS.[destination_id]
, FG.[name]
FROM sys.partition_schemes AS PS
JOIN sys.destination_data_spaces AS DDS
ON PS.[data_space_id] = DDS.[partition_scheme_id]
JOIN sys.filegroups AS FG
ON DDS.[data_space_id] = FG.[data_space_id]
Então, acho que é possível verificar se os grupos de arquivos referenciados por destination_data_spacescorrespondem aos referenciados por partitionspara cada partição:
WITH A AS (
SELECT T.[name] AS [Table]
, I.[name] AS [Index]
, PS.[name] AS [PartitionScheme] -- for row data
, FSPS.[name] AS [FS_PartitionScheme] -- candidate
, FG.[name] AS [FS_DestinationFilegroup] -- referenced by partition scheme
, PFG.[name] AS [FS_PartitionFilegroup] -- referenced by partition
, CASE WHEN FG.[name] = PFG.[name] THEN 1 ELSE 0 END AS [FilegorupsMatch]
, COUNT(*) OVER (PARTITION BY T.[object_id], I.[index_id], FSPS.[data_space_id]) AS [NumPartitions]
, SUM(CASE WHEN FG.[name] = PFG.[name] THEN 1 ELSE 0 END) OVER (PARTITION BY T.[object_id], I.[index_id], FSPS.[data_space_id]) AS [SumMatches]
FROM sys.tables AS T
JOIN sys.indexes AS I
ON T.[object_id] = I.[object_id]
--partition scheme used for row data
JOIN sys.partition_schemes AS PS
ON I.[data_space_id] = PS.[data_space_id]
--look for candidate partition schemes, that are used for FILESTREAM data
--must use the same partition function
JOIN sys.partition_schemes AS FSPS
ON PS.[function_id] = FSPS.[function_id]
AND PS.[data_space_id] <> FSPS.[data_space_id]
--destination must be a FILESTREAM filegroup
JOIN sys.destination_data_spaces AS DDS
ON FSPS.[data_space_id] = DDS.[partition_scheme_id]
JOIN sys.filegroups AS FG
ON DDS.[data_space_id] = FG.[data_space_id]
AND FG.[type_desc] = 'FILESTREAM_DATA_FILEGROUP'
--get the partition where partition_number matches desination_id
JOIN sys.partitions AS P
ON I.[object_id] = P.[object_id]
AND I.[index_id] = P.[index_id]
AND P.[partition_number] = DDS.[destination_id]
--find the filegroup referenced by the partition
LEFT JOIN sys.filegroups AS PFG
ON P.[filestream_filegroup_id] = PFG.[data_space_id]
)
SELECT [Table], [Index], [PartitionScheme], [FS_PartitionScheme]
FROM A
WHERE [NumPartitions] = [SumMatches]
GROUP BY [Table], [Index], [PartitionScheme], [FS_PartitionScheme]
Deve haver uma linha por Índice, a menos que algum palhaço faça isso:
CREATE PARTITION SCHEME [FSPartitionSchemeB2]
AS PARTITION [APartitionFunction]
TO ([FSPartitionB], [FSPartitionA]);
GO
Então, se alguém puder confirmar que de fato o partition_numbersempre corresponde ao destination_id, podemos pelo menos identificar o esquema de partição até uma definição única (excluindo NEXT USED), mas ainda não um . definitivo data_space_id.
