Solved

SQL index creation taking an abnormally long time

Posted on 2016-11-15
2
20 Views
Last Modified: 2016-11-15
I'm trying to create a nonclustered index on a SQL table, and it's taking what I would consider an extremely long time.

The table has approximately 120K rows, and already has a single index on a unique key column.

I'm trying to CREATE NONCLUSTERED INDEX _index_klltilrm_h_UPC ON KLLTILRM_H (UPC_CODE)

The UPC_CODE column is a char(12) data type.

SQL Server 2012, running on a Server 2012 environment with 128GB ram - the data lives on a brand new EqualLogic 16-drive SAN that is (for the moment) dedicated to the DB server.

Anyone have any ideas how to solve this one?
0
Comment
Question by:Ryan Shenk
2 Comments
 
LVL 14

Accepted Solution

by:
Vikas Garg earned 500 total points
ID: 41887868
Hello,

Each time when you create/recreate index, server starts to order pages, and this is quite resource-demanding procedure.
Table is a large one.
So you can divide your table in several smaller tables ,if it is possible.
Or you can create an empty copy of this table, add cluster index on empty table, import all data from your main table and after that delete the main table.

For Example

CREATE TABLE dbo.Test_New
(
       ...
)

CREATE Index

INSERT INTO dbo.Test_New(...)
SELECT ...
FROM dbo.Test


DROP TABLE Test

sp_rename 'Test_New', 'Test'
0
 

Author Closing Comment

by:Ryan Shenk
ID: 41887869
Thanks, worked perfectly.  Appreciate the help!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now