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.

On our sql server, a query takes 20 ms. If I look at the execution plan, parallelism is used, hash match, Bitmap create, ... a lot of images with two arrows pointing to the left.

On sql server of a customer where our product runs, the same query takes 2500 ms. If I look at their execution plan, no parallelism or any of the things with arrows are used...

I've been searching for a couple of days no why the query runs so much slower on their sql server.

  • Is parallelism and all of the other things something that can be configured on their sql server?
  • And how to you configure that?
  • What are the dangers of using parallelism?

Another strange thing is that on our server, the query needs some 1200 reads and 0 writes. On their sql server it needs 1.5 million reads and some 1500 writes. Why these writes when a read query is done?

share|improve this question
1  
Are the datasets absolutally identical (Same data, same indexes, same statistics, etc)? –  Mark Henderson Mar 20 '10 at 9:35
    
Yes, but I've just seen that degree of parallelism is set on 1 at their sql server... –  Lieven Cardoen Mar 20 '10 at 9:40
    
could you update all of the questions you have asked regarding this to point to this question and solution? It might help tie all of these questions together. Seems that it might assist others in the future that encounters this particular problem. –  doug_w Mar 21 '10 at 21:08
    
@doug_w Yes, will do that. But the answer is still not very clear. I'll update it and do what you've asked when I have all the information at hand. thx. –  Lieven Cardoen Mar 22 '10 at 18:08
add comment

1 Answer 1

up vote 1 down vote accepted
  • Parallelism depends on server cores. Without multi core you can not paralellize. So, is the hardware identical? ;)
  • You can configura mximum paralellism so that you dont have one user blocking up the complete server, but no minimum.
  • The dangers? Well, besides the fact that SOMETIMES it goes terribly wrong in some version of SQL Server (and is a lot slower than without - action: patch up your sql server) acutlally is reduces the number of CONCURRENT statements that are executed, as one statement then uses more system resources.

On their sql server it needs 1.5 million reads and some 1500 writes. Why these writes when a read query is done?

If you look at the query plan you will most likely see it is NOT pure reading because it involves some temporary objects which - obviously - are being written to. Sorting, merging of paralellsim can all result into write operations in temporary objects.

You can see read/write on EVERY step of the execution plan.

On sql server of a customer where our product runs, the same query takes 2500 ms. If I look at their execution plan, no parallelism or any of the things with arrows are used...

Did you check the obvious? Crappy hardware - bad CPU (unlikely), not enough RAM and a totally overloaded IO subsystem? Many people virtualize SQL servers these days, and somehow they forget their brain in all that savings and put a crap IO subsystem below, so that your SQL erver is a LOT slower - because the disc layout sucks.

I would have to see and compare both execution plans (can you upload screenshots somewhere? or best, upload the exported plans so I can load them into enterprise manager).

share|improve this answer
    
Hi TomTom, I've just seen that degree of parallelism is set on 1 at their sql server. The server has 8 cpu's. Could there be a reason that they set this to 1? On our server, we have 4 cpu's and the degree is set to unlimited. That's why on our server, the query goes a 100 times faster... I can upload the execution plans, but you'll see that the difference is that on our server parallelism is used and on their server not. Thx for your help by the way! –  Lieven Cardoen Mar 20 '10 at 11:23
    
My next question now would be if they have a reasonable explanation as why they would set the degree on 1. Maybe they think that decent queries do not need parallelism? –  Lieven Cardoen Mar 20 '10 at 11:24
1  
Reason for setting it to 1? I assume dump admin, or a query where the system went wrong. Maybe they did run into a query where the parallel plan was terrible thanks to an error - that may have been fixed ever since. –  TomTom Mar 20 '10 at 18:11
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.