asked on

SQL table looses indexes after import of large data, re-indexing script needs to be executed

Hello Experts, I use SQL DTS package to import into multiple tables for product information, after large imports my database gets slow untill I do not run a re-indexing script. How can I check properly if the table needs to be re-indexed or not?

Scott Pletcher

You can use the SQL dmv: sys.dm_db_index_physical_stats.

USE <import_db>

SELECT *
FROM sys.dm_db_index_physical_stats ( DB_ID('<import_db>'), OBJECT_ID('dbo.tablename'), NULL, NULL, NULL )

If the avg_fragmentation_in_percent is >= 20% (others prefer other thresholds, pick the one that's best for your needs), then the table needs reorg'ed or rebuilt.

If less than that, index is OK as is.

EddieIT

ASKER

Thank you, but how can I run it on a specific table / database?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EddieIT

ASKER

I did that, but I got a statement error

SELECT *
FROM sys.dm_db_index_physical_stats ( DB_ID('<V2K_SuitSupply_IndexTest>'), OBJECT_ID('dbo.tblProduct'), NULL, NULL, NULL )

Scott Pletcher

Assuming "V2K_SuitSupply_IndexTest" is the DB name:

SELECT *
FROM sys.dm_db_index_physical_stats ( DB_ID('V2K_SuitSupply_IndexTest'), OBJECT_ID('dbo.tblProduct'), NULL, NULL, NULL )

EddieIT

ASKER

Got it, Thanks, whatis teh difference between index_Type_Desc HEAP and NONCLUSTERED INDEX

DcpKing

Heap implies no imposed structure to the records, so no indices at all. A non-clustered Index is one that refers out to all the records, but does not re-order them in any way. A clustered Index imposes a physical order on the records

Scott Pletcher

There are two types of tables:

a HEAP, with no clustered index
a clustered index, which controls the physical order of the rows in the table [a clustered index IS the table itself].

Additionally, you can add one or more nonclustered indexes to a table to speed up lookups.

A nonclustered can always be safely dropped and recreated, or rebuilt.