Archive for the ‘sqlserver’ category

May you live in interesting times…

May you live in interesting times” is an English expression purported to be a translation of a traditional Chinese curse.


Doing some quick math here:

One of these is CLEARLY not like the others – and, in fact, were it not for that specific thing (and the entire climate leading up to that disaster + everything that’ll follow thereafter), I’d say that living in interesting times was hardly a curse…

SQL Server Standard Edition Memory Limits – A Better Way?

With SQL Server 2012, Microsoft changed how SQL Server was licensed – by making customers pay per core instead of per socket. While this caused grumbling among SQL Server professionals and customers, I’ve always seen it as a MOSTLY fair move on Microsoft’s part – as their goal with licensing SQL Server has always been to charge customers based upon the amount of compute being handled by SQL Server.

Where this change feels a bit less than fair, though, is when it comes to the artificial limitations put into place against SQL Server’s Standard Edition’s use of RAM (SQL Server 2012 Standard Edition was limited to an absurd 64GB of RAM, whereas SQL Server 2016 and 2016 Standard Editions are limited to a still paltry 128GB of RAM).

Stated differently, I think it’s fair for Microsoft to charge customers per core – but not if they’re going to heavily curtail how much compute you can do with this core by severely limiting the amount of RAM you can allocated per core. (Standard Edition Licenses weigh in at around $2,800 per core – but if you’ve dropped $45K for a 16 core 2014 Standard Edition box, you’re limited to a paltry 8GB of RAM per core – whereas a 2016 instance with 24 cores would cost $65,000 yet top out at a miserly 5.33GB/core of RAM – or less RAM per core (for a higher overall price).)

A Better Way?

Yes, there are ways around this limitation (like using Buffer Pool Extensions (available on Standard Edition) or even potentially spinning up multiple instances per OSE – as the 128GB RAM limitation is per instance NOT per host), and Enterprise Edition really isn’t that expensive when you compare it to Oracle or even when you think about how much it will DO for you in a given year (and compare how much it costs per year to, say, what you’d pay 2 or 3 employees per year).

Still, I think there might be a better way: Enable up to 32GB of RAM per each 2-core Standard Edition ‘pack’ licensed. With this approach, a simple 4-core VM could pull off up to 64GB of RAM, a ‘decent’ Standard Edition instance with 8 cores could pull off up to 128GB of RAM (the max currently in place today) and a higher-end 2016 box with 24 cores (i.e., 12x 2-core packs) could top out at 384GB of RAM.

Arguably, this approach would add a tiny bit of additional complexity into the licensing landscape (and orgs running 4-core boxes today with 128GB of RAM would obviously find fault with my ‘logic’), but I’d wager that medium to large Standard Edition instances would benefit tremendously – to the point I’d even guess that Microsoft Support Services would probably field a slightly smaller number of support calls for performance related issues simply because of the extra benefits additional RAM can provide to SQL Server workloads.

SQL Sentry’s Plan Explorer PRO – Now Completely Free

Wow, that’s very impressive – and a huge win for SQL Server Professionals everywhere.


SQL Sentry just announced that the PRO version of Plan Explorer is now completely free – as in you don’t even have to register to use it. (For those in the know, there used to be two versions: a ‘lighter-weight’ free version, and a paid-for PRO version; they’ve now consolidated all features into the PRO version – and made it free.)

Spoil Yourself with Better Insight and Analysis

I’ll be honest: since I spend so much of my time on my clients’ machines when analyzing performance problems and looking at execution plans, I’ve made a conscious effort in the past to avoid using Plan Explorer too much in my Lab – for fear of spoiling myself.

This announcement changes all of that – meaning that I’ll have a whole suite of additional tools at my disposal when performance tuning or troubleshooting.

SQL Sentry’s Motivation

It doesn’t take a genius to see that SQL Sentry is trying to use Plan Explorer PRO to establish a ‘foot in the door’ with as many potential customers and clients as possible.

But a maneuver like this only works with a market when you’re providing something that users REALLY want, management can easily see the value being offered, and the tool being offered is really solid (non-buggy).

Interestingly enough, that sums up Plan Explorer PRO perfectly.

One of My Favorite Undocumented T-SQL Snippets

While there are tons of undocumented T-SQL commands out there, one of my all-time favorites is this little gem:


It’s not much, but it will show you default details on:


 Arguably, there are better ways to retrieve some of this information (like SELECT * FROM sys.dm_server_registry;).

But SERVERPROPERTY(‘ErrorLogFileName’) works just fine when looking for low-level details.

The only real problem I have with this particular snippet of T-SQL is that I can never remember EXACTLY what the property is to ‘search’ for (i.e., ‘ErrorLogFileName’) – which is part of why I’ve blogged about it (so that I’ll be able to more easily find this snippet in the future).