Solved

SQL index creation taking an abnormally long time

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

840 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