question

Apeman avatar image
Apeman asked

Parallelism and CPU speed

Is MSSQL aware of the processor type /speed when determing if a query can run parallel?
I would say if you have low speed CPUs, parallelism will happen more often compared to a set of fast CPUs (but that's just my brainwave)

parallelism
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

No, the optimizer doesn't measure the speed of your processesors when determining parallelism. It has the number of processors that you've designated as available for SQL Server, the threshold for parallelism, and the estimated costs of the query based on the statistics available when the optimizer runs. It uses these to determine whether or not a query should run in a parallel fashion. No other measures are made against your specific machine.

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

There is overhead involved to use multiple processors regardless of their specs. The speed of CPUs is really a non-factor. The optimizer decides whether a single CPU will perform adequately or whether multiples are better even with the switching overhead.
1 Like 1 ·
And the overhead for parallelism can be pretty costly. I think the default value of 5 is stupid low. Generally, with exceptions, most of my servers are reset to 25 right out of the gate.
1 Like 1 ·
Fatherjack avatar image
Fatherjack answered

Yes the query optimiser is able to make decisions based on the hardware made available to SQL Server - that isnt necessarily the same as the hardware that exists as it is possible to internally configure SQL Server to only use certain processors/RAM/HDD etc.

If you get a query that has an execution plan that shows parallelism then it is worth checking out to see if this is the best option but in my experience its pretty often close enough to not make changes to avoid it. That may be due to the hardware I am using or the awfully simple queries that I run - You Mileage May Vary

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Maybe I'm not clear enough. I was wondering if an execution plan changes (say to parallel) if I just replace the CPUs with a faster one, not the amount of CPUs (or cores). So will the execution plan for a 1.66Ghz be different compared to a 3.2Ghz? And same if I upgrade to a newer processor architecture?
0 Likes 0 ·
no, as Grant has explained in better detail than me, it doesnt measure CPU spec, simply how many CPUs are available.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.