SQL Server Defragment index fragmentation test
The way SQL server organizes indexes is just a few, and always smart to join the intelligent judgment very uncomfortable, or worse than Dbms_advisor:
1SQL SERVER 2000/2005
Inquire
2000
Use Db_name
DECLARE @table_id int
Set @table_id =object_id (' table_name ')
DBCC SHOWCONTIG (@table_id)
2005
SELECT Index_id,index_type_desc,avg_fragmentation_in_percent,page_count
From Sys.dm_db_index_physical_stats (db_id (), object_id (' table_name '), NULL, NULL, ' LIMITED '); |
Finishing
2000
DBCC Indexdefrag (db_name, ' dbo. table_name ', index_name)
DBCC dbreindex (' DB_Name.dbo.Table_Name ', ', 100)
2005
ALTER INDEX index_name on dbo. TABLE_NAME REBUILD |
Here is a good example of online search, the link is:
Http://www.cnblogs.com/perfectdesign/archive/2008/02/20/sqlserverreindexrebuild.html
Create a new table:
CREATE TABLE T3
(
I int primary key,
XX varchar (
$) NOT NULL
)
Add Data:
DECLARE @x int
Set @x =
0
While @x <
+
Begin
INSERT into T3 values (@x, ' qweasdqweasdqweasdqweqweasdqwe ')
Set @x = @x+
1
End
To perform a dynamic management view:
SELECT Index_id,index_type_desc,avg_fragmentation_in_percent,page_count
From Sys.dm_db_index_physical_stats
(DB_ID (), object_id (' t3 '), NULL, NULL, ' LIMITED ');
can see:
index_id 0 means this is a heap, and the average fragment has 33%.
There are several ways to reduce fragmentation that do not work and do not reduce fragmentation.
Including:
DBCC Indexdefrag (test, ' dbo.t3 ', pk__t3__0ea330e9)
Alter index PK__T3__0EA330E9 on T3
Rebuild
DBCC DBREINDEX (' T3 ')
These methods, as well as the deletion of the rebuild index, do not reduce the number of fragments.
Later I think because the data is too small, resulting in a few pages, the database may have some kind of intelligence, to determine whether it is worthwhile to do the work of rebuilding the index, so increase the amount of data:
DECLARE @x int
Set @x =
+
While @x <
10000
Begin
INSERT into T3 values (@x, ' qweasdqweasdqweasdqweqweasdqwe ')
Set @x = @x+
1
End
Execute the statement again:
SELECT Index_id,index_type_desc,avg_fragmentation_in_percent,page_count
From Sys.dm_db_index_physical_stats
(DB_ID (), object_id (' t3 '), NULL, NULL, ' LIMITED ');
Alter index t3index on T3 rebuild
It's showing up!
Conclusion:
SQL Server in the implementation of the relevant operations will be intelligent to determine whether it is worth doing, such as the number of pages is too small to rebuild the index, rebuild Reindex. Similarly, there are many intelligent judgments in SQL Server 2005 to ensure a large, yet intelligent design.
Like what:
Generate thresholds for query plans
Caching mechanisms, cache filtering, LRU algorithms
Pre-read mechanism
Checkpoint reducing rollback distance
Intelligent Join judgment
Re-compiling
Understanding SQL Server, a similar software product, can provide us with more ideas when designing a product, even if you know what's above is not going to help your SQL development much.
There are several different ways to attach the difference:
Reindex is a better choice, fast, but he can't operate online.
Indexdefrag is slow but can be operated online
Rebuild recommend that you use it when there are fewer fragments.
With the Microsoft Rebuild index script, you can see from the inside that Microsoft is based on the fragment size recommended, but this will depend on each different database.
--Ensure a Use <databasename> statement have been executed first.
SET NOCOUNT on;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar (
8000);
--Ensure the temporary table does not exist
IF EXISTS (SELECT name from sys.objects WHERE name = ' Work_to_do ')
DROP TABLE Work_to_do;
--Conditionally Select from the function, converting object and index IDs to names.
SELECT
object_id as Objectid,
index_id as IndexID,
Partition_number as Partitionnum,
Avg_fragmentation_in_percent as Frag
Into Work_to_do
From Sys.dm_db_index_physical_stats (db_id (), NULL, NULL, NULL, ' LIMITED ')
WHERE avg_fragmentation_in_percent >
10.0> index_id
0;
--Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR for SELECT * from WORK_TO_DO;
-Open the cursor.
OPEN partitions;
--Loop through the partitions.
FETCH NEXT
From partitions
Into @objectid, @indexid, @partitionnum, @frag;
While
@ @FETCH_STATUS=
0
BEGIN;
SELECT @objectname = o.name, @schemaname = S.name
From sys.objects as O
JOIN Sys.schemas as s on s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = Name
From sys.indexes
WHERE object_id = @objectid and index_id = @indexid;
SELECT @partitioncount = count (*)
From sys.partitions
WHERE object_id = @objectid and index_id = @indexid;
--arbitrary decision point at which to switch between reorganizing and rebuilding
IF @frag <
30.0
BEGIN;
SELECT @command = ' ALTER INDEX ' + @indexname + ' on ' + @schemaname + '. ' + @objectname + ' REORGANIZE ';
IF @partitioncount >
1
SELECT @command = @command + ' partition= ' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
IF @frag >=
30.0
BEGIN;
SELECT @command = ' ALTER INDEX ' + @indexname + ' on ' + @schemaname + '. ' + @objectname + ' REBUILD ';
IF @partitioncount >
1
SELECT @command = @command + ' partition= ' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
PRINT ' Executed ' + @command;
FETCH NEXT from partitions into @objectid, @indexid, @partitionnum, @frag;
END;
--Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
--Drop the temporary table
IF EXISTS (SELECT name from sys.objects WHERE name = ' Work_to_do ')
DROP TABLE Work_to_do;
GO
Recommended for Bol:
| avg_fragmentation_in_percent value |
Fix Statement |
> 5% and < = 30% |
ALTER INDEX REORGANIZE |
> 30% |
ALTER INDEX REBUILD with (ONLINE = ON) * |
Less than 5 is not necessary to rebuild, so the above SQL statement is still a place to discuss.
Http://blog.csdn.net/greenery/archive/2008/08/06/2778486.aspx
/**************************************
Role: Organize index fragmentation online
Date: 2008-5-15
Description
Adapted to Microsoft's example
For the inability to stop rebuilding the index,
Defragment index fragments online and update table statistics to enhance results.
You can also customize the degree of logical fragmentation of indexes that need to be collated, scan density, number of pages
Must be performed when database access is low
***************************************/
CREATE PROC [dbo]. [Sys_indexdrag]
As
BEGIN
SET NOCOUNT on
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag decimal--Logical Fragment
DECLARE @MaxScanDensity decimal--Scanning density
DECLARE @Page INT--8k pages, set a limit to organize a certain large index
DECLARE @TmpName VARCHAR (150)
--Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0
SELECT @MaxScanDensity =70.0
SELECT @Page =400
--Declare cursor
DECLARE Tables CURSOR for
SELECT 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 cursor for list of indexes to be defragged
DECLARE Indexes CURSOR for
SELECT ObjectName, ObjectId, IndexID, Logicalfrag
From #fraglist
WHERE (Logicalfrag >= @maxfrag OR [scandensity]<[email protected]) and [Countpages]>[email protected]
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
--Online defragmentation
PRINT ' executing DBCC indexdefrag (0, ' + RTRIM (@tablename) + ',
' + RTRIM (@indexid) + ')-Fragmentation currently '
+ RTRIM (CONVERT (varchar), @frag) + '% '
SELECT @execstr = ' DBCC indexdefrag (0, ' + RTRIM (@objectid) + ',
' + RTRIM (@indexid) + ') '
EXEC (@execstr)
--Update statistic information
IF @TmpName <> @tablename
BEGIN
SET @[email protected]
EXEC (' UPDATE STATISTICS ' [email protected])
END
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
GO
|
Go SQL Server Defragment index fragmentation test
