Go SQL Server Defragment index fragmentation test

Source: Internet
Author: User
Tags rtrim

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.