MS SQL Slow on Indexing - 2 hours on 50MM records
Posted on 2009-07-08
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:
CREATE INDEX IDX_Profile_Sales
on Profile (Sales)
It has been running for TWO HOURS. I've recently also seen other queries, such as:
set Sales = (Select Sales from table2 where table2.email = Profile.email)
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 ...