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.
Best Answer
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.