Quatrième article de la série Une approche pragmatique de la production SQL Server, nous voyons ici la mise à jour des statistiques.
Quand SQL Server met-il à jour les statistiques ?
- Lors de la réorganisation d’un indexe. Les statistiques sont dans ce cas mises à jour à partir de l’intégralité des pages de l’indexe (mode FULLSCAN)
- Lorsque la base est en mode “auto_update_statistics”, que le seuil d’obsolescence d’une statistique a été atteint (article MSDN sur le sujet) et que la compilation d’une requête a besoin de cette statistique.
Faut-il mettre à jour les statistiques manuellement ?
Oui et Non !
Non, lorsque les bases sont de faible volumétrie et que l’activité transactionnelle n’est pas élevée; la gestion automatique des statistiques suffit amplement.
Oui, lorsque l’on sait que les statistiques ne sont plus représentatives bien avant le seuil des 20% (données réparties de manière non homogène dans certaines tables), ou lorsque l’on ne peut pas se permettre d’ajouter à une compilation le temps mis à mettre à jour les statistiques.
Dans le cadre d’un parc d’instances SQL Server, il est intéressant de disposer d’une tâche de gestion des statistiques exécutée sur toutes les instances qui mettra à jour ou non les statistiques selon quelques règles.
Quelles statistiques mettre à jour ?
Chaque base de données peut contenir des données très volatiles et d’autres statiques, comme par exemple des tables d’historique, souvent volumineuses. Certaines tables peuvent être des tables de travail, régulièrement purgées, pour lesquelles les statistiques ne seront jamais pertinentes.
Il faut donc trouver un mécanisme capable d’identifier les tables de travail et les tables qui n’ont pas subi de modification de données (depuis la dernière mise à jour).
Lorsqu’une donnée est mise à jour dans une table, la valeur de la colonne rowmdctr de la table système sysindexes s’incrémente(MSDN: sysindexes). Cette colonne est remise à zéro lorsque les statistiques sont mises à jour. Récupérer la liste des statistiques qui ne sont pas totalement à jour consiste donc simplement à remonter les lignes de sysindexes à l’exception de celles pour lesquelles rowmodctr vaut 0 !
(Comme son nom ne l’indique pas, sysindexes contient une entrée pour chaque indexe mais aussi pour chaque statistique de la base).
La mise à jour peut s’effectuer sur l’intégralité de l’objet (FULLSCAN) ou sur un échantillon (SAMPLE). La première méthode est plus longue que la seconde puisque les pages balayées sont plus nombreuses. Il existe un risque, même s’il est faible, qu’une statistique créée à partir d’un échantillon ne soit pas représentative (le mécanisme de statistiques filtrées de SQL Server 2008 peut aider à contourner ce problème). Pour les tables de faible volumétrie, la mise à jour en FULLSCAN est préférable. Pour les plus gros objets, le mode SAMPLE peut être utilisé, mais tâchez d’être toujours au moins à 20% de pages balayées.
Attention, la mise à jour des statistiques a un double impact sur les performances:
La lecture des pages de chaque objet s’accompagne d’une pose de verrous partagés, ils entrent donc en concurrence avec les accès en modification sur l’objet.
Les plans d’exécution en cache qui utilisent les statistiques mis à jour sont invalidés. Les prochaines exécutions de requêtes passeront donc nécessairement par la phase de compilation.
il est donc préférable d’effectuer cette mise à jour lors d’une période de faible activité.
Voici un exemple de procédure stockée de mise à jour de statistiques qui repose sur ces quelques règles :
USE outils_dba
GO
IF OBJECT_ID('maj_statistiques_base') IS NOT NULL
DROP PROCEDURE maj_statistiques_base
GO
CREATE PROCEDURE maj_statistiques_base ( @nom_base sysname,
@complet varchar(3)='NON'
)
AS
/***************************************************************************************
B. Vesan, Cap Data Consulting, Mars 2012
Cette procedure permet de mettre à jour les statistiques d'une base.
Si la valeur du paramètre @complet vaut 'OUI', l'intégralité des statistiques sera mise à jour.
Sinon, seules les statistiques sur les tables ayant reçu des mouvements seront mises à jour.
La procédure retournera 0 en cas de succès, 1 en cas d'échec.
***************************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @cmd varchar(4000),@msg varchar(1000), @erreur int
CREATE TABLE #table_stats(commande varchar(4000))
IF DB_ID(@nom_base) IS NULL
BEGIN
SET @msg = 'Echec de l'' exécution de maj_statistiques_base : La base '+ISNULL(@nom_base,'(valeur nulle)')+' n''existe pas'
RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG
return 1
END
IF ( DATABASEPROPERTYEX(@nom_base,'Status ')!='ONLINE' OR EXISTS (select 1 FROM sys.databases WHERE name=@nom_base AND source_database_id IS NOT NULL))
BEGIN
SET @msg = 'La base '+@nom_base+' n''est pas disponible ou il s''agit d''un snapshot'
PRINT @msg
return 0
END
set @cmd = 'USE ['+@nom_base+']
select ''UPDATE STATISTICS ['+@nom_base+'].[''+SCH.name+''].[''+T.name+''](''+SI.name+'') WITH RESAMPLE''
from sysindexes SI
inner join sys.tables T ON T.object_id = SI.id
inner join sys.stats S ON (S.object_id = SI.id AND S.stats_id=SI.indid)
inner join sys.schemas SCH ON (T.schema_id = SCH.schema_id)'
if UPPER(@complet) ='NON' set @cmd = @cmd + ' WHERE SI.rowmodctr 0'
--PRINT @cmd
INSERT INTO #table_stats
EXECUTE(@cmd)
DECLARE CURSEUR_INDEXES INSENSITIVE CURSOR FOR
select commande from #table_stats
OPEN CURSEUR_INDEXES
FETCH NEXT FROM CURSEUR_INDEXES INTO @cmd
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT @cmd
EXECUTE(@cmd)
select @erreur=@@ERROR
IF @erreur !=0
BEGIN
SET @msg = 'Echec de l'' exécution de maj_statistiques_base : Erreur '+CAST(@erreur as varchar(5))+' lors de l''appel à la commande "'+@cmd+'"'
RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG
CLOSE CURSEUR_INDEXES
DEALLOCATE CURSEUR_INDEXES
DROP TABLE #table_stats
return 1
END
FETCH NEXT FROM CURSEUR_INDEXES INTO @cmd
END
CLOSE CURSEUR_INDEXES
DEALLOCATE CURSEUR_INDEXES
DROP TABLE #table_stats
END
GOContinuez votre lecture sur le blog :
- Production SQL Server: Réorganisation des objets (Benjamin VESAN) [SQL Server]
- Oracle et SQL Server: Les Statistiques (Benjamin VESAN) [OracleSQL Server]
- Production SQL Server : Sauvegardes (Benjamin VESAN) [SQL Server]
- Retrouver les tables dont les stats ne sont plus compilées en automatique (David Baffaleuf) [SQL Server]
- Production SQL Server : Contrôle de cohérence (Benjamin VESAN) [SQL Server]