January 12, 2011 at 11:01 am
I'm trying to create an index but it is taking way longer than expected. When I look in the Activity Monitor, there isn't much CPU or database IO.
Normally when I create an index, I see CPU at about 85% and IO at 20MB/sec. But this one is running only about 20% and 6MB/sec. I've created a lot of indexes in this table before, so this one is a surprise.
The actual SQL code came from the XML view of the plan as reported in the sp "WhoIsActive" (Adam Mechanic - http://sqlblog.com/blogs/adam_machanic/archive/2009/12/03/who-is-active-v9-57-fast-comprehensive-dmv-collection.aspx) and this has worked well in the past.
The biggest difference here is this index uses more included columns (like 15) and three columns, but not all that many more than others.
What surprises me it the lack of CPU and IO activity, it's like there is a resource governor (which there isn't) or something that is holding things back.
Any thoughts?
Norman
January 12, 2011 at 11:27 am
Is it being blocked by other processes in the table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 1:06 pm
Sorry, should have included that little bit - no, nothing is reporting being blocked that I can see. Generally nothing else running at the moment.
Generally these are created in 20 minutes, this has been running for over 4 hours...
The table is large - 188 columns and about 84 million rows. Not mine, vendor provided and not much chance of getting that changed.
I am beginning to think that I'm running out of memory with this.
Norman
January 12, 2011 at 1:35 pm
On a table that size, and an index with the number of columns you indicate, I'd generally expect it to take a LONG time to complete, especially if the table is in use whilst the index is being built.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2011 at 2:40 pm
Yeah, I've got several of these sized tables that I have to deal with. But the surprising thing was the lack of CPU and IO activity. Just seems like something isn't quite right. I didn't expect to be able to create an index quickly but this is just so much slower than others.
Maybe I can do this over the weekend when usage is lower...
Thanks for the time and comments.
Norman
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply