473,704 Members | 2,660 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

index fragmentation LogicalFragment ation -how bad is bad - silly question alter

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 LogicalFragmena tion 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%
logicalfragment ation, 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('Temp db.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 ,
[MinimumRecordSi ze] INT,
[MaximumRecordSi ze] INT , [AverageRecordSi ze] INT,
[ForwardedRecord s] INT ,
[Extents] INT, [ExtentSwitches] INT, [AverageFreeByte s]
NUMERIC(6,2)
,
[AveragePageDens ity] NUMERIC(6,2), [ScanDensity]
NUMERIC(6,2) ,
[BestCount] INT ,
[ActualCount] INT , [LogicalFragment ation] NUMERIC(6,2) ,
[ExtentFragmenta tion] NUMERIC(6,2) )
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Sch ema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @TableName
SET @TableName = RTRIM(@TableNam e)
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

Mar 10 '07 #1
8 5204
Hi

"rcamarda" <ro************ **@gmail.comwro te in message
news:11******** **************@ 64g2000cwx.goog legroups.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 LogicalFragmena tion is over
30%.
In the "Reorganizi ng 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.ht m

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

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.ht m
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%
logicalfragment ation, 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.ht m
>
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

Mar 10 '07 #2
thanks John:
Result of one trouble file (tab delimited).
alloc_unit_type _desc index_depth index_level
avg_fragmentati on_in_percent fragment_count avg_fragment_si ze_in_pages
page_count
IN_ROW_DATA 1 0 33.333333333333 3 12 11.166666666666 7 134
IN_ROW_DATA 2 0 88.235294117647 1 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

Mar 11 '07 #3
Hi Rob

"rcamarda" <ro************ **@gmail.comwro te in message
news:11******** **************@ p10g2000cwp.goo glegroups.com.. .
thanks John:
Result of one trouble file (tab delimited).
alloc_unit_type _desc index_depth index_level
avg_fragmentati on_in_percent fragment_count avg_fragment_si ze_in_pages
page_count
IN_ROW_DATA 1 0 33.333333333333 3 12 11.166666666666 7 134
IN_ROW_DATA 2 0 88.235294117647 1 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
Mar 11 '07 #4
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"."INTS TD"
(
"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_APPR OVED_DT" DATETIME NULL,
"VISA_APPROVED_ DT" DATETIME NULL,
"PT_START_D T" DATETIME NULL,
"PT_END_DT" DATETIME NULL,
"FT_START_D T" DATETIME NULL,
"FT_END_DT" DATETIME NULL,
"LOAD_DT" DATETIME NULL
);

CREATE INDEX STUDENT_ID ON "dbo"."INTS TD" ( "STUDENT_ID " );
CREATE INDEX LOAD_DT ON "dbo"."INTS TD" ( "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_s ource');
SET @object_id = OBJECT_ID(N'ds_ v6_source.dbo.i ntstd');
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_numbe r index_type_desc
alloc_unit_type _desc index_depth index_level
avg_fragmentati on_in_percent fragment_count avg_fragment_si ze_in_pages
page_count avg_page_space_ used_in_percent record_count
ghost_record_co unt version_ghost_r ecord_count min_record_size _in_bytes
max_record_size _in_bytes avg_record_size _in_bytes
forwarded_recor d_count
8 1102626971 0 1 HEAP IN_ROW_DATA 1 0 21.739130434782 6 10 12.4 124
NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 30.769230769230 8
10 2.6 26 NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 33.333333333333 3 9
2 18 NULL NULL NULL NULL NULL NULL NULL NULL
On Mar 11, 6:36 am, "John Bell" <jbellnewspo... @hotmail.comwro te:
Hi Rob

"rcamarda" <robert.a.cama. ..@gmail.comwro te in message

news:11******** **************@ p10g2000cwp.goo glegroups.com.. .
thanks John:
Result of one trouble file (tab delimited).
alloc_unit_type _desc index_depth index_level
avg_fragmentati on_in_percent fragment_count avg_fragment_si ze_in_pages
page_count
IN_ROW_DATA 1 0 33.333333333333 3 12 11.166666666666 7 134
IN_ROW_DATA 2 0 88.235294117647 1 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

Mar 11 '07 #5
Hi

"rcamarda" <ro************ **@gmail.comwro te in message
news:11******** **************@ t69g2000cwt.goo glegroups.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"."INTS TD"
(
"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_APPR OVED_DT" DATETIME NULL,
"VISA_APPROVED_ DT" DATETIME NULL,
"PT_START_D T" DATETIME NULL,
"PT_END_DT" DATETIME NULL,
"FT_START_D T" DATETIME NULL,
"FT_END_DT" DATETIME NULL,
"LOAD_DT" DATETIME NULL
);

CREATE INDEX STUDENT_ID ON "dbo"."INTS TD" ( "STUDENT_ID " );
CREATE INDEX LOAD_DT ON "dbo"."INTS TD" ( "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_s ource');
SET @object_id = OBJECT_ID(N'ds_ v6_source.dbo.i ntstd');
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_numbe r index_type_desc
alloc_unit_type _desc index_depth index_level
avg_fragmentati on_in_percent fragment_count avg_fragment_si ze_in_pages
page_count avg_page_space_ used_in_percent record_count
ghost_record_co unt version_ghost_r ecord_count min_record_size _in_bytes
max_record_size _in_bytes avg_record_size _in_bytes
forwarded_recor d_count
8 1102626971 0 1 HEAP IN_ROW_DATA 1 0 21.739130434782 6 10 12.4 124
NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 30.769230769230 8
10 2.6 26 NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 33.333333333333 3 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
Mar 11 '07 #6
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" <jbellnewspo... @hotmail.comwro te:
Hi

"rcamarda" <robert.a.cama. ..@gmail.comwro te in message

news:11******** **************@ t69g2000cwt.goo glegroups.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"."INTS TD"
(
"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_APPR OVED_DT" DATETIME NULL,
"VISA_APPROVED_ DT" DATETIME NULL,
"PT_START_D T" DATETIME NULL,
"PT_END_DT" DATETIME NULL,
"FT_START_D T" DATETIME NULL,
"FT_END_DT" DATETIME NULL,
"LOAD_DT" DATETIME NULL
);
CREATE INDEX STUDENT_ID ON "dbo"."INTS TD" ( "STUDENT_ID " );
CREATE INDEX LOAD_DT ON "dbo"."INTS TD" ( "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_s ource');
SET @object_id = OBJECT_ID(N'ds_ v6_source.dbo.i ntstd');
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_numbe r index_type_desc
alloc_unit_type _desc index_depth index_level
avg_fragmentati on_in_percent fragment_count avg_fragment_si ze_in_pages
page_count avg_page_space_ used_in_percent record_count
ghost_record_co unt version_ghost_r ecord_count min_record_size _in_bytes
max_record_size _in_bytes avg_record_size _in_bytes
forwarded_recor d_count
8 1102626971 0 1 HEAP IN_ROW_DATA 1 0 21.739130434782 6 10 12.4 124
NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 30.769230769230 8
10 2.6 26 NULL NULL NULL NULL NULL NULL NULL NULL
8 1102626971 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 33.333333333333 3 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 -

Mar 11 '07 #7
Hi Rob

On Mar 11, 4:47 pm, "rcamarda" <robert.a.cama. ..@gmail.comwro te:
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" <jbellnewspo... @hotmail.comwro te:
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

Mar 12 '07 #8
On Mar 12, 8:23 am, "John Bell" <jbellnewspo... @hotmail.comwro te:
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

Mar 12 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
6672
by: Tron Thomas | last post by:
Given the following information about memory management in C++: ----- The c-runtime dynamic memory manager (and most other commercial memory managers) has issues with fragmentation similar to a hard drive file system. Over time, the more often use call new/delete or alloc/free, there will be gaps and fragments in the heap. This can lead to inefficient use of available memory, as well as cache-hit inefficiencies.
14
1802
by: Ioannis Vranos | last post by:
I would like to see your views on these. C++98 is already a large language since it supports 4 paradigms and each one is supported well, with optimal space and time efficiency. And this is excellent. From the few things that i have read about C++0x, in addition to some C99... features (actually some other term comes in my mind for this instinctively, but it is another subject for discussion), there is library expansion with
25
4917
by: sql_server_2000_user | last post by:
Hi, I have a table with about 305 million rows, and a composite primary key that consists of an ascending int and an ascending varchar(18), which is typically of length 13. Even if all the keys used the full 18 characters of the varchar, it seems to me each key should be 22 bytes, so the index should be roughly 6.4GB. However, the size of the index as shown in EM is about 24GB, and this is slowing everything down considerably. Does...
10
8879
by: wackyphill | last post by:
After rebuilding an index, it still shows as the same amount of fragmentation. ANy ideas what's wrong? I'm determining which indexes to rebuild using the following query: SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, ips.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,
1
2334
by: Raziq Shekha | last post by:
Hi Folks, SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran the command : dbcc dbreindex ('tablename') go for all tables in the database. Then I compared the dbcc showcontig with all_index output from before and after the reindex and on the
1
3612
by: Chris Mullins | last post by:
We've been using the SSLStream class found in System.Net.Security to build a giant Sockets server that provides TLS encryption at the channel leve. Before .Net 2.0, we used an open-source encryption channel from Mentalis, and have even looked at the Mono implementation for doing this. The problem comes from the SSLStream not doing any buffer management. None. Zero. In the "no buffer management" case, each SSLStream allocates bufferes...
1
1390
by: Shameer | last post by:
I monitor the index fragmentation through dbcc showcontig command , i get result as Pages Scanned Extents Scanned Extent Switches Avg. Pages per Extent Scan Density Logical Scan Fragmentation Extent Scan Fragmentation Avg. Bytes Free per Page Avg. Page Density (full).
0
968
by: Shameer | last post by:
Hi, I monitor the index fragmentation through dbcc showcontig command , i get result as Pages Scanned Extents Scanned Extent Switches Avg. Pages per Extent Scan Density Logical Scan Fragmentation Extent Scan Fragmentation
1
2721
by: jane | last post by:
HI, I had one delete statement, it was running for some time, caused fragmentation on indexes. The delete performance is getting worse. After reorg the indexes, the performance is getting better. My question is I got explain before the reorg/runstats and after reorg/ runstats. The estimated costs are exactly the same.. I suppose the explain costs should be lower after reorg/runstats.
1
1549
by: digitalox | last post by:
All efforts to rebuild indexes fails. Not with an error, but they still show high fragmentation. The environment: SQL Server 2005 in 2000 compatibility mode non-clustered indexes on tables that have clustered indexes with unique constraint Tried so far: Rebuild, reorganize, manual drop and recreate, setting the db in 2005 mode This only seems to happen on the tables that also have the clustered index with a unique contraint ( which...
0
8766
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8684
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
9025
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8975
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5930
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4438
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4699
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3134
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2478
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.