July 14, 2008 at 10:16 am
I rebuild a database so that data resides on one drive array (Raid-10) and the indexes on another drive array (Raid-5).
I have a view that includes 5 tables. Anything that accesses this view will lock up. I do a sp_lock and all of the indexes on the view tables have a schema lock and the query will sit there for hours doing nothing. Everything around them is getting a timeout. But the original query just sits there.
If I move the indexes for these 5 tables back onto the same drive as the data, the queries behaves normally again.
The only change was the physical disk location (Raid-10 to a Raid-5). The database is read intensive (few writes).
Any ideas why this behavior?
Thanks
July 14, 2008 at 10:28 am
Saw something similar in the past; but it was related to MAXDOP.... I would google maxdop and try setting the max degree of paralellism to 1 if it's a multi-processor box. This may or may not help.
July 14, 2008 at 12:19 pm
You might need to recompile the view.
- 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
July 14, 2008 at 12:19 pm
The other thing to test is whether or not other queries that access those indexes, without going through that view, work.
- 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
July 15, 2008 at 9:22 am
I would verify the physical disks are of the same speed and also check the IO throughput on each disk. Maybe some physical differences there. Is the IO on the new Index array dedicated to SQL traffic?
July 15, 2008 at 9:27 am
You can use SQLIO to verify the disk speeds and check the throughput.
It's available on Microsoft's site.... it's pretty non-intrusive other than using CPU and DISK for a couple minutes.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply