Aggiornamento statistiche SQL per database che contengono External Table PolyBase

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
2. 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!