Enterprise is the Cadillac Tesla Model S of SQL Server editions – unbridled access to all the things YAAAAAAAAHHHHHHHHHhRR!
Other SQL Server editions (e.g. Standard, Business Intelligence, Developer, etc) have limitations – on product features (e.g. no Columnstore Indexing) and/or hardware resources (e.g. can’t use more than 16 cores or 4 sockets…whichever is smaller) and/or usage (e.g. development-only). Which is simply not the case with Enterprise Edition.
…no hardware restrictions, no usage restrictions, and no product feature restrictions…and rightfully so because it certainly is not cheap!
…but it might be cheaper for some folks…
Enterprise “Core” vs “CAL”
If your company is in an existing agreement with Microsoft, Enterprise Edition (for SQL 2012 and 2014) actually comes in 2 licensing flavors…
- Server+CAL: where a “server license” is purchased for the server and a “CAL” (client access license) is purchased for each user who will consume data from the server.
- Core: where a “core license” is purchased for every single (physical) CPU core.
Note: Core-based licensing is actually a bit more complicated than that. Ok, its actually a lot more complicated than that. For example, there’s a core-factor used depending on the CPU architecture (e.g. AMD, single core procs, multicore procs, etc). Also there’s a few bundling levels…core-licenses are sold in groups of two…a minimum of 4 cores must be licensed per CPU socket (which is related to the core-factor). Don’t really want to get into here, don’t have the time, don’t have the energy, and everyone’s situation is different…but if you’re really eager and interested in the nitty gritty, start here.
Now, depending on your scenario, from a licensing/cost perspective, one of these 2 flavors is going to be better (read: cheaper) than the other. For example, if you have a 12 core server and 5k users, it will be much MUCH cheaper to go with a Core-based licensing strategy (because the cost of the CALs really starts to add up). On the other hand, if you have a 20 core server and only 100 users, it will be cheaper to go with Server+CAL licensing strategy (since the cost of a server license is only marginally more than the cost of a single core license).
But, if you’re not in an existing agreement with Microsoft, you will (most likely) not be eligible to use the Server+CAL licensing strategy. In which case, you can ignore the rest of this post
Read The Fine Print
Remember up at the top where I said, with Enterprise Edition you get “all the things”? Well that’s not entirely true.
While it is true that you get access to all of the product features available in SQL Server, with the Server+CAL licensing strategy there’s a hardware limit of 20 CPU cores per instance. That means if you have a big bad-ass server with 32 physical CPU cores, you will never be using more than 20 of those cores at a time on that instance. Its true, go check the log entries right after startup if you don’t believe me.
Note: if you’re in this situation, it may make sense to split the processing across 2-or-more SQL instances on the same box and allocate a group of cores to each instance so that you don’t have more than 20 CPUs allocated to any single instance. Of course, whether that makes sense or not depends on several factors…one of which is how easy (read: cheap) would it be (from a development perspective) to split the database(s) across multiple instances? Usually not very easy. How close you are to the breakeven point between Core and Server+CAL? If Server+CAL is only saving you $5k, I’d probably recommend you just suck it up and switch to Core-based licensing to tap those unused cores. Or maybe the cost difference is significant but so is the development cost so you learn a hard lesson
Here’s one place where this is stated:
By the way, just in case you didn’t notice the highlighted areas in the image above, this 20-core restriction also applies to Analysis Services and Reporting Services…wait, wuhhhhhht? Yup!
Relevant Story Time
Given this fairly lengthy lead in, we are finally ready to get to the actual point of this post…so congratulations for those who stuck with me!
On a recent project, with a client who was eligible for and in fact using Enterprise “CAL” Edition…we noticed a very strange difference (performance-wise) between “Core” and “CAL” with our Analysis Services instances. During an early round of load-testing we found that the I/O job threads was our major bottleneck…specifically, it was the “Processing pool busy I/O job threads” counter hitting a wall at 20 threads after which we’d immediately see the the “Processing pool I/O job queue length” begin to climb higher and higher. Given that we still had a ton of CPU and memory resources and our storage hardware was solid (EMC-VMAX) it just seemed strange…strange enough for me to spend a good bit of time tweaking the msmdsrv.ini config trying to push through this barrier…surely we can get more than 20 concurrent I/O job threads!?!
Eventually I resigned to opening a thread on the MSDN forums after reading just about everything there was to read on Analysis Services thread tuning. Unfortunately, we didn’t get a good reply in the MSDN forum thread (or during the subsequent escalation to Microsoft – despite working with some insanely knowledgeable escalation engineers). As you can see (if you read the MSDN forum post) my lingering suspicion was that this “concurrent IO job threads” limit was directly related to the “concurrency limit of 20” message seen in the msmdsrv.log file (below)…
…and so I was on a mission to figure out this “concurrency of 20” business…what it actually represented and eventually how to increase it and push through the current limits of the system. Unfortunately, that mission was pretty short as there’s just not a lot of relevant information out there…and so it was left unanswered.
But then we had a breakthrough…
…thanks to one of the architects at this client, who I’ll refer to as EG (short for “Evil Genius”) who decided to try installing Enterprise “Core” Edition to see if that made a difference…even though I told him it would be a waste of time. These servers did not have more than 20-cores and therefore wouldn’t be hitting the CPU core limit for Enterprise “CAL” edition. Plus the bottleneck wasn’t even related to CPU – we still had plenty of CPU power waiting for us once we pushed through this bottleneck.
Thankfully, EG was a thorough genius, and proceeded to to test it anyways. Later that day, he informed me that the new install was out performing the rest of SSAS servers by a wide margin. Shocked and in disbelief, I immediately fired up performance monitor to check the counters. Happiness barely begins to describe how I felt when seeing that this new server (same specs as the rest) had pushed way past the previous barrier of 20 “Processing pool busy I/O job threads”!
Looking at the msmdsrv.log file on this server showed the following:
…and we rode off into the sunset!
But from a knowledge-seeking perspective – I’m still stumped. Is this a bug or is it working as designed? If you know the answer, please reach out 🙂
Only SQL Server Enterprise “Core” Edition gives you “All The Things”!
Enterprise “Server+CAL” gives you “Most of the Things!” but limits the number of CPU cores available for a single SQL Server Instance…that applies to the Database Engine, Analysis Services, and Reporting Services.
Additionally, for Analysis Services, the Enterprise “Server+CAL” setup will also limit the concurrency of your threads. This is not stated in any documentation of which I am aware. I’m also unsure if/how this manifests itself in the DB Engine and/or Reporting Services…as we simply stumbled upon it with one of my clients.
None of this is even a factor for those who are not already in an agreement with Microsoft and eligible for “Server+CAL” licensing model…in fact you should have stopped reading a good while ago …new customers will only be offered Enterprise “Core” Edition. Fortunately for SSAS scenarios (which is the majority of my clients), you can (and should) consider Business Intelligence edition which is licensed using the “Server+CAL” pricing strategy and is IDENTICAL to Enterprise “Core” Edition…yes, no CPU limitations and no concurrency limitations…again, that’s just from an Analysis Services perspective.
Btw, here’s the same server with BI-edition installed proving it doesn’t have the concurrency limitation…
In the next post, I’ll provide a SSAS-focused breakdown to show you how to evaluate whether to go with Enterprise Edition or Business Intelligence Edition for your Analysis Services Environment. Stay tuned!