1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189
| CREATE PROCEDURE SP_DEFRAG_AUTO
/******************************************************************************
* DEFRAGMENTATION AUTOMATIQUE DES INDEX PAR SEUIL EN ORDRE ALÉATOIRE *
*******************************************************************************
* Copyright DATA SAPIENS - Frédéric Brouard - 11 février 2005 *
******************************************************************************/
@HEURE_LIMITE VARCHAR(5) = '08:00', -- doit être de la forme '08:30' : heure limite de début de la dernière opération de défragmentation
@RATIO_EXTENTSWITCHES FLOAT = 1.3, -- doit être d'au moins 1. Valeur inférieure à 1 ignorée. Valeur conseillée pour déclencher une défragementation : 1.3 à 2.0 Déclenche la défragmentation si ExtentSwitches / Extents >= @RATIO_EXTENTSWITCHES alors la défragmentation a lieu
@RATIO_COUNT FLOAT = 1.3, -- doit être d'au moins 1. Valeur inférieure à 1 ignorée. Valeur conseillée pour déclencher une défragementation : 1.3 à 2.0 Déclenche la défragmentation si BestCount / ActualCount >= @RATIO_COUNT alors la défragmentation a lieu
@RATIO_LOGICALFRAG FLOAT = 0.2 -- doit être d'au plus 1. Valeur supérieure à 1 ignorée. Valeur conseillée pour déclencher une défragementation : 0.2 à 0.4. Déclenche la défragmentation si LogicalFrag / CountPages >= @RATIO_LOGICALFRAG
AS
DECLARE @TABLE_NAME VARCHAR (128)
DECLARE @QUERY VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @objectName VARCHAR(256)
DECLARE @indexName VARCHAR(256)
DECLARE @GUID uniqueidentifier
DECLARE @T_BEFORE FLOAT
DECLARE @T_AFTER FLOAT
DECLARE @H INT
DECLARE @M INT
DECLARE @S INT
DECLARE @DATABASE VARCHAR(128)
SET NOCOUNT ON
SET @DATABASE = DB_NAME(DB_ID())
-- Curseur sur les tables d'exploitation à l'exclusion des tables systèmes et des vues
DECLARE C_TABLES CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME NOT IN (
'sysusers',
'systypes',
'syssubscriptions',
'sysreferences',
'syspublications',
'sysprotects',
'syspermissions',
'sysobjects',
'sysmergesubsetfilters',
'sysmergesubscriptions',
'sysmergeschemachange',
'sysmergepublications',
'sysmergearticles',
'sysmembers',
'sysindexkeys',
'sysindexes',
'sysfulltextcatalogs',
'sysforeignkeys',
'sysfiles',
'sysfilegroups',
'sysdepends',
'sysconstraints',
'syscomments',
'syscolumns',
'sysarticleupdates',
'sysarticles',
'Mssubscriptions',
'MSsubscription_properties',
'MSsubscriber_schedule',
'MSsubscriber_info',
'MSsnapshot_history',
'MSsnapshot_agents',
'MSreplication_subscriptions',
'MSreplication_objects',
'MSrepl_version',
'MSrepl_transactions',
'MSrepl_originators',
'MSrepl_errors',
'MSrepl_commands',
'Mspublisher_databases',
'Mspublications',
'MSpublication_access',
'MSmerge_tombstone',
'MSmerge_subscriptions',
'MSmerge_replinfo',
'MSmerge_history',
'MSmerge_genhistory',
'MSmerge_delete_conflicts',
'MSmerge_contents',
'MSmerge_agents',
'MSlogreader_history',
'MSlogreader_agents',
'MSdistributor',
'MSdistributiondbs',
'MSdistribution_history',
'MSdistribution_agents',
'MSdistpublishers',
'MSarticles',
'MSagent_profiles',
'MSagent_parameters'
)
-- Creation de la table des informations de défragmentation
CREATE TABLE #T_INDEXFRAG (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Ouverture du curseur pour balayer toutes les tables
OPEN C_TABLES
-- Lecture première lignes
FETCH NEXT FROM C_TABLES INTO @TABLE_NAME
-- Boucle de balayage
WHILE @@FETCH_STATUS = 0
BEGIN
-- Execute DBCC SHOWCONTIG sur chaque table visée
INSERT INTO #T_INDEXFRAG
EXEC ('DBCC SHOWCONTIG (''' + @TABLE_NAME + ''')
WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT FROM C_TABLES INTO @TABLE_NAME
END
-- Fermeture du curseur et désallocation de l'espace mémoire
CLOSE C_TABLES
DEALLOCATE C_TABLES
-- ouverure du curseur sur les index fragmentés d'après les critères fournit
DECLARE C_INDEX CURSOR
FOR
SELECT NEWID() as GUID, ObjectId, IndexId, ObjectName, IndexName
FROM #T_INDEXFRAG
WHERE CAST(ExtentSwitches AS FLOAT) / CAST(Extents AS FLOAT) >= @RATIO_EXTENTSWITCHES
OR CAST(BestCount AS FLOAT) / CAST(ActualCount AS FLOAT) >= @RATIO_COUNT
OR CAST(LogicalFrag AS FLOAT) / CAST(CountPages AS FLOAT) >= @RATIO_LOGICALFRAG
ORDER BY GUID
OPEN C_INDEX
-- lecture du premier index à défragmenter
FETCH NEXT FROM C_INDEX INTO @GUID, @objectid, @indexid, @objectName, @indexName
-- boucle sur les index
WHILE @@FETCH_STATUS = 0
BEGIN
SET @objectName = RTRIM(@objectName)
SET @indexName = RTRIM(@indexName)
IF SUBSTRING(CONVERT(CHAR(23), CURRENT_TIMESTAMP, 121), 12, 5) >= @HEURE_LIMITE
BREAK
SET @T_BEFORE = CAST(CURRENT_TIMESTAMP AS FLOAT)
SELECT @QUERY = 'DBCC INDEXDEFRAG (db_name(), ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')'
EXEC (@QUERY)
SET @T_AFTER = CAST(CURRENT_TIMESTAMP AS FLOAT)
SET @T_AFTER = (@T_AFTER - @T_BEFORE) * 24
SET @H = FLOOR(@T_AFTER)
SET @T_AFTER = (@T_AFTER - FLOOR(@T_AFTER)) * 60
SET @M = FLOOR(@T_AFTER)
SET @T_AFTER = (@T_AFTER - FLOOR(@T_AFTER)) * 60
SET @S = FLOOR(@T_AFTER)
RAISERROR('Défragmentation effectuée dans la base %s pour l''index %s de l''objet %s en %d heures, %d minutes, %d secondes' , 0, 1, @DATABASE, @indexName, @objectName, @H, @M, @S) WITH LOG
FETCH NEXT FROM C_INDEX INTO @GUID, @objectid, @indexid, @objectName, @indexName
END
CLOSE C_INDEX
DEALLOCATE C_INDEX
DROP TABLE #T_INDEXFRAG
GO |
Partager