SQL Query takes very long at random times

Question

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 simple queries run so long?

Answer 1

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

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