Posted by John Bell on 03/11/07 13:15

Hi

"rcamarda" <robert.a.camarda@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

[Back to original message]