?
Solved

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

Posted on 2012-09-11
8
Medium Priority
?
374 Views
Last Modified: 2012-09-11
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
Comment
Question by:EddieIT
  • 4
  • 3
8 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38387419
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
 

Author Comment

by:EddieIT
ID: 38387434
Thank you, but how can I run it on a specific table / database?
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38387529
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:EddieIT
ID: 38387659
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38387719
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
 

Author Comment

by:EddieIT
ID: 38387761
Got it, Thanks, whatis teh difference between index_Type_Desc HEAP and NONCLUSTERED INDEX
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 38387990
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38388268
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question