Seems my biggest issues are wait locks of the following type:

LCK_M_U (waiting to acquire update lock)

LCK_M_S (waiting to acquire shared lock)

On a single table (parent).

PARENT

|

DATA

Parent is update with refresh overwrite...

Thank you for the reply. Will investigate SNAPSHOT ISOLATION. Spotted this article re how to enable: http://www.sqlservercentral.com/Forums/Topic711799-146-1.aspx. Will test first.

thanks for the reply guys, just the push in the correct direction i was looking for.

Ah i see , thanks for posting.

WayneS (10/14/2010)

---

scott_lotus (10/14/2010)

---

Mirroring the db doesnt help me ether as i cannot afford to mirror the a/p cluster ( i think this is how it has to be done ?...

Really interesting topic. We work with a 450Gb db and do many more writes than reads. Nodes that send data can queue several hours before being overwritten which is helpful...

thank you again for the reply , very helpful stuff.

Yip, sorry will do.

thanks very much for the reply, sorry for all the spawned Qs below:

1) so you are saying the first column in the index is the important one ? (that...

GilaMonster (10/6/2010)

---

scott_lotus (10/6/2010)

---

GilaMonster (10/6/2010)

---

My guess is that you hit the 'debug' button rather than the execute button in management studio. It won't say debugging if you just ran the query.

Stop...

GilaMonster (10/6/2010)

---

My guess is that you hit the 'debug' button rather than the execute button in management studio. It won't say debugging if you just ran the query.

Stop it, then...

Grant Fritchey (9/29/2010)

---

Yes, READ COMMITTED is the default. Well, I'm stumped then. Something that is just a SELECT shouldn't be grabbing exclusive locks, so the best thing I can...

I agree that NOLOCK on queries are not ideal but its saved show stopping performance issues on several occasions and i have not found anything else to date that can...

That's a good point. Im sure the query is SELECT only. Added (NOLOCK) to the larger tables used but not the table in question.

1) I thought SELECTS exclusively lock...

Disabled the problematic index and created a new one. Re-ran the query with no issues. Will continue to monitor.