Sql Server performance

Question

I know that I can't get a specific answer to my question, but I would like to know if I can find the tools to get to my answer.

Ok we have a Sql Server 2008 database that for the last 4 days has had moments where for 5-20 minutes becomes unresponsive for specific queries.

e.g. The following queries run in different query windows simultaneously have the following results

SELECT * FROM Assignment --hangs indefinitely

SELECT * FROM Invoice -- works fine

Many of the tables have non-clustered indexes to help speed up SELECTs

Here's what I know: 1) The same query will either hang indefinitely or run normally. 2) In Activity Monitor in the processes tab there are normally around 80-100 processes running

I think that what's happening is 1) A user updates a table 2) This causes one or more indexes to get updated 3) Another user issues a select while the index is updating

Is there a way I can figure out why at a specific moment in time SQL Server is being unresponsive for a specific query?

Answer 1

The sp_who system stored procedure will tell you if a connection is blocked because of another query.

Answer 2

When a query (such as your examples) hangs, the most common cause (in my experience) is that another query is blocking it. This might be apparent.

To diagnose this, I simply uses Activity Monitor in Sql server Management Studio.

Find the node that is being blocked and through that, you can find what is causing the block. Then at least you isolate the problem. The next step is to prevent the blocking... That will certainly depend.

For me, it's been a long running BEGIN TRAN contents.