• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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?
0
EddieIT
Asked:
EddieIT
  • 4
  • 3
1 Solution
 
Scott PletcherSenior DBACommented:
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.
0
 
EddieITAuthor Commented:
Thank you, but how can I run it on a specific table / database?
0
 
Scott PletcherSenior DBACommented:
The code is already designed for that.

Just replace "<import_db>" with your db name, and "dbo.tablename" with your table name.

Repeat the entire SELECT statement for each table you need to process.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
EddieITAuthor Commented:
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 )
0
 
Scott PletcherSenior DBACommented:
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 )
0
 
EddieITAuthor Commented:
Got it, Thanks, whatis teh difference between index_Type_Desc HEAP and NONCLUSTERED INDEX
0
 
DcpKingCommented:
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
0
 
Scott PletcherSenior DBACommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now