6
votes

SQL remplace toutes les tables par un index de colonnes en cluster

Nous menons un projet de migration et cherchons à remplacer la plupart des index Rowstore par des index Clustered Columnstore pour les grands Data Warehouse. Nous ajoutons un index unique sur la colonne d'identité.

Quelqu'un a-t-il un script à modifier dans toutes les 100 tables et à remplacer l'index cluster par clé primaire par Columnstore Index?

Tester pour voir si l'index columnstore aidera à optimiser les performances si nous migrons.

* Au fait, les colonnes d'identité sont-elles recommandées dans Columnstore? Besoin d'un moyen d'identifier chaque ligne (parfois les colonnes d'identité peuvent être réinitialisées, donc en plaçant une contrainte unique, Ou faut-il utiliser Guids?) Si ce n'est pas l'identité, n'hésitez pas à supprimer la colonne d'identité avec Guid ou autre chose.

Courant:

CREATE TABLE [dbo].[Fact_SalesTransaction] 
(
    [FactSalesTransactionId]        INT              IDENTITY (1, 1) NOT NULL,
    [DimCustomerId]                 INT              NOT NULL,
    [DimSellerId]                   INT              NOT NULL,
    [SalesDatetime]                 DATETIME         NULL,
    [DimSalesDateId]                INT              NULL,
    [SalesAmount]                   DECIMAL (28, 2)  NULL,
    [ETLCreateDate]                 DATETIME         NULL,
);


CREATE CLUSTERED COLUMNSTORE INDEX ccx_Fact_SalesTransaction ON Fact_SalesTransaction;  

CREATE UNIQUE INDEX unx_FactSalesTransactionId ON dbo.Fact_SalesTransaction (FactSalesTransactionId);

Attendu:

CREATE TABLE [dbo].[Fact_SalesTransaction] 
(
    [FactSalesTransactionId]        INT              IDENTITY (1, 1) NOT NULL,
    [DimCustomerId]                 INT              NOT NULL,
    [DimSellerId]                   INT              NOT NULL,
    [SalesDatetime]                 DATETIME         NULL,
    [DimSalesDateId]                INT              NULL,
    [SalesAmount]                   DECIMAL (28, 2)  NULL,
    [ETLCreateDate]                 DATETIME         NULL,
    CONSTRAINT [pk_SalesTransactionId] PRIMARY KEY CLUSTERED ([SalesTransactionId] ASC)
);

Nous voulons uniquement utiliser T-SQL pour effectuer cela sur une base de données existante.

Ressource utile en commentaire: générer des scripts de création SQL pour des tables existantes avec une requête

sql-server indexing sql-server-2016 data-warehouse columnstore

6 commentaires

avez-vous essayé le blog brent ozar?

Toutes vos tables de stockage de lignes ont-elles uniquement un index clusterisé de clé primaire et aucun autre index ou contrainte de clé étrangère à conserver?

J'ai écrit quelque chose de similaire l'année dernière pour archiver beaucoup de données. J'ai interrogé sys.tables et sys.columns et ainsi de suite pour obtenir toutes les références, puis j'ai écrit SQL dynamique pour générer des scripts qui produiraient la structure vers laquelle copier. Cela fonctionnerait-il pour vous? Avez-vous essayé d'obtenir des métadonnées de sys.tables pour travailler?

J'ai trouvé quelque chose que vous pouvez utiliser comme référence pour créer un script pour créer le code de votre table à partir de métadonnées. On dirait qu'il a tout ce dont vous avez besoin ici: stackoverflow.com/questions/706664/...

Si je ne me trompe pas, vous devez modifier votre contrainte de cluster de clé primaire sous 'current' pour pointer vers FactSalesTransactionId au lieu de 'SalesTransactionId'.

cette question est toujours ouverte, si quelqu'un veut ajouter des réponses plus optimisées, n'hésitez pas, merci

3 Réponses :

4
votes

Devart a répondu à cette question en demandant comment générer un script pour une table. J'ai modifié son code pour omettre la partie clé primaire et l'ai remplacée par des scripts de création de clé unique et de columnstore suivant son modèle général. J'ai également dû utiliser la réponse de marc_s à cette question sur la façon de savoir si la clé primaire d'une table est groupée ou non. J'ai mis tout cela dans une procédure stockée appelée #scriptTable. Voici ce qui est ci-dessous:

declare @rowId int = 1;
declare @table nvarchar(max);
declare @sql nvarchar(max);

while @rowId <= (select max(rowId) from @tables) begin
    select @table = name from @tables where rowId = @rowId;
    exec #scriptTable @table, @sql output;
    print (@sql); -- turn 'print' into 'exec' or otherwise use sp_executeSql
    set @rowId += 1;
end

Vous pouvez utiliser #scriptTable comme ceci:

declare @tables table (
    rowId int identity(1,1),
    name nvarchar(max)
);

insert      @tables
select      schema_name(schema_id) + '.' + name 
from        sys.tables
where       type_desc = 'user_table'

Remplacez «print» par «exec» ou utilisez sp_executeSql lorsque vous êtes prêt.

Pour l'utiliser sur toutes les tables, capturez d'abord les tables que vous souhaitez modifier:

declare @sql nvarchar(max);
exec #scriptTable 'dbo.Fact_SalesTransaction', @sql output;
print (@sql);

Vous êtes maintenant prêt à boucler les tables et à appliquer #scriptTable:

create procedure #scriptTable
    @table_name sysname,
    @sql nvarchar(max) output
as

DECLARE 
      @object_name SYSNAME
    , @object_id INT

SELECT 
      @object_name = '[' + s.name + '].[' + o.name + ']'
    , @object_id = o.[object_id]
FROM sys.objects o WITH (NOWAIT)
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE s.name + '.' + o.name = @table_name
    AND o.[type] = 'U'
    AND o.is_ms_shipped = 0

;WITH index_column AS 
(
    SELECT 
          ic.[object_id]
        , ic.index_id
        , ic.is_descending_key
        , ic.is_included_column
        , c.name
    FROM sys.index_columns ic WITH (NOWAIT)
    JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
    WHERE ic.[object_id] = @object_id
),
fk_columns AS 
(
     SELECT 
          k.constraint_object_id
        , cname = c.name
        , rcname = rc.name
    FROM sys.foreign_key_columns k WITH (NOWAIT)
    JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id 
    JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
    WHERE k.parent_object_id = @object_id
)
SELECT @sql = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
    SELECT CHAR(9) + ', [' + c.name + '] ' + 
        CASE WHEN c.is_computed = 1
            THEN 'AS ' + cc.[definition] 
            ELSE UPPER(tp.name) + 
                CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') 
                       THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                     WHEN tp.name = 'decimal' 
                       THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                    ELSE ''
                END +
                CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
                CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
                CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END + 
                CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END 
        END + CHAR(13)
    FROM sys.columns c WITH (NOWAIT)
    JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
    LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
    LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
    LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
    WHERE c.[object_id] = @object_id
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
    + ')'  + CHAR(13)
    + ISNULL((SELECT (
        SELECT CHAR(13) +
             'ALTER TABLE ' + @object_name + ' WITH' 
            + CASE WHEN fk.is_not_trusted = 1 
                THEN ' NOCHECK' 
                ELSE ' CHECK' 
              END + 
              ' ADD CONSTRAINT [' + fk.name  + '] FOREIGN KEY(' 
              + STUFF((
                SELECT ', [' + k.cname + ']'
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')' +
              ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
              + STUFF((
                SELECT ', [' + k.rcname + ']'
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
               + ')'
            + CASE 
                WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE' 
                WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
                WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT' 
                ELSE '' 
              END
            + CASE 
                WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
                WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
                WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'  
                ELSE '' 
              END 
            + CHAR(13) + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.name  + ']' + CHAR(13)
        FROM sys.foreign_keys fk WITH (NOWAIT)
        JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
        WHERE fk.parent_object_id = @object_id
        FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
    + ISNULL(((SELECT
         CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END 
                + ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @object_name + ' (' +
                STUFF((
                SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
                FROM index_column c
                WHERE c.is_included_column = 0
                    AND c.index_id = i.index_id
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'  
                + ISNULL(CHAR(13) + 'INCLUDE (' + 
                    STUFF((
                    SELECT ', [' + c.name + ']'
                    FROM index_column c
                    WHERE c.is_included_column = 1
                        AND c.index_id = i.index_id
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '')  + CHAR(13)
        FROM sys.indexes i WITH (NOWAIT)
        WHERE i.[object_id] = @object_id
            AND i.is_primary_key = 0
            AND i.[type] = 2
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
    ), '')
    + char(13)

-- Replaced "create primary key" logic in the original with what's below:

    +   ISNULL(
            (
                select  'create clustered columnstore index [ccx_' + @table_name + '] on ' + @object_name + char(13) +
                        'create unique ' + convert(nvarchar(max), i.type_desc) + ' index [' + replace(k.name, 'pk_', 'unx_') + '] ' +
                            'on ' + @object_name + ' (' + 
                                (
                                    SELECT STUFF((
                                        SELECT  ', [' + ic.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
                                        FROM    index_column ic
                                        WHERE   ic.is_included_column = 0
                                        AND     ic.[object_id] = k.parent_object_id 
                                        AND     ic.index_id = k.unique_index_id     
                                        FOR XML PATH(N''), TYPE
                                    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
                                ) + 
                            ')' + CHAR(13)
                FROM sys.key_constraints k WITH (NOWAIT)
                join sys.indexes i with (nowait) on k.unique_index_id = i.index_id and k.parent_object_id = i.object_id
                WHERE k.parent_object_id = @object_id 
                    AND k.[type] = 'PK'
            ), 
            ''
        );

Comme précédemment, remplacez «print» par «exec» ou utilisez sp_executeSql lorsque vous êtes prêt.

Notez que ma partie peut nécessiter des modifications supplémentaires si vous avez des noms de table qui nécessiteront des crochets autour d'eux.

EDIT: Mise à jour un peu du code pour simplifier (légèrement) et travailler avec les noms de table nécessitant des crochets autour d'eux.

1 commentaires

Voilà, cette réponse de devart renvoie à son tour à quelques excellents articles que les personnes intéressées par ce genre de choses devraient lire.

1
votes

Pour la création de CCI sur toutes les tables, vous pouvez utiliser ce qui suit:

    DECLARE @SQLscript nVARCHAR(1000) =
'CREATE CLUSTERED COLUMNSTORE INDEX &&& ON @@@ WITH (DROP_EXISTING = OFF,COMPRESSION_DELAY = 0)'

declare @tables table (
    rowId int identity(1,1),
    name nvarchar(max)
);

insert      @tables
select      schema_name(schema_id) + '.' + name 
from        sys.tables
where       type_desc = 'user_table'

declare @rowId int = 1;
declare @table nvarchar(max);
declare @sql nvarchar(max);

while @rowId <= (select max(rowId) from @tables) begin
    select @table = name from @tables where rowId = @rowId;
    SET @SQLscript = REPLACE(REPLACE(@SQLscript, '@@@',@table) ,'&&&','cci_' + SUBSTRING(REPLACE(@table,'].[','_'),CHARINDEX('.',REPLACE(@table,'].[','_'))+1,LEN(REPLACE(@table,'].[','_'))))
 BEGIN TRY 
  EXEC  sp_executeSql @SQLscript; 
  END TRY 
  BEGIN CATCH 
  PRINT @SQLscript
  END CATCH 

    SET @SQLscript = 'CREATE CLUSTERED COLUMNSTORE INDEX &&& ON @@@ WITH (DROP_EXISTING = OFF,COMPRESSION_DELAY = 0)'
    set @rowId += 1;
end

0 commentaires

0
votes

La réponse de @pwilcox est juste, sauf pour la dernière partie où il crache le code de l'index du rowstore. Cet index ne doit pas être mis en cluster car la ligne ci-dessus crée déjà l'index clustered columnstore.

Réparer:

-- Replaced "create primary key" logic in the original with what's below:

    +   ISNULL(
            (
                select  'create clustered columnstore index [ccx_' + @table_name + '] on ' + @object_name + char(13) +
                        'create unique index [' + replace(k.name, 'pk_', 'unx_') + '] ' +
                            'on ' + @object_name + ' (' + 
                                (
                                    SELECT STUFF((
                                        SELECT  ', [' + ic.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
                                        FROM    index_column ic
                                        WHERE   ic.is_included_column = 0
                                        AND     ic.[object_id] = k.parent_object_id 
                                        AND     ic.index_id = k.unique_index_id     
                                        FOR XML PATH(N''), TYPE
                                    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
                                ) + 
                            ')' + CHAR(13)
                FROM sys.key_constraints k WITH (NOWAIT)
                join sys.indexes i with (nowait) on k.unique_index_id = i.index_id and k.parent_object_id = i.object_id
                WHERE k.parent_object_id = @object_id 
                    AND k.[type] = 'PK'
            ), 
            ''
        );

0 commentaires