SQL Query takes a lot of time in random order

Question

I run a really simple query on a really small table (<10 rows, 5 columns) on SQL Server 2005 and usually returns results instantly, but sometimes it takes a very long time (for example, 5-10 seconds). I know that our server is quite busy, and this is probably the reason (since I do not think that this can happen due to locks - no one writes to this table), but I need to find a bottleneck somehow.

Any suggestions on how I can find the exact server resource to make such simple queries lasting?

Answer 1

The only thing you need is profiling. You need to have an idea of ​​memory, input / output and processor. You need to know which of these 3 causes the server to slow down. There are many products that do this (there is even a good performance monitor that comes with windows installed).

Do not think about it, you need to see the data in order to understand the fundamental problem.

Answer 2

In SSMS, right-click the connected server in Object Explorer and select "Action Monitor."

Here you can see recent expensive queries and other performance data.

Answer 3

In addition to starting the profiler and checking also on page life expectancy , as well as buffer cache hit ratio See: Use sys.dm_os_performance_counters to get the hit rate of the buffer cache and page life expectancy counters

it can also be (but you have to check) what is happening, is that the data you are looking for has gone out of cache to RAM, now it should get it from disk and it will take longer when you start it again in a second, he will be fast again.

you can check by doing io on statistics

 SET STATISTICS IO ON select * ..your query 

you should see something like this

Table 'TableNAme'. Scan Number 1, Logical Read 4, Physical Read 2

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

you can check it (not in production)

deleting data from RAM

 DBCC freeproccache DBCC DROPcleanbuffers 

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

Table 'TableNAme'. Scan Number 1, Logical Read 4, Physical Read 2

Table 'TableNAme'. Scan Number 1, Logical Read 4, Physical Read 0

Answer 4

You do not need to write to the table to get a lock. You can try changing one of the SELECT statements to use WITH (NOLOCK) Another statement (insert / update / delete), which is very slow, and joining this table may block it.