Solved

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

Posted on 2009-07-08
11
494 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

773 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