Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2009-07-08
11
496 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
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.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 43
ms sql + get number in list out of total 7 36
T-SQL: New to using transactions 9 46
Exchange database won't mount 4 24
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…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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…

829 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