Hi Ananda

As for what will happen when you clear stats - the numbers in the DMV are cummulative over time, and clearing them would start values at zero again (wait...

Awesome info, Paul - thanks for that.

Time to write an article (or ten), maybe...? 🙂

Well, I managed to reproduce the error on our test system with MAXDOP = 1.

Looks like...

Sorry, I can't think of anything else to suggest without being able to see what you're seeing.

Hmmmm. Tab characters maybe? Does paste special/Text help at all?

you can right click on the results grid and save as .CSV, then open it up in Excel, see if that works.

Ola Andre

I don't speak Spanish/Portuguese but I think you are looking for PIVOT:

http://msdn.microsoft.com/en-us/library/ms177410%28v=SQL.100%29.aspx

Edit: Actually, maybe not 🙂

SQLkiwi (2/28/2011)

---

OK, that makes sense. The 23,725 attempts and 10 successes on the clustered index on live shows that escalation from row (or page) locks to a table lock...

Ah, gotcha...

Answers in bold inline. Thanks so much for your time with this

SQLkiwi (2/28/2011)

---

Are you certain...

Thanks Paul

The version is indeed 10.00.4000, ie SP2.

sys.dm_db_index_operational_stats show the following for the offending table/index:

leaf insert count 8283880

nonleaf insert count 26707

row lock count 12352306

page lock count 56674

index lock promotion attempt...

SQLkiwi (2/27/2011)

---

mazzz (2/27/2011)

---

Thanks Paul. I'm pretty sure both Test and Live servers are at SP2 as they're both fairly recent installs, but will double check tomorrow when I'm back in...

Thanks Paul. I'm pretty sure both Test and Live servers are at SP2 as they're both fairly recent installs, but will double check tomorrow when I'm back in the office.

UMG Developer (2/25/2011)

I think you are pretty safe in that the issue is with the database design/code and not a server issue, so I would push back on the 3rd...

UMG Developer (2/25/2011)

---

Yes, a config_value of 0 is the default.

SPID 22 is probably a system session, so it shouldn't be causing the problem. (SPIDs under 50 are almost always system...

Thanks for responding. I think it's still the default? config_value presumably?

name minimum maximum config_value run_value

locks 5000 ...