Posted by rcamarda on 03/11/07 16:47

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.com> wrote:
> Hi
>
> "rcamarda" <robert.a.cama...@gmail.com> wrote in message
>
> news:1173613533.607839.275800@t69g2000cwt.googlegroups.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 -

[Back to original message]