Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

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:

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
drgdrg
Asked:
drgdrg
  • 6
  • 4
1 Solution
 
BrandonGalderisiCommented:
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
 
ivanovnCommented:
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
 
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 ...

Thanks
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
BrandonGalderisiCommented:
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
 
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 ...

Thanks
0
 
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..."  
0
 
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.
0
 
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
0
 
BrandonGalderisiCommented:
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
 
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.
0
 
BrandonGalderisiCommented:
Like I said earlier.  The best time to add indexes to large tables is during off peak time.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now