I enjoy writing blogs as I learn always something new at Comprehensive Database Performance Health Check. Recently a client reached out to me with a very interesting question, why is he not able to see the parallelism in Express Edition. Let us discuss today.
Real-World Scenario on Parallelism in Express Edition
One of my large eCommerce clients has many different versions and editions of SQL Server installed in his environment. They had one query that was running efficiently with Parallel threads, but it always gave a poor performance when it runs on a single thread. Recently they experienced a situation where they have to run the same query on the massive 64 logical CPU machine. They had 1 standard edition and many express editions installed on the machine.
When they ran the query on the standard edition machine, it was able to utilize the maxdop (which was set to 4) efficiently but when they were running the query on the express edition, it was giving a serial plan. They wanted me to help them to have the same parallel plan on the express edition.
Well, the answer is Not possible to have Parallelism in Express Edition. If you are using express edition, your plans will always be serial and will not get any parallel plans.
Well, that’s it for today. I hope you find this information helpful when you are working with different editions of SQL Server.
If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.
Here are my few recent videos and I would like to know what is your feedback about them.
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
Reference: Pinal Dave (http://blog.SQLAuthority.com)
2 Comments. Leave new
Hi Pinal,
I came across this:
SQL Express is limited to one physical processor (socket), however if you are running multi core processors (dual core, quad core etc.) in that case sql server can achieve parallelism by using all the cores of a single processor. The following MS support aritlce confirms that http://support.microsoft.com/kb/914278
please be aware when running SQL express on a VirtualMachine: In ESX you can define either a single socket with multi core, or a multi socket with a single core. in the 2nd situation you will end up with only 1 core available for SQL Express, because SQL is limited to 1 socket. Change VM to use a single socket with multi core will increase SQL performance a lot.