Hidden schedulers are used to process requests that are internal to the engine itself. Visible schedulers are used to handle end-user requests. When you run the casual SELECT * query, it will utilize a visible scheduler to process the query. With this information, if I have a 64 core server and all is well, I should have 64 visible schedulers online to process requests.
However, I discovered that some of the schedulers were set to “VISIBLE OFFLINE”. This essentially means that those particular schedulers are unavailable to SQL Server for some reason. How many offline schedulers do I have? A quick query resulted in 24 schedulers currently offline. 24 logical cores means that 12 physical cores are offline.
But why would a scheduler be set to “VISIBLE OFFLINE”?
Read on for the answer, and check the comments for a helpful plug for sp_Blitz.
The DTU Calculator, a third-party service created by Justin Henriksen (a Microsoft employee), will calculate the DTU requirements for our on-premises database that we want to migrate to Azure, by firstly capturing a few performance monitor counters, and then performing a calculation on those results, to provide the recommended service tier for our database.
Justin provides a command-line application or PowerShell script to capture these performance counters:
Processor – % Processor Time
Logical Disk – Disk Reads/sec
Logical Disk – Disk Writes/sec
Database – Log Bytes Flushed/sec
For more details on DTUs, John Sterrett looks at the math.
- “A minimum of 16 core licenses is required for each server.”
- “A minimum of 8 core licenses is required for each physical processor.”
When most sysadmins see that, they’ll think, “Okay, so I shouldn’t bother buying a server smaller than 2 8-core processors.”
There are plenty of scenarios in which this doesn’t hurt (much): mainly when you need a hefty server with more than 16 cores, or when you are running in a virtualized environment and can split that hardware across a number of logical servers.
As for me, this is one reason why I’m looking forward to SQL Server on Linux.
Copied from somewhere else on the internet, this PowerShell script will return the product key used for a SQL instance Install. Super useful when changing licenses on temporary VM’s I spin up and play around with to SQL Developer whose instances have passed the Enterprise evaluation use-by date. Putting this here for my own benefit. I claim no kudos!
Click through for the code.
When I see those numbers in Microsoft marketing slides, I sometimes wonder if they can be real, but then I put these numbers together myself. Granted you would get some discounts, but the fact that all of these features are built into SQL Server, should convince you of the value SQL Server offers. Pricing discounts are generally similar between vendors, so that is not really a point of argument. If you are doing a really big Oracle deal you may see a larger upfront discount, but you will still be paying your 23% support fees on that very large list price. (Software Assurance from Microsoft will be around 20%, but from a much lower base) Additionally, several of these features ae available in SQL Server Standard Edition. None of these features are in Oracle’s Standard Edition.
Postgres is a really good database engine, with a rich ecosystem of developers writing code for it. SQL Server on the other hand, is a mature product that has had a large push to support analytic performance and scale.
Additionally, this customer is leveraging the Azure ecosystem as part of their process, and that is only possible via SQL Server’s tight integration with the platform.
This isn’t a direct comparison to help determine in some absolute sense which product is better, but rather looking at a use case from a customer which takes advantage of many of the features in SQL Server.
So, that’s it! Right?
No so fast! There are other factors that may come into play, or you may be wondering about. You may think they are part of Power BI, but they may be separate.
What I like about this post is that Adam goes into detail on some of the other potential costs involved aside from product licensing.
Now, look at the hyper-converged architectures. The CPUs used for I/O handling are the same as those that your VMs use to power your applications. A substantial portion of the host’s CPU power is now needed for I/O handling, and this activity comes first in the CPU scheduling queues.
This fact, by itself, is not necessarily a problem. Most virtualization host CPUs are relatively lightly utilized, and this amount of CPU needed for I/O handling is readily absorbed without causing a performance problem.
But, larger scale SQL Servers can read and write exceptionally large amounts of data around the clock. The I/O handling at the host layer can start to drain resources from the host. The additional activity scheduling time inside the hypervisor could be slowing down these SQL Servers without you knowing it.
As always, work out how your licensing would look under different alternatives or you could end up wasting a pretty penny.
SQL Server 2016 launched last week to great reviews and with a ton of great new features. I have been working with this version for well over a year now and extremely happy to see it hit RTM and be broadly adopted. So as DBAs it always sucks when you get excited about new features, only to find out the price changed, or vendor “O” made that feature a cost option. So what’s new with SQL Server 2016 licensing? (you won’t this as a session title at any upcoming SQL Server events). Well first the good news—SQL Server 2016 is the same price and 2012 and 2014 (roughly $6800 core for Enterprise Edition). That’s definitely good news—Microsoft gave us a bunch of new functionality and didn’t raise the price. Additionally, if you see my below post on what is in Standard Edition, they added a lot of functionality there, too.
But we know finance and marketing employees have jobs to do as well, and there is no way they were letting a major version release happen without some changes. So let’s take a look at the one’s Denny Cherry (b|t) and I could glean out of the licensing guide. Please download and read for yourself.
There are a couple of interesting nuances that you’ll want to read up on.
– Two Major Licensed Editions: Enterprise and Standard;
– Enterprise Edition can be licensed only “By Core”. Standard also available on “Server+CAL*” basis;
– If you have SA** you can still use your old CAL licenses with SQL Server 2016 Enterprise Edition, but will be limited by usage of only 20 Cores on your server;
– Standard Edition is limited by 4 Sockets/16 Cores and 128 Gb of Memory;
Licensing is boring, painful, and ultimately necessary to understand.
Before 2016, you had to manually opt-in by checking a checkbox during installation.
With SQL Server 2016, there’s no checkbox – you’re opted in by default.
I’m actually a huge fan of app telemetry – sending crash reports and usage data back to the application developers in order to help make the app better. I want developers to know how I use their apps, because I want them to improve the parts of the app that I use the most. Heck, I’d be fine if SSMS turned on the microphone while I worked, and then did sentiment analysis. (They would see a very high number of four-letter words tied to the term “IntelliSense.”)
I’m generally fine with sending telemetry results, but I also think the option to disable this should be easier than a registry setting.