Solved

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

Posted on 2009-07-08
11
490 Views
Last Modified: 2012-05-07
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:

Update Profile
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 ...

Thanks
0
Comment
Question by:drgdrg
  • 6
  • 4
11 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24804245
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.
0
 
LVL 10

Expert Comment

by:ivanovn
ID: 24804254
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.
0
 
LVL 1

Author Comment

by:drgdrg
ID: 24804422
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 ...

Thanks
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24805730
While your create index is running, run the following query from a different connection.


sp_lock

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

Also try this to check for blocking.

sp_who2

Look for the spid of the connection where your reindex is being initiated.
0
 
LVL 1

Author Comment

by:drgdrg
ID: 24807541
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 ...

Thanks
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 1

Author Comment

by:drgdrg
ID: 24807585
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..."  
0
 
LVL 1

Author Comment

by:drgdrg
ID: 24807727
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.
0
 
LVL 1

Author Comment

by:drgdrg
ID: 24807770
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
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24807846
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.
0
 
LVL 1

Accepted Solution

by:
drgdrg earned 0 total points
ID: 24808869
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24814808
Like I said earlier.  The best time to add indexes to large tables is during off peak time.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now