Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shiloh
Starting Member
48 Posts |
 Posted - 2007-12-02 : 15:11:42
|
| Hi allRecently we are having issues with one of the indexes during our maintenance. There is one index that which usually would have taken 10-15 mins continues to run even after 3 hrs. Other indexes on the same table finish nicely. Just this one gets stuck. Our index creation scripts drops existing index and creates new one..CREATE INDEX ... ON ... WITH DROP_EXISTING, SORT_IN_TEMPDB ...etcSo last week when I got woken up because of this, I tried to drop the index explicitly and re create the index (by removing the DROP_EXISTING in above script). Even though it took about 45 mins it still finished in time during the maintenance window.We had the same issue this week. I was looking at sp_who2 during the index creation, I noticed very low CPU activity and high DiskIO.example: CPU : 4k cycles, DiskIO 50000.There is 20% of free space on the index file group. Generally index creation takes higher CPU and lower DiskIO. How do I find out what the issue is with this index? The table has 40 million rows. this is a non-clustered index. There are 2 other indexes on the table - one clustered. they both finished properly in less than 10 mins.Thanks in advance,Don |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-02 : 16:14:19
|
| How many columns in that index? Did you see file growth? |
 |
|
|
|
|
|
Create Index