?
Solved

SQL index creation taking an abnormally long time

Posted on 2016-11-15
2
Medium Priority
?
228 Views
Last Modified: 2016-12-28
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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 2000 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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.
Suggested Courses

762 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