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 curTablesHi
"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173530238.674192.247030@.64g2000cwx.googlegro ups.com...
Quote:
Originally Posted by
>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
Quote:
Originally Posted by
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
Quote:
Originally Posted by
>
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.
Quote:
Originally Posted by
>
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.
Quote:
Originally Posted by
>
If you are using DBCC DBREINDEX or DBCC INDEXDEFRAG you should look to
change to ALTER INDEX...
John|||thanks John:
Result of one trouble file (tab delimited).
alloc_unit_type_descindex_depthindex_level
avg_fragmentation_in_percentfragment_countavg_fragment_size_in_pages
page_count
IN_ROW_DATA1033.33333333333331211.1666666666667134
IN_ROW_DATA2088.2352941176471161.062517
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|||Hi Rob
"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173578291.621286.217970@.p10g2000cwp.googlegr oups.com...
Quote:
Originally Posted by
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|||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_idobject_idindex_idpartition_numberindex_type_desc
alloc_unit_type_descindex_depthindex_level
avg_fragmentation_in_percentfragment_countavg_fragment_size_in_pages
page_countavg_page_space_used_in_percentrecord_count
ghost_record_countversion_ghost_record_countmin_record_size_in_bytes
max_record_size_in_bytesavg_record_size_in_bytes
forwarded_record_count
8110262697101HEAPIN_ROW_DATA1021.73913043478261012.4124
NULLNULLNULLNULLNULLNULLNULLNULL
8110262697131NONCLUSTERED INDEXIN_ROW_DATA2030.7692307692308
102.626NULLNULLNULLNULLNULLNULLNULLNULL
8110262697141NONCLUSTERED INDEXIN_ROW_DATA2033.33333333333339
218NULLNULLNULLNULLNULLNULLNULLNULL
On Mar 11, 6:36 am, "John Bell" <jbellnewspo...@.hotmail.comwrote:
Quote:
Originally Posted by
Hi Rob
>
"rcamarda" <robert.a.cama...@.gmail.comwrote in message
>
news:1173578291.621286.217970@.p10g2000cwp.googlegr oups.com...
>
Quote:
Originally Posted by
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
>
Quote:
Originally Posted by
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|||Hi
"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173613533.607839.275800@.t69g2000cwt.googlegr oups.com...
Quote:
Originally Posted by
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|||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.comwrote:
Quote:
Originally Posted by
Hi
>
"rcamarda" <robert.a.cama...@.gmail.comwrote in message
>
news:1173613533.607839.275800@.t69g2000cwt.googlegr oups.com...
>
>
>
Quote:
Originally Posted by
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].)
>
Quote:
Originally Posted by
File is INTSTD. this is the DLL that is maintained in my data
warehouse tool from cognos.
// Connection: 2-Source
>
Quote:
Originally Posted by
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
);
>
Quote:
Originally Posted by
CREATE INDEX STUDENT_ID ON "dbo"."INTSTD" ( "STUDENT_ID" );
CREATE INDEX LOAD_DT ON "dbo"."INTSTD" ( "LOAD_DT" );
>
Quote:
Originally Posted by
The SQL snipit I copied from BOL
>
Quote:
Originally Posted by
DECLARE @.db_id SMALLINT;
DECLARE @.object_id INT;
>
Quote:
Originally Posted by
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');
>
Quote:
Originally Posted by
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 -|||Hi Rob
On Mar 11, 4:47 pm, "rcamarda" <robert.a.cama...@.gmail.comwrote:
Quote:
Originally Posted by
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.comwrote:
>
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|||On Mar 12, 8:23 am, "John Bell" <jbellnewspo...@.hotmail.comwrote:
Quote:
Originally Posted by
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/t...ex/default.aspx
for more.
John
No comments:
Post a Comment