jump to navigation

Timeout | Blocking | Wait types November 2, 2009

Posted by msrviking in Performance tuning.
trackback

I am into a performance analysis and tuning assignment wherein I need to identify a “mysterious” performance issue. Sounds familiar by words, “Yes” but its really difficult if one doesn’t know where to start (the state I am in the beginning of the exercise).  Well, all these aside, but here is the problem statement given by the team “We have timeouts and deadlocks happening”.

With this small and simple one liner I started thinking like this,

– What is a time out? I had to get back to basics  for this and per definition which I love to read again and again is

“Before executing a query, SQL Server estimates how much memory it needs to run and tries to reserve this amount of memory from the buffer pool. If the reservation succeeds the query is executed immediately. If there is not enough memory readily available from the buffer pool, then the query is put into a queue with a timeout value, where the timeout value is guided by the query cost. The basic rule is: higher the estimated cost is, larger the time out value is. When the waiting time of this query exceeds the timeout value, a time out error is thrown and the query is removed from the queue.”

Well keeping in this in mind, I went hunting for memory, CPU configuration of the box.

– What is the configuration of the RAM, CPU and how is it doing?

64G RAM, and 8 core machine is having time out and deadlocks. Wow! Can’t believe it and so I decided to go to the box and check it myself on how it is doing. To my surprise all looks fine (RAM and CPU) running below the threshold levels. So what is that I am hunting for?

Over years of experience gave me a gut feeling which is usually very close to solution or at least to the  problem to start with, in such confused state of mind.  So my gut feeling was saying all looks fine so what is bad going on here.  A strong thought – maybe processes are getting blocked, and all these processes are accessing same tables through different t-sql batch of statements (procedures, functions).

– Does blocking cause timeouts?

This was the first question I had with series of questions like if timeouts are happening why isn’t evident in CPU /RAM utilization. What is going wrong and where? These questions started haunting me which led me to these interesting links which I am consuming (may be 10th time) to understand what should be my next question.

Here are the links for you guys

http://www.informit.com/articles/article.aspx?p=686168

http://sqlserverpedia.com/wiki/Wait_Types

I am curently using these links to arrive at some information for analysis and tune the damn queries causing performance issue through blocking.

I shall keep you guys posted on how I progress, but remember yes blocking & wait type are related (although I didn’t mention about wait types explicitly in the post) and yes of course you have time outs because of blocking which I shall confirm it after this.

Maybe you guys know all these, and even I do but thought of blogging as my thoughts were flowing else I need to spend “regurgetating” you see ;).

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: