Aggiornamento statistiche SQL per database che contengono External Table PolyBase

Articoli
Sergio Govoni

Recentemente, ho avuto l’occasione di configurare un piano di manutenzione database per un DB in cui sono state create alcune External Table PolyBase che permettono di raggiungere dati archiviati all’esterno dell’istanza SQL Server.

Non si poteva testare la manutenzione database contestualmente alla configurazione del piano e così ho atteso la finestra temporale dedicata a queste attività. All’ora indicata, il piano di manutenzione (configurazione classica) non è stato eseguito correttamente, le verifiche hanno successivamente rilevato un errore nel task di aggiornamento statistiche.

E’ stato restituito il seguente messaggio di errore durante l’esecuzione dello statement:

UPDATE STATISTICS [dbo].[ExternalTable] WITH FULLSCAN, COLUMNS

L’errore:

Message 46519, level 16, state 22
The object Update Statistics isn't supported on External Table.

Il caso è descritto nella documentazione del comando CREATE STATISTICS dove è evidenziato il testo “Updating statistics is not supported on external tables. To update statistics on an external table, drop and re-create the statistics” che indica espressamente che l’aggiornamento delle statistiche non è supportato per le External Table.

Le statistiche sulle External Table vengono gestite, dietro le quinte, in questo modo: SQL Server importa la tabella esterna in una tabella temporanea sul tempdb e quindi crea le statistiche. Per le statistiche create con il campionamento semplice, vengono importate solo le righe campionate. Per tabelle esterne di grandi dimensioni, è più veloce utilizzare il campionamento predefinito anziché l’opzione di scansione completa.

Per aggiornare le statistiche sulle External Table abbiamo quindi le seguenti opzioni:

  1. Ignorare le External Tables
  • Non possibile nel piano di manutenzione database di SSMS, è necessario utilizzare soluzioni di terze parti
  1. Eliminare le statistiche sulle External Tables, ricreandole al termine delle attività di manutenzione

Percorrendo la seconda opzione, ho sviluppato la stored procedure sp_recreate_stats_external_table che consiste di una CTE in grado di fornire in output i comandi T-SQL per eliminare e creare di nuovo le statistiche definite su tabelle esterne.

CREATE OR ALTER PROCEDURE dbo.sp_recreate_stats_external_table
AS BEGIN
  DECLARE
    -- Output table
    @DropCreateCmd TABLE
	(
	  SchemaName SYSNAME NOT NULL
      ,TableName SYSNAME NOT NULL
      ,ObjectType SYSNAME NOT NULL
      ,OperationType NCHAR(1) NOT NULL
      ,SQLCmd NVARCHAR(1024) NOT NULL
	);

  -- Generate CREATE STATISTICS statements
  WITH Stat AS
  (
    SELECT
      'A' AS RowType
      ,T.object_id
      ,T.stats_id
      ,T.StatLevel
      ,T.KeyOrdinal
      ,T.SchemaName
      ,T.TableName
      ,CAST('CREATE ' +
            'STATISTICS [' + TRIM(T.StatsName) +
            '] ON [' + TRIM(T.SchemaName) +
            '].[' + TRIM(T.TableName) +
            '] ( ' AS VARCHAR(MAX)) AS SQLCmd
    FROM
    (
      SELECT
        DISTINCT
        stat.object_id
        ,stat.stats_id
        ,CAST(0 AS INTEGER) AS StatLevel
        ,CAST(0 AS INTEGER) AS KeyOrdinal
        ,stat.name AS StatsName
        ,sch.name AS SchemaName
        ,obj.name AS TableName
      FROM
        sys.stats_columns AS statc
      JOIN
        sys.stats AS stat ON ((stat.stats_id = statc.stats_id)
                             AND (stat.object_id = statc.object_id))
      JOIN
        sys.objects AS obj ON statc.object_id = obj.object_id
      JOIN
        sys.external_tables external_tab ON (external_tab.object_id = obj.object_id)
                                        AND (external_tab.schema_id = obj.schema_id)
      JOIN
        sys.columns AS col ON ((col.column_id = statc.column_id)
                              AND (col.object_id = statc.object_id))
      JOIN
        sys.schemas AS sch ON obj.schema_id = sch.schema_id
    WHERE
      ((stat.auto_created = 1) OR (stat.user_created = 1))
      AND (obj.type = 'U')
    ) AS T

    UNION ALL

    SELECT
      'R' AS RowType
      ,statcol.object_id
      ,statcol.stats_id
      ,CAST(S.StatLevel + 1 AS INTEGER) AS IdxLevel
      ,CAST(statcol.stats_column_id AS INTEGER) KeyOrdinal
      ,S.SchemaName
      ,S.TableName
      ,CAST(S.SQLCmd + CASE(statcol.stats_column_id) WHEN 1 THEN '' ELSE ',' END +
              '[' + TRIM(col.name) +
              '] ' AS VARCHAR(MAX)) AS SQLCmd
    FROM
      Stat AS S
    JOIN
      sys.stats_columns AS statcol ON ((statcol.object_id = S.object_id)
                                      AND (statcol.stats_id = S.stats_id))
    JOIN
      sys.columns AS col ON ((col.column_id = statcol.column_id)
                        AND (col.object_id = statcol.object_id))
    WHERE
      (statcol.stats_column_id = (S.KeyOrdinal + 1))
  ),
  Stat2 AS
  (
    SELECT
      MAX(Stat.KeyOrdinal) AS MaxKeyOrdinal
      ,Stat.object_id
      ,Stat.stats_id
    FROM
      Stat
    JOIN
      sys.objects AS O ON O.object_id = Stat.object_id
    WHERE
      (Stat.RowType = 'R')
    GROUP BY
      Stat.object_id
      ,Stat.stats_id
  )
  INSERT INTO @DropCreateCmd
  (
    SchemaName
	,TableName
	,ObjectType
	,OperationType
	,SQLCmd
  )
  SELECT
    Stat.SchemaName
    ,Stat.TableName
    ,'STATS' AS ObjecType
    ,'C' AS OperationType
    ,Stat.SQLCmd + ') WITH FULLSCAN;'
  FROM
    Stat2
  JOIN
    Stat ON ((Stat.object_id = Stat2.object_id)
            AND (Stat.stats_id = Stat2.stats_id))
            AND (Stat.KeyOrdinal = Stat2.MaxKeyOrdinal);

  -- Generate DROP STATISTICS statements
  WITH StatsDrop AS
  (
    SELECT
      T.SchemaName
      ,T.TableName
      ,'STATS' AS ObjectType
      ,'D' AS OperationType
      ,'DROP STATISTICS [' +
	     TRIM(SchemaName) + '].[' +
		 TRIM(TableName) + '].[' +
  	     TRIM(StatisticName) + '];' AS SQLCmd

    FROM (
      SELECT
        sch.[Name] as SchemaName
        ,obj.[Name] as TableName
        ,stat.[Name] as StatisticName
      FROM
        sys.stats AS stat
      INNER JOIN
        sys.objects AS obj ON stat.[object_id] = obj.[object_id]
      INNER JOIN
        sys.external_tables external_tab ON (external_tab.[object_id] = obj.[object_id])
                                        AND (external_tab.[schema_id] = obj.[schema_id])
      INNER JOIN
        sys.schemas AS sch ON obj.[schema_id] = sch.[schema_id]
      WHERE
        ((stat.auto_created = 1) OR (stat.user_created = 1))
        AND (obj.type = 'U')
    ) AS T
  )
  INSERT INTO @DropCreateCmd
  (
    SchemaName
	,TableName
	,ObjectType
	,OperationType
	,SQLCmd
  )
  SELECT
    SchemaName
	,TableName
	,ObjectType
	,OperationType
	,SQLCmd
  FROM
    StatsDrop;

  SELECT
    SchemaName
	,TableName
	,ObjectType
	,OperationType
	,SQLCmd
  FROM
    @DropCreateCmd;

  RETURN;
END;

I comandi CREATE e DROP STATISTICS possono essere memorizzati all’interno di una tabella temporanea ed eseguiti separatamente. Le statistiche vengono eliminate prima dell’esecuzione del task di aggiornamento statistiche e create immediatamente dopo. È importante gestire correttamente gli errori per evitare di perdere l’output della stored procedure (in particolare i comandi di creazione statistiche) durante i task di manutenzione.

Buon divertimento!