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:
- Ignorare le External Tables
- Non possibile nel piano di manutenzione database di SSMS, è necessario utilizzare soluzioni di terze parti
- 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!