MsSql Server high Resource Waits and Head Blocker
- by MartinHN
Hi
I have a MS SQL Server 2008 Standard installation running a database for a webshop. The current size of the database is 2.5 GB. Running on Windows 2008 Standard. Dual Intel Xeon X5355 @ 2.00 GHz. 4 GB RAM.
When I open the Activity Monitor, I see that I have a Wait Time (ms/sec) of 5000 in the "Other" category.
In the Processes list, all connections from the webshop, the Head Blocker value is 1.
I see every day that when I try to access the website, it can take 20-30 secs before it even starts to "work". I know that it is not network latency. (I have a 301 redirect from the same server that is executed instantly).
When the first request has been served, it seems as if it's not a sleep anymore and every subsequent request is served instantly with the speed of light.
The problem was worse two weeks ago, until I changed every query to include WITH (NOLOCK). But I still experience the problem, and the Wait times in the Activity Monitor is about the same.
The largest table (Images) has 32764 rows (448576 KB). Some tables exceed 300000 rows, thought they're much smaller in size than the Images table.
I have the default clustered index for every primary key column, only.
Any ideas?
