Indexen Onderhouden is altijd een heikel punt. Dit onderhouden kan via een automatische job worden uitgevoerd, wil je echter meer controle over de genomen stappen kan je het beste zelf een script maken. Op internet zijn natuurlijk voldoende voorbeelden te vinden. Ook ik heb daar een script gevonden, dat regelmatig wordt beschreven, en genoemd op het internet, maar hier nog eens wordt toegelicht in het Nederlands.
Ik heb het script een beetje aangepast zodat in een bestand wordt gelogd wanneer en welke index opnieuw is gedefragmenteerd (“DBCC INDEXDEFRAG” voor SQL2000, “ALTER INDEX REORGANIZE” voor SQL2005). Doordat de gegevens worden gelogd kan ik kijken hoe vaak een index opnieuw wordt gereorganiseerd.
De hieronder uitgewerkte procedure loopt bij mij op een specifieke database dagelijks elk kwartier. Deze manier van indexeren kan worden gedaan onder werktijden is echter minder efficiënt.
Sie de volgende Internet Artikelen:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
http://www.exforsys.com/tutorials/sql-server-2005/maintaining-sql-server-indexes.html
De door mij gebruikte database is een database die gebruikt wordt om roosters die in GPUntis worden gemaakt te kunnen presenteren op een webpagina. Omdat tijdens het updaten van de roosters 2 tabellen veel wijzigingen voor de kiezen krijgen (ca. 10.000 en 30.000 regels per rooster update, die tot 10 keer per dag kan voorkomen) is de logische index als snel erg vervuilt.
Om dit probleem onder controle te krijgen loopt het onderstaande script.
In de nacht worden dan de tabellen volledig opnieuw opgebouwd d.m.v. een Reindex (“DBCC REINDEX” voor SQL2000, “ALTER INDEX REBUILD” voor SQL2005) Hierbij moet men rekening houden dat er locks op de database komen dus echt iets voor de nachtelijke uren. Deze manier van indexeren is wel meer efficient omdat de index wordt verwijderd en geheel opnieuw wordt opgebouwd.
Als laatste een zeer belangriijk aandachtspunt, schakel Auto Shrink uit op productie systemen omdat tijdens een auto shrink de indexen worden gefragmenteerd, waardoor performance isseus kunnen ontstaan. Tevens gebruikt een auto shrink ook veel resources dat ook weer tot performance isseus kan leiden.
Internet artikel:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Dan de genoemde scripts:
-- Creeren van een logtabel!
CREATE TABLE [dbo].[HSZ_BT_LogDefragIndex](
[Datum] [datetime] NULL,
[Omschrijving] [varchar](300) NULL
) ON [PRIMARY]
Het script (SQL 2000) voor het controleren van de indexen en als de logische fragmentatie is meer dan 30 dan wordt die index gereorganiseerd.
==========================================================================================
-- Author: Bob Tossaint copied from http://msdn.microsoft.com/en-us/library/ms177571.aspx
-- Create date: 20081104
-- Change date: 20081104
-- Description: Reindex uitvoeren op die indexen waarvan de LogicalFrag hoger is dan 30 procent
-- ============================================================================================ALTER PROCEDURE [dbo].[HSZ_BT_ControleIndexEnIndexDefragWhenNeeded]
AS
BEGIN
/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr varchar(400);
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
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);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @tablename;
END
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO dbo.HSZ_BT_LogDefragIndex (Datum,Omschrijving)
values(Getdate(),'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%');
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')';
EXEC (@execstr);
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
END
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
END
Laatst aangepast op Donderdag 23 September 2010 07:54
Geschreven door Bob Tossaint
Donderdag 06 November 2008 09:54
