Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
A raw guess: have a look at the system log to see if any disk failure –  Gregory MOUSSAT May 25 '12 at 15:18
    
@GregoryMOUSSAT No entries in there but the usual service control manager notifications. It's a brand new machine, too. –  Polynomial May 25 '12 at 15:22
    
Any difference in anti-virus? You could run Belarc your machine and someone else's, see what's different. –  gravyface May 25 '12 at 15:24
    
@gravyface All dev machines run MSSE, configured to skip scanning MDF and LDF files in the data directory. –  Polynomial May 25 '12 at 15:25
1  
I'd still do the Belarc. –  gravyface May 25 '12 at 15:35
show 9 more comments

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
This might actually be something. We store attached files to some records as BLOB. They're only a few hundred KB each (mainly JPEGs / XLS files). I've got a couple of ideas about where this might be slow. This gives me a few avenues to look at, thanks :) –  Polynomial May 28 '12 at 5:54
    
This was (part of) the problem. We were creating a background task that pulled out the files into memory for caching, which left a dataset open. For each row we pulled, we did another query to get some user info. I'm not sure why, but when I moved everything around into a JOIN and removed the extra queries, everything runs smoothly. Interestingly, when I checked query execution time, it only took a few milliseconds to execute each query on the db end. The network connection looked clear too, so I've no idea where the lag was. I'll mark this as accepted since it prompted me to the solution. –  Polynomial May 28 '12 at 8:48
    
There are two ways that I know of to get around the issue. One is to use a custom component as an "uploader" to place the files into your file system, instead. Another thing is a sort of external blob storage. However, from your last comment (thanks for the points, by the way) you seem to say that what causes you to lose time is from your query processing a request where it has to filter out jpeg files? So the real solution was optimizing your query? –  BGM May 28 '12 at 14:49
    
Not quite. The issue was that, when opening more than one result set at the same time, the code just hung for a few seconds. This reasoning seems to hold, since the other areas in our codebase that lagged were doing the same thing, and areas that weren't doing that didn't lag. I've refactored them to now use JOINs, or hold the results in a temporary list before doing the subsequent queries. Much faster now :) –  Polynomial May 28 '12 at 15:26
    
Absolutely clueless as to why that's causing a problem though. My colleague noticed the same issue a while back and just avoided using multiple result set handles at once. It's probably not worth the time to go dig into the code to work it out. –  Polynomial May 28 '12 at 15:27
show 3 more comments

Joins can press the envelope on tempdb performance. Generally speaking, tempdb should be the fastest and most optimized database on the server. The reality is often quite different.

Tempdb should be on an ssd, and it should not be configured to auto-grow. Auto-grow can be a huge performance killer. On a production system, tempdb should have one partition/file per processor core.

share|improve this answer
    
This is just a lowly dev machine running on a laptop. Same goes for everyone else. Nobody else gets the problem. –  Polynomial May 25 '12 at 15:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.