Sql Server performance
- by Jose
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?
