gebeurtenis
31 mrt, 23 - 2 apr, 23
De grootste SQL-, Fabric- en Power BI-leerevenement. 31 maart – 2 april. Gebruik code FABINSIDER om $ 400 te besparen.
Zorg dat u zich vandaag nog registreertDBCC INDEXDEFRAG (Transact-SQL)
van toepassing op:
SQL ServerAzure SQL Managed Instance
Indexen van de opgegeven tabel of weergave defragmenteren.
Belangrijk
Deze functie wordt verwijderd in een toekomstige versie van SQL Server. Vermijd het gebruik van deze functie in nieuwe ontwikkelwerkzaamheden en plan om toepassingen te wijzigen die momenteel gebruikmaken van deze functie. Gebruik in plaats daarvan ALTER INDEX.
Van toepassing op: SQL Server 2008 (10.0.x) en latere versies
Transact-SQL syntaxisconventies
syntaxsql
DBCC INDEXDEFRAG
(
{ database_name | database_id | 0 }
, { table_name | table_id | view_name | view_id }
[ , { index_name | index_id } [ , { partition_number | 0 } ] ]
)
[ WITH NO_INFOMSGS ]
De database die de index bevat om te defragmenteren. Als 0 is opgegeven, wordt de huidige database gebruikt. Databasenamen moeten voldoen aan de regels voor id's.
De tabel of weergave die de index bevat om te defragmenteren. Namen van tabellen en weergaven moeten voldoen aan de regels voor id's.
De naam of id van de index om te defragmenteren. Als dit niet is opgegeven, defragmenteert de instructie alle indexen van de opgegeven tabel of weergave. Indexnamen moeten voldoen aan de regels voor id's.
Het partitienummer van de index om te defragmenteren. Als dit niet is opgegeven of als 0 is opgegeven, defragmenteert de instructie alle partities in de opgegeven index.
Onderdrukt alle informatieve berichten met ernstniveaus van 0 tot en met 10.
DBCC INDEXDEFRAG het bladniveau van een index defragmenteert, zodat de fysieke volgorde van de pagina's overeenkomt met de logische volgorde van links naar rechts van de leaf-knooppunten, waardoor de prestaties van indexscans worden verbeterd.
Notitie
Wanneer DBCC INDEXDEFRAG wordt uitgevoerd, vindt indexdefragmentatie serieel plaats. Dit betekent dat de bewerking op één index wordt uitgevoerd met één thread. Er treedt geen parallelle uitvoering op. Bewerkingen op meerdere indexen van dezelfde DBCC INDEXDEFRAG instructie worden ook uitgevoerd op één index tegelijk.
DBCC INDEXDEFRAG ook de pagina's van een index comprimeren, rekening houdend met de opvulfactor die is opgegeven toen de index werd gemaakt. Lege pagina's die zijn gemaakt vanwege deze compressie, worden verwijderd. Zie Vulfactor opgeven voor een index-voor meer informatie.
Als een index meer dan één bestand omvat, DBCC INDEXDEFRAG één bestand tegelijk defragmenteert. Pagina's worden niet gemigreerd tussen bestanden.
DBCC INDEXDEFRAG rapporteert dat het geschatte percentage elke vijf minuten is voltooid.
DBCC INDEXDEFRAG kan op elk moment in het proces worden gestopt en blijft voltooid werk behouden.
In tegenstelling tot DBCC DBREINDEX, of de indexbouwbewerking in het algemeen, is DBCC INDEXDEFRAG een onlinebewerking. Het houdt geen sloten op de lange termijn vast. Daarom blokkeert DBCC INDEXDEFRAG het uitvoeren van query's of updates niet. Omdat de tijd om te defragmenteren is gerelateerd aan het niveau van fragmentatie, kan een relatief niet-gefragmenteerde index sneller worden gedefragmenteerd dan een nieuwe index kan worden gebouwd. Een sterk gefragmenteerde index kan aanzienlijk langer duren om te defragmenteren dan opnieuw te bouwen.
De defragmentatie wordt altijd volledig geregistreerd, ongeacht de instelling voor het databaseherstelmodel. Zie ALTER DATABASE (Transact-SQL)voor meer informatie. De defragmentatie van een sterk gefragmenteerde index kan meer logboeken genereren dan het maken van een volledig geregistreerde index. De defragmentatie wordt echter uitgevoerd als een reeks korte transacties, dus een groot logboek is niet nodig als logboekback-ups regelmatig worden gemaakt of als de instelling van het herstelmodel EENVOUDIG is.
DBCC INDEXDEFRAG indexbladpagina's in willekeurige volgorde plaatst. Als een index daarom wordt gekoppeld aan andere indexen op schijf, maakt het uitvoeren van DBCC INDEXDEFRAG voor die index niet alle bladpagina's in de index aaneengesloten. Bouw de index opnieuw om de clustering van pagina's te verbeteren.
DBCC INDEXDEFRAG kan niet worden gebruikt om de volgende indexen te defragmenteren:
- Een uitgeschakelde index.
- Een index met paginavergrendeling ingesteld op UIT.
- Een ruimtelijke index.
DBCC INDEXDEFRAG wordt niet ondersteund voor gebruik in systeemtabellen.
DBCC INDEXDEFRAG retourneert de volgende resultatenset (waarden kunnen variëren) als een index is opgegeven in de instructie (tenzij WITH NO_INFOMSGS is opgegeven):
Output
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
359 346 8
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Aanroeper moet eigenaar zijn van de tabel of lid zijn van de sysadmin vaste serverfunctie, de db_owner vaste databaserol of de db_ddladmin vaste databaserol.
In het volgende voorbeeld worden alle partities van de PK_Product_ProductID-index in de Production.Product tabel in de AdventureWorks2022-database gedefragmenteert.
SQL
DBCC INDEXDEFRAG (AdventureWorks2022, 'Production.Product', PK_Product_ProductID);
GO
In het volgende voorbeeld ziet u een eenvoudige manier om alle indexen in een database te defragmenteren die zijn gefragmenteerd boven een gedeclareerde drempelwaarde.
SQL
/*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
PRINT '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;
GO
- DBCC (Transact-SQL)
- sys.dm_db_index_physical_stats (Transact-SQL)
- CREATE TABLE (Transact-SQL)
- ALTER TABLE (Transact-SQL)
- ALTER INDEX (Transact-SQL)