Solved

SQL index creation taking an abnormally long time

Posted on 2016-11-15
2
143 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 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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

691 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