Posted by rcamarda on 03/11/07 11:45

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" <jbellnewspo...@hotmail.com> wrote:
> Hi Rob
>
> "rcamarda" <robert.a.cama...@gmail.com> wrote in message
>
> news:1173578291.621286.217970@p10g2000cwp.googlegroups.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

[Back to original message]