Greetings. We had a whole bunch of blocking occuring in one of our new DB/ apps lately during our peak user usage time. During this time our CPU was running around 80%. Since I have Idera Diagnostic Mgr, I'm able to go back in time, and see that it was mostly
on Page, PageLatch_EX and Key locks. Obviously Row locks would be more ideal. As an FYI, there are no missing indexes for the main offending queries.
Its pretty rare that I need to deal with this stuff (probably 4 years ago), so I'm trying to get my head back into it. As such have some questions:
- Is there a definate way to tell if the raised CPU caused the excessive locks, or the excessive locks caused the raised CPU.
- If I use sp_indexoption to not allow Page or Key locks, would it then default to Row locks?
View Complete Post