SQL Query takes very long at random times

I am running a really simple query on a really small table (<10 rows, 5 columns) on SQL Server 2005 and usually it returns results instantly, but sometimes it takes very long to complete (like 5-10s). I am aware, that our server is quite heavily loaded and this is probably the cause (as I don't think that it can happen because of locks - nobody's writing to that table) - but I need to find the bottleneck somehow.

Any suggestions on how could I find the exact server resource, that's making such si开发者_高级运维mple queries run so long?

In SSMS right-click connected server in Object Explorer and choose Activity Monitor.

There you can see Recent Expensive Queries and other performance data.

In addition to running profiler and checking also for page life expectancy and also buffer cache hit ratio See : Use sys.dm_os_performance_counters to get your Buffer cache hit ratio and Page life expectancy counters

it could also be (but you have to test) is what happens is that the data you are looking for got bumped out of the cache in RAM, now it has to get it from disk and that will take longer, when you run it again a second later it will be fast again

you can check by running with statistics io on

SET STATISTICS IO ON

select * ..your query

you should see something like this

Table 'TableNAme'. Scan count 1, logical reads 4, physical reads 2

if you see physical reads above 0, it grabbed it from disk

you can verify this (not on production)

by dropping the data from RAM

DBCC freeproccache
DBCC DROPcleanbuffers

now when you run a query twice, you will see something like this, the first run will be from disk, the second from RAM

Table 'TableNAme'. Scan count 1, logical reads 4, physical reads 2

Table 'TableNAme'. Scan count 1, logical reads 4, physical reads 0

The only thing that you need is profiling. You need to have an idea about memory, input/output and processor. You need to know which of these 3 is causing the server to slow down. There are a lot of products that does it (there is even an okay performance monitor that comes with windows installed).

Don't "think" about it, you need to see data in order to understand the fundamental issue.

You don't need to write to the table to get a lock on it. You could try modifying one of your SELECT statements to use WITH (NOLOCK) Another statement (insert/update/delete) which is very slow and joins to this table might be locking it.