I'm getting really slow queries in MS SQL Server 2008 R2 on my dev machine. This problem has been plaguing me for about a month. Other developers don't have the same problem, but we all run the same code. It seems to be that any query that includes a JOIN takes >20s, some taking up to a minute. Inserts and updates are fast. The total database size is about 30MB, so it's hardly huge.
During the laggy queries, the CPU usage stays flat, the IO rates stay low, and the pagefault delta stays low too. I've not tweaked any performance settings in the db config – it's all stock from the setup.
The software that connects to the SQL server is running on the same machine as it. I've tried multiple dev database copies, and customer databases that are known to be fine, all to no avail.
Any ideas what might be causing this?
Best Answer
Maybe you have a lot of BLOBs (Binary Large OBJectS) stored in your database. That happens if you store very large binary objects in your db, such as other databases, or perhaps zip packages or whatnot.
That can become a performance killer, and is quite possible if you allow folks to upload files through something like Sharepoint.