Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Recently I have been struggling with SQL server performance and although we have fixed a huge multitude of basic errors in the config it is still performing badly. To clarify it is not overall performance but rather fairly frequent time outs (from the client application). Previously I had looked to the memory as a cause but this has now been resolved and we are still getting the same behaviour.

Looking at the graphs from Management Data Warehouse I can see that LCK_M_U/X/IX are causing the majority of our waits around the time a user experiences a timeout. Everything I am reading states I need to look at the queries and processes running but I have yet to find anything aimed at a level I can understand. The locks, as you can see in the picture below, seem to spike which coincides with the error on the users side. Is there a clever DMV or some such that I can address to try and work out what query is being run that is creating the lock? Is it a case of trawling through a trace to find the details? Any guidance greatly appreciated and apologies if the information is not clear.

enter image description here

share|improve this question
1  
What kind of databases are on this server? Is it a single database, or many? What are the access patterns (OLTP/DW)? My initial reaction is that some part of the app is running a big read or write (missing index?), and other users have to wait to make changes to the data (aka blocking). This post on my blog may be of interest for background: voluntarydba.com/post/2013/01/22/… –  Jon Seigel Mar 19 '13 at 17:35
add comment

3 Answers

up vote 3 down vote accepted

Collecting Data from sp_WhoIsActive in a Table is a good technique for tracking down blocking and locking issues.

The @get_locks parameter can be used if you want to see the finer detail of the locks involved. Alternatively, @get_task_info and @get_additional_info will typically capture enough to identify the cause.

If the output gathered isn't clear enough to understand the problem, feel free to append to your question.

share|improve this answer
add comment

Locks tend to form chains and you are always interested mostly in what the process at the head of the chain is doing. Simply looking at lock wait times can be misleading because many processes can wait long times (thus increasing the wait times stats) but all be blocked by a single dog-slow process. What you likely see is a coalesce point: because locks granted have to be compatible with all current grantees and all pending waiters whenever a high level lock (X, or SCH_M) gets into the wait queue for a resource all subsequent requests queue up behind this.

To give an example: say you have a query running a report on a table, say it takes 5 mins. It hold an IS lock ont he table. This IS is compatible will all normal operations (reads and writes). A request comes in that wants SCH_M. Is incompatible with the IS so he's put in the queue. Now all of the sudden all other activity on the table goes into the queue, because every requests, reads or wites, are incompatible with this waiter. So all of the sudden all lock times spike. After 5 min the slow query finishes (it 'drains' in the DB jargon), the SCH_M is granted, it does its job in 5 ms and everyone else is free to proceed. This is just an (extreme) example, it doesn't have to be SCH_M. The idea is that just wait times don't tell the full story.

Luckily SQL Server itself can report blocking chains to you via an almost unknown feature called blocked process threshold. Mix in Blocked Process Report and DDL event notifications and you get a fully automated solution.

For an example see my answer at MS SQL Server third party transaction blocking monitor tools.

Another observation: You have 22.30% wait times on IX. This implies a object level lock occurred. Intent locks (IS, IX) are compatible so the fact that IX was blocked suggests that someone acquired an incompatible object level lock. A scan acquiring an S, or a big update escalating to X.

share|improve this answer
add comment

If you want to track down what is causing blocking, then WMI event notification with BLOCKED_PROCESS_REPORT provides you with all the details from query executing to locks used.

A way to automate can be found here http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/402c5be7-bbbb-429e-8f71-bb1d03267ef6/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.