Tabelle e stored procedures inusate su SQL Server

Nella vita di un amministratore di un datawarehouse su SQL Server prima o poi capita la necessità di voler “mettere in ordine la stanza” buttando via (oopss… archiviando opportunamente) tutta quella spazzatura che si è accumulata negli anni sia per attività di sviluppo richieste ma mai completate, sia per richeste completate ma magari mai usate. A questo si aggiunge sicuramente la pletora delle tabelle/viste/store procedure “temporanee” che di volta in volta si sono aggiunte lì…

Ora, per capire se cancellare o non cancellare, le strategie restano due:

• a spanne si scorre l’elenco degli oggetti e si rinominano aspettando qualche settimana che “forse” un utente lamenti un disservizio
• si esegue qualche verifica… e si fa esattamente quanto sopra.

Identificare le stored procedure eseguite:

La tabella sys.dm_exec_procedure_stats è basilare per capire cosa si sia eseguito in periodi recenti: questa tabella tiene traccia delle statistiche di esecuzione delle stored procedure che sono ancora in cache. Le logiche con cui una cosa stia nella cache non è che sono chiarissime, per cui anche questa tabella non è proprio eccezionalmente attendibile.

Comunque sia io l’ho usata così:

nel mio datawarehouse ho creato una tabella SPUSATE e ho creato un job con SQL Server Agen che accoda ogni 5 minuti eventuali nuove procedure utilizzate:

WITH U AS (
SELECT d.object_id, DB_NAME(d.database_id) database_name, OBJECT_NAME(object_id, database_id) 'proc name',
d.cached_time, d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
WHERE DB_NAME(d.database_id) = 'DWH'
)
MERGE DWH.dbo.SPUSATE S USING U ON S.object_id = U.object_id AND S.database_name = U.database_name
WHEN NOT MATCHED BY TARGET THEN
INSERT (object_id, database_name, [proc name], cached_time, last_execution_time, total_elapsed_time, [avg_elapsed_time],last_elapsed_time, execution_count)
VALUES (U.object_id, U.database_name, U.[proc name], U.cached_time, U.last_execution_time, U.total_elapsed_time, U.[avg_elapsed_time], U.last_elapsed_time, U.execution_count)
WHEN MATCHED AND (S.last_execution_time <> U.last_execution_time) THEN
UPDATE SET
cached_time = U.cached_time,
last_execution_time = U.last_execution_time,
total_elapsed_time = U.total_elapsed_time,
[avg_elapsed_time] = U.[avg_elapsed_time],
last_elapsed_time = U.last_elapsed_time,
execution_count = U.execution_count;

Dopo una settimana ho l’elenco delle procedure che sicuramente sono state utilizzate.

Identificare le tabelle inusate:

Per questa attività ci avvaliamo di sys.dm_db_index_usage_stats, che fornisce le statistiche sull’utilizzo agli indici, compresa la chiave cluster.

Io ho usato il seguente codice:

WITH UUT (TableName, TotalRowCount, CreatedDate , LastModifiedDate )
AS (
SELECT DBTable.name AS TableName, PS.row_count AS TotalRowCount ,DBTable.create_date AS CreatedDate, DBTable.modify_date AS LastModifiedDate
FROM sys.all_objects DBTable
JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name
WHERE DBTable.type ='U'
AND NOT EXISTS (
SELECT OBJECT_ID
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = DBTable.object_id
)
)
SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate
FROM UUT
ORDER BY TotalRowCount ASC<

Che fornisce l’elenco delle tabelle per le quali non  è stato usato nessun indice dall’ultimo riavvio di SQL Server (dubbi su quando è stato riavviato? Eseguite SELECT sqlserver_start_time AS LastSQLServiceRestart FROM sys.dm_os_sys_info)

Spostare in attesa di cancellare…

Io, prima di cancellare, ho isolato gli oggetti:

• ho creato uno schema UnusedObjs
• ci ho spostato dentro le tabelle, le stored procedure e le viste che ho ritenuto candidate alla cancellazione

Per creare lo schema si utilizza banalmente

CREATE SCHEMA UnusedObjs

Poi, uno per uno, ci si spostano gli oggetti:

ALTER SCHEMA UnusedObjs TRANSFER dbo.<nomeoggetto

Poi si aspetta che qualcuno chiami o che qualche job schedulato vada in errore.