MS SQL Slow on Indexing - 2 hours on 50MM records

I have a SQL 2005 database on a beefy Dell Poweredge 2950, 8GB ram, 4 TB of hard disk.

I have a database that is 25GB in size with about 50 million records.  

I've tried adding an index on a newly added field - a money type - located at the end of the record layout (about 40-50 columns).  I've tried creating the index using:

on Profile (Sales)

It has been running for TWO HOURS.  I've recently also seen other queries, such as:

Update Profile
set Sales = (Select Sales from table2 where =

take several hours and not complete.  IN the above update, the email column in both tables were indexed and there was no index on the sales column

There are literally 4 TB free in that volume on the raid array.  I am seeing memory pegged at 6.5GB usage by SQL (we're running 64 bit) and 7.55GB PF usage ...

What should I be looking for to address this?  Specifically, what commands or menu options?  Sorry, I'm not Mr. T-SQL so I need a little spoon feeding ...

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It takes time to build an index.  The ideal situation would be that you would build your index during off peak time.  The increased load on the server can effect other queries attempting to execute.
Have you looked at the execution plan for your update query? This may give you some insight in what index is being used and not being used.

As far as the index creation, with 50 million rows you can expect the procedure to take a bit of time, but it shouldn't take over 2 hours unless there is some other connection holding a lock or something similar. I don't use SQL Server so I am not sure what tools you have available, but you might want to look at which sessions are active at the time and if anything is holding an exclusive lock on your tables.
drgdrgAuthor Commented:
I've added other indexes last week (such as email address and city) and that took about 3 minutes each.  

I do wonder if something else is effecting it.  I've tried rebooting ... will try again and try to see if anything is running.   Unfortunately, I'm not the SQL Server Guru, yet that's my job ...

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

While your create index is running, run the following query from a different connection.


It will give you the object and database ids of the objects holding locks.  

Also try this to check for blocking.


Look for the spid of the connection where your reindex is being initiated.
drgdrgAuthor Commented:
I ran the sp_lock and sp_who2

Honestly, the results don't mean much to me, but two stand out on sp_who2:

spid: 5
status: background
dbname: master
command: unknown token
cputime: 15734
DiskIO: 1,263,727

spid: 10
status: suspended
dbname: (the slow database)
command: checkpoint
cputime: 8796
disk IO: 29

I don't know if this tells you anything useful ...

drgdrgAuthor Commented:
I've tried going into other databases as well and even a small test database takes 10 seconds to expand the "Tables" tree and opening a table with 300 rows takes 8-10 seconds...

I'm wondering if it isn't the database, but something with SQL or the OS ... I hate to say this, but "nothing has changed..."  
drgdrgAuthor Commented:
I'm also now noticing that SQL Server Agent is showing as:

SQL Server Agent (Agent XPs disabled) with a red arrow pointing down.  I've tried restarting it, running some TSQL I've located via Google that is supposed to help, but no joy so far.
drgdrgAuthor Commented:
I've gone through the logs ... it seems that the database is in a recovery mode, even though it does not indicate this on the database itself.  I'll wait util its finished (about 1 1/4 hours to go) and if its fixed, then that's it.  Otherwise, I'll follow up with more.  Thanks
A "recovery mode" pertains to how the transaction log is managed.  Not to be confused with a database that is recovering which is related but not the same.  A database is recovered when items from the transaction log must be applied to a database after SQL Server is restarted.  This happens always and the speed is dependent upon how much information needs to be applied.
drgdrgAuthor Commented:
OK ... thanks.  Once that recovery completed, the system did speed up radically.  We're going to test tomorrow and see if we can identify the problems that arose initially.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Like I said earlier.  The best time to add indexes to large tables is during off peak time.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.