index fragmentation LogicalFragmentation -how bad is bad - silly question alter

[rcamarda]

I thought I would delve into index fragmentation and I found some
great sql from many posters (thanks Erland!).
My question is how bad is bad? I know this is very subjective.
Some scripts I found would reindex if the LogicalFragmenation is over
30%.
I have some tables that are 98% (I'm guessing really bad). I know it
all depends..

more as a learning point: I found a table that had over 30%
logicalfragmentation, I dropped the indexes, created then ran the
script that used type code segment:
'DBCC SHOWCONTIG(' + @TableName + ') WITH TABLERESULTS, ALL_INDEXES,
NO_INFOMSGS')

In one case, the indexes for the table dropped below 30%, in another
case the index was still fragmented ever after I dropped and re-
created index.

SQL Server 2005 x64 SP2

This is the script I am running (I found this in another thread that
Erland posted):

SET NOCOUNT ON
USE ds_v6_source
DECLARE @TableName VARCHAR(100)
-- Create a table to hold the results of DBCC SHOWCONTIG
IF OBJECT_ID('Tempdb.dbo.#Contig') IS NOT NULL
DROP TABLE #Contig
CREATE TABLE #Contig ([ObjectName] VARCHAR(100), [ObjectId] INT,
[IndexName]
VARCHAR(200),
[IndexId] INT, [Level] INT, [Pages] INT , [Rows] INT ,
[MinimumRecordSize] INT,
[MaximumRecordSize] INT , [AverageRecordSize] INT,
[ForwardedRecords] INT ,
[Extents] INT, [ExtentSwitches] INT, [AverageFreeBytes]
NUMERIC(6,2)
,
[AveragePageDensity] NUMERIC(6,2), [ScanDensity]
NUMERIC(6,2) ,
[BestCount] INT ,
[ActualCount] INT , [LogicalFragmentation] NUMERIC(6,2) ,
[ExtentFragmentation] NUMERIC(6,2) )
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @TableName
SET @TableName = RTRIM(@TableName)
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Contig EXEC('DBCC SHOWCONTIG(' + @TableName + ') WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT FROM curTables INTO @TableName
END
CLOSE curTables
DEALLOCATE curTables

[John Bell]

Hi

"rcamarda" <ro**************@gmail.comwrote in message
news:11**********************@64g2000cwx.googlegro ups.com...

>I thought I would delve into index fragmentation and I found some
great sql from many posters (thanks Erland!).
My question is how bad is bad? I know this is very subjective.
Some scripts I found would reindex if the LogicalFragmenation is over
30%.

In the "Reorganizing and Rebuilding Indexes" topic, BOL recommends to
REORGANIZE for 5-30% and REBUILD 30%

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm

Although the script on the "sys.dm_db_index_physical_stats" topic in BOL use
10-30% to REORGANIZE

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm

I have some tables that are 98% (I'm guessing really bad). I know it
all depends..

more as a learning point: I found a table that had over 30%
logicalfragmentation, I dropped the indexes, created then ran the
script that used type code segment:
'DBCC SHOWCONTIG(' + @TableName + ') WITH TABLERESULTS, ALL_INDEXES,
NO_INFOMSGS')

DBCC SHOWCONTIG does not support some new features in SQL Server 2005 see
the "DBCC SHOWCONTIG" topic in BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/1df2123a-1197-4fff-91a3-25e3d8848aaa.htm

>
In one case, the indexes for the table dropped below 30%, in another
case the index was still fragmented ever after I dropped and re-
created index.

Posting the output from sys.dm_db_index_physical_stats would be useful.

>
SQL Server 2005 x64 SP2

Check you are on the version of SP2 downloaded after 2007-03-05 or the
critical update http://support.microsoft.com/kb/933508 if you have
Maintenance Plans or SSIS packages.

>

If you are using DBCC DBREINDEX or DBCC INDEXDEFRAG you should look to
change to ALTER INDEX...

John

[rcamarda]

thanks John:
Result of one trouble file (tab delimited).
alloc_unit_type_desc index_depth index_level
avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages
page_count
IN_ROW_DATA 1 0 33.3333333333333 12 11.1666666666667 134
IN_ROW_DATA 2 0 88.2352941176471 16 1.0625 17

Based on your points about BOL, I found a script the reindex, or reorg
the index, but I still have files upwards of 80% fragmentation.
This is an undiscovered country for me, thanks for the guidance!
Rob

[John Bell]

Hi Rob

"rcamarda" <ro**************@gmail.comwrote in message
news:11**********************@p10g2000cwp.googlegr oups.com...

thanks John:
Result of one trouble file (tab delimited).
alloc_unit_type_desc index_depth index_level
avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages
page_count
IN_ROW_DATA 1 0 33.3333333333333 12 11.1666666666667 134
IN_ROW_DATA 2 0 88.2352941176471 16 1.0625 17

Based on your points about BOL, I found a script the reindex, or reorg
the index, but I still have files upwards of 80% fragmentation.
This is an undiscovered country for me, thanks for the guidance!
Rob

There seems to be some columns missing! Can you also post the DDL for the
table and indexes.

John

[rcamarda]

The rest of the columns where null, so I didnt include..
(I am wondering if I should create the indexes in a file space other
than [PRIMARY].)

File is INTSTD. this is the DLL that is maintained in my data
warehouse tool from cognos.
// Connection: 2-Source

CREATE TABLE "dbo"."INTSTD"
(
"STUDENT_ID" CHAR(20) NOT NULL,
"REINSTATE_DT" DATETIME NULL,
"VISA_MAILED_DT" DATETIME NULL,
"INITIALI20_DT" DATETIME NULL,
"FORMI20_DT" DATETIME NULL,
"I94CARD_DT" DATETIME NULL,
"SEVIS" CHAR(12) NULL,
"SEVIS_ISSUE_DT" DATETIME NULL,
"REINSTATE_APPROVED_DT" DATETIME NULL,
"VISA_APPROVED_DT" DATETIME NULL,
"PT_START_DT" DATETIME NULL,
"PT_END_DT" DATETIME NULL,
"FT_START_DT" DATETIME NULL,
"FT_END_DT" DATETIME NULL,
"LOAD_DT" DATETIME NULL
);

CREATE INDEX STUDENT_ID ON "dbo"."INTSTD" ( "STUDENT_ID" );
CREATE INDEX LOAD_DT ON "dbo"."INTSTD" ( "LOAD_DT" );
The SQL snipit I copied from BOL

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'ds_v6_source');
SET @object_id = OBJECT_ID(N'ds_v6_source.dbo.intstd');
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL,
NULL , 'LIMITED');

tab delimited results for the table:
database_id object_id index_id partition_number index_type_desc
alloc_unit_type_desc index_depth index_level
avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages
page_count avg_page_space_used_in_percent record_count
ghost_record_count version_ghost_record_count min_record_size_in_bytes
max_record_size_in_bytes avg_record_size_in_bytes
forwarded_record_count
8 1102626971 0 1 HEAP IN_ROW_DATA 1 0 21.7391304347826 10 12.4 124
NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 30.7692307692308
10 2.6 26 NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 33.3333333333333 9
2 18 NULL NULL NULL NULL NULL NULL NULL NULL
On Mar 11, 6:36 am, "John Bell" <[e-mail]:

Hi Rob

"rcamarda" <[e-mail] in message

news:11**********************@p10g2000cwp.googlegr oups.com...

thanks John:
Result of one trouble file (tab delimited).
alloc_unit_type_desc index_depth index_level
avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages
page_count
IN_ROW_DATA 1 0 33.3333333333333 12 11.1666666666667 134
IN_ROW_DATA 2 0 88.2352941176471 16 1.0625 17

Based on your points about BOL, I found a script the reindex, or reorg
the index, but I still have files upwards of 80% fragmentation.
This is an undiscovered country for me, thanks for the guidance!
Rob

There seems to be some columns missing! Can you also post the DDL for the
table and indexes.

John

[John Bell]

Hi

"rcamarda" <ro**************@gmail.comwrote in message
news:11**********************@t69g2000cwt.googlegr oups.com...

The rest of the columns where null, so I didnt include..
(I am wondering if I should create the indexes in a file space other
than [PRIMARY].)

File is INTSTD. this is the DLL that is maintained in my data
warehouse tool from cognos.
// Connection: 2-Source

CREATE TABLE "dbo"."INTSTD"
(
"STUDENT_ID" CHAR(20) NOT NULL,
"REINSTATE_DT" DATETIME NULL,
"VISA_MAILED_DT" DATETIME NULL,
"INITIALI20_DT" DATETIME NULL,
"FORMI20_DT" DATETIME NULL,
"I94CARD_DT" DATETIME NULL,
"SEVIS" CHAR(12) NULL,
"SEVIS_ISSUE_DT" DATETIME NULL,
"REINSTATE_APPROVED_DT" DATETIME NULL,
"VISA_APPROVED_DT" DATETIME NULL,
"PT_START_DT" DATETIME NULL,
"PT_END_DT" DATETIME NULL,
"FT_START_DT" DATETIME NULL,
"FT_END_DT" DATETIME NULL,
"LOAD_DT" DATETIME NULL
);

CREATE INDEX STUDENT_ID ON "dbo"."INTSTD" ( "STUDENT_ID" );
CREATE INDEX LOAD_DT ON "dbo"."INTSTD" ( "LOAD_DT" );
The SQL snipit I copied from BOL

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'ds_v6_source');
SET @object_id = OBJECT_ID(N'ds_v6_source.dbo.intstd');
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL,
NULL , 'LIMITED');

tab delimited results for the table:
database_id object_id index_id partition_number index_type_desc
alloc_unit_type_desc index_depth index_level
avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages
page_count avg_page_space_used_in_percent record_count
ghost_record_count version_ghost_record_count min_record_size_in_bytes
max_record_size_in_bytes avg_record_size_in_bytes
forwarded_record_count
8 1102626971 0 1 HEAP IN_ROW_DATA 1 0 21.7391304347826 10 12.4 124
NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 30.7692307692308
10 2.6 26 NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 33.3333333333333 9
2 18 NULL NULL NULL NULL NULL NULL NULL NULL

This does not show the fragmentation you are talking about! You may want to
consider making student_id/load_dt a clustered index. If this data is bulk
loaded for unique load_dts then a clustered index on load_dt/student_id
would effectively append data with no fragmentation.

Do you have a sensible fill factor?

John

[rcamarda]

John,
I've been running the scripts you've pointed out in BOL, so they are
changing. the amount and size of fragmentation is coming down, but I
have some smaller files with a lot.
fill Factor is default, which I think is 80.

On Mar 11, 9:15 am, "John Bell" <[e-mail]:

Hi

"rcamarda" <[e-mail] in message

news:11**********************@t69g2000cwt.googlegr oups.com...

The rest of the columns where null, so I didnt include..
(I am wondering if I should create the indexes in a file space other
than [PRIMARY].)

File is INTSTD. this is the DLL that is maintained in my data
warehouse tool from cognos.
// Connection: 2-Source

CREATE TABLE "dbo"."INTSTD"
(
"STUDENT_ID" CHAR(20) NOT NULL,
"REINSTATE_DT" DATETIME NULL,
"VISA_MAILED_DT" DATETIME NULL,
"INITIALI20_DT" DATETIME NULL,
"FORMI20_DT" DATETIME NULL,
"I94CARD_DT" DATETIME NULL,
"SEVIS" CHAR(12) NULL,
"SEVIS_ISSUE_DT" DATETIME NULL,
"REINSTATE_APPROVED_DT" DATETIME NULL,
"VISA_APPROVED_DT" DATETIME NULL,
"PT_START_DT" DATETIME NULL,
"PT_END_DT" DATETIME NULL,
"FT_START_DT" DATETIME NULL,
"FT_END_DT" DATETIME NULL,
"LOAD_DT" DATETIME NULL
);

CREATE INDEX STUDENT_ID ON "dbo"."INTSTD" ( "STUDENT_ID" );
CREATE INDEX LOAD_DT ON "dbo"."INTSTD" ( "LOAD_DT" );

The SQL snipit I copied from BOL

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'ds_v6_source');
SET @object_id = OBJECT_ID(N'ds_v6_source.dbo.intstd');
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL,
NULL , 'LIMITED');

tab delimited results for the table:
database_id object_id index_id partition_number index_type_desc
alloc_unit_type_desc index_depth index_level
avg_fragmentation_in_percent fragment_count avg_fragment_size_in_pages
page_count avg_page_space_used_in_percent record_count
ghost_record_count version_ghost_record_count min_record_size_in_bytes
max_record_size_in_bytes avg_record_size_in_bytes
forwarded_record_count
8 1102626971 0 1 HEAP IN_ROW_DATA 1 0 21.7391304347826 10 12.4 124
NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 30.7692307692308
10 2.6 26 NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 33.3333333333333 9
2 18 NULL NULL NULL NULL NULL NULL NULL NULL

This does not show the fragmentation you are talking about! You may want to
consider making student_id/load_dt a clustered index. If this data is bulk
loaded for unique load_dts then a clustered index on load_dt/student_id
would effectively append data with no fragmentation.

Do you have a sensible fill factor?

John- Hide quoted text -

- Show quoted text -

[John Bell]

Hi Rob

On Mar 11, 4:47 pm, "rcamarda" <[e-mail]:

John,
I've been running the scripts you've pointed out in BOL, so they are
changing. the amount and size of fragmentation is coming down, but I
have some smaller files with a lot.
fill Factor is default, which I think is 80.

On Mar 11, 9:15 am, "John Bell" <[e-mail]:

If your tables are bulk loaded and never change a 100% fill factor
would be feasible. If the data pages are all in memory then the index
fragmentation is not going to be an issue, this may be the case with
small tables.
To reduce fragmentation of the heap tables you can try adding a
clustered index and then drop it, although you should look at possibly
having a permanent clustered indexes in some cases.

Check the indexes are being used by seeing if there is an entry in
sys.dm_db_index_usage_stats . If there is no entry the index has not
been used since the last reboot, so you would need to see if it will
ever be usfull.

John

[John Bell]

On Mar 12, 8:23 am, "John Bell" <[e-mail]:

Hi Rob

If there is no entry the index has not
been used since the last reboot, so you would need to see if it will
ever be usfull.

Just to correct myself! That is not quite right, look for entries with
values of 0
see https://www.microsoft.com/technet/te...x/default.aspx
for more.

John