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.

How is it possible that a cached query could take that long. With cache cleared it takes 25000 ms (I know that's just way too long but that's not the issue here). Once it is cached, it takes 2500 ms. What could be the problem here? If it's cached, I assume Sql Server gets it out of the RAM. So, again, 2500ms???

share|improve this question
    
post the query and execution plan as xml. You will get better help. Sounds like you have something going on like a function in the where clause. –  doug_w Mar 19 '10 at 13:55
    
Well, the query takes 2500 ms on sql server of a customer and 20 ms on our sql server. Settings are the same. Only difference is that there sql server is more powerfull (more cpu + RAM) and is virtual. –  Lieven Cardoen Mar 19 '10 at 13:58
    
Specify how powerfull their VIRTUAL MACHINE is - not the phsyical server. Chek IO, CPU and.... IO LOAD. Especially IO... You did not say anything about IO ;) –  TomTom Mar 19 '10 at 14:53
    
Man, really, ok, that's right, but I just want to know what the most probably reason is that a cached query would take 2500 ms on a virtual server that is more powerfull than our server where the same cached query takes 20ms. –  Lieven Cardoen Mar 19 '10 at 15:21
    
Not knowing anything (since nothing has been provided) I'm just going to go ahead and blame your VM. Doesn't matter how beefy the machine the VM is sitting on is, if there is a configuration issue with the VM (like if you don't do "hard" memory allocations, or sufficient space allocated, or whatever) that will absolutely have implications on your database performance. –  Satanicpuppy Mar 19 '10 at 15:40

1 Answer 1

up vote 3 down vote accepted

Because it may take so long?

Cached means the query plan is cached, not the results. If the cache is sub optimal, or you just have crappy indices or a lot of data - why not? ;) I have seen queries run for hours - after the plan was determined.

Now, you dinciate that is on a VM - may I just say that VM's NORMALLY totally suck for databases. Noting wrong with them in general, but when I plan a proper database server I spend time thinking about my disc layout. Most VM implementations I have seen - ignore that. Result: crappy IO. Resiult: SLOW PROCESSING.

Sorr, VM does not abstract away physical reality.

I would really look at the VM - allocated RAM, CPU, discs - and then go down to the physical level and see how the discs translate into pyhsical discs.

share|improve this answer
    
very true about misconfiguring vm hosts. Most folks that are on the ball during a virtualization assessment are shocked to here how much IO all of their machines do without any special load, and are suprised that the san they thought they were going to buy was way undersized –  Jim B Mar 19 '10 at 23:27
    
Gets worse - I know hosts (hosting for cheap VM) that get a decent server (dual opteron, 64 gb) and plug in a high performance.... mirror of.... 2tb hard drives, as the size is ok and the price even more. The result obviously is terrible even for booting all the (cheap) vm's. –  TomTom Mar 20 '10 at 6:18
    
VMware engineers say that your disk speed is the key (for their product). As fast as possible. Sounds like a lot of other things. –  jl. Mar 22 '10 at 19:32
    
Absolutely. Discs are super critical. I run a nice hyper-v server and when it is starting it's OS I can see the RAID 10 of velociraptors totally being trashed. You REALLY need to have HUGH eye on IO. ONE disc is challenged by a computer at times. Now a VM server puts 20-30 computers on that disc. Not the place to have cheap large discs. –  TomTom Mar 22 '10 at 19:45

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.