Ces deux procédures à mettre dans vos bases permettent de temps à autre de reconstruire tout ce qui stocke des données et de recalculer toutes les statistiques hors index.
CREATE PROCEDURE S_MAINT.P_REBUILD_ALL_TABLE
AS
/******************************************************************************
* Maintenance grossière : reconstruction de toutes les tables et index *
*******************************************************************************
* Frédéric Brouard - SQLpro - MVP SQL Server - www.sqlspot.com - 2011-05-19 *
*******************************************************************************
* Cette procédure effectue une reconstruction de toutes les structures de *
* stockage : table, index de table, index de vue *
*******************************************************************************/
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max);
-- recherche de tous les objets à reconstruire
DECLARE C CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
WITH
T AS (SELECT DISTINCT s.name AS S_NAME, o.name AS O_NAME
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE o."type" IN ('U', 'V'))
SELECT 'ALTER INDEX ALL ON ['+ S_NAME +'].[' + O_NAME + '] REBUILD WITH (FILLFACTOR = 90);' AS SQL_COMMAND
FROM T;
OPEN C;
FETCH C INTO @SQL;
-- boucle sur tous les objets
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@SQL); --> exécution de la reconstruction
FETCH C INTO @SQL;
END;
CLOSE C;
DEALLOCATE C;
GO
------------------------------------------------------------------------------
CREATE PROCEDURE S_MAINT.P_UPDATE_ALL_STATS
AS
/******************************************************************************
* Maintenance grossière : recalcul de toutes les statistiques hors index *
*******************************************************************************
* Frédéric Brouard - SQLpro - MVP SQL Server - www.sqlspot.com - 2011-05-19 *
*******************************************************************************
* Cette procédure effectue un reclacul de toutes les statistiques hors index *
* en mode de balayage complet (sans échantillon) *
*******************************************************************************/
SET NOCOUNT ON;
DECLARE @T NVARCHAR(261), @S sysname, @SQL VARCHAR(max);
-- recherche de toutes les statistiques à recalculer
DECLARE C CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT '[' + c.name +'].['+ o.name +']' AS T, a.name AS S
FROM sys.stats AS a
INNER JOIN sys.objects AS o
ON o.object_id = a.object_id
INNER JOIN sys.schemas AS c
ON o.schema_id = c.schema_id
WHERE o."type" IN ('U', 'V')
AND auto_created = 1
AND user_created = 0;
OPEN C;
FETCH C INTO @T, @S;
-- boucle sur tous les objets
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'UPDATE STATISTICS ' + @T +' [' + @S + '] WITH FULLSCAN;'
EXEC (@SQL); --> exécution du recalcul
FETCH C INTO @T, @S;
END
CLOSE C;
DEALLOCATE C
GO
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
merci SQLpro pour ce script qui m’appariât très intéressant
par comptre quel serai le script qui doit être utiliser pour attaquer le reste de mes statistique
cad les statistique de mes index car selon votre article vous attaquer que les statistic qui sont hors index
Sur le sujet il existe différentes méthodes…. Je préfère la version avec WHILE EXISTS(…) SELECT TOP 1 …
A lire : http://sqlpro.developpez.com/cours/sqlserver/MSSQLServer_avoidCursor/
@Elsuket : Bien joué
la méthode que tu proposes contourne l’utilisation de curseur … et donne des idées pour des traitements de ce genre.
A+
Une autre façon de faire :