Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL index creation taking an abnormally long time

Posted on 2016-11-15
2
Medium Priority
?
328 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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 to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

618 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