Tune in here to watch our webcast video for this week! To join our weekly webcast for live Q&A, make sure to watch the video by 12:00 PM EST on Tuesday, October 7! Not only do we answer your questions, we also give away a prize at 12:25 PM EST – don’t miss it!
Have questions? Feel free to leave a comment so we can discuss it on Tuesday!
You have a sneaking suspicion that your servers aren’t all paid for, and you need to get a rough idea of how SQL Server licensing works. You’ve never bought a box of SQL Server before, and you have no idea where to get started.
Microsoft Certified Master Brent Ozar will break it down into a few simple, easy-to-understand slides and show you the most popular licensing options. He’ll also explain 3 classic licensing mistakes and help you avoid ‘em in this 20-minute video.
To ask licensing questions after watching the video, join our weekly webcast for live Q&A. Not only do we answer your questions, we also give away a prize at 12:25 PM EST – don’t miss it!
Have questions? Feel free to leave a comment so we can discuss it on Tuesday!
What you need to do: log into SQLpass.org and change your password. If you use the same password anywhere else, change it on all of those sites immediately as well.
Why you need to do it: anytime you ever logged into SQLpass.org or SQLsaturday.com, or updated your password, or created an account, your password (along with everything else) went unencrypted between your computer and PASS’s web servers. Anyone sniffing network packets along the way saw your username, email, password, etc in clear text. (Think about what a security gold mine this would have been for someone sniffing WiFi at a SQLSaturday or the PASS Summit.) There’s a nice side benefit for updating your account – you also become eligible to vote in the current PASS elections.
Who you need to thank: the vulnerability was discovered by George Stocker on Friday, and PASS HQ finished the fixes & testing on Saturday. That’s a fantastic turnaround time – kudos to PASS for reacting so fast!
Who you should blame: yourself, for not noticing for years that you were putting login information into a web site that wasn’t using https. What kind of data professional are you, anyway? You’re probably even using that same password on multiple web sites, or heaven forbid, your company email. Get it together and use 1Password or LastPass.
Who you should not blame: the current PASS Board of Directors because this has likely been in place ever since PASS set up their current web site, and the current management inherited this little surprise. (You know how it goes – it’s like your ancient SQL Server 2000 in the corner where everybody knows the SA password.)
What’s still left to do: PASS needs to clearly, concisely communicate the severity of this issue to members as well – I really wish they’d set all passwords in the database to null, and force everybody to go through the password-reset process. The SSL setup still needs some work, as shown in the SSL tests, but considering the whole thing was done in 24 hours, it’s one heck of a good first step. (SQLsaturday.com also fails that test.)
We had a comment recently on Five Things That Fix Bad SQL Server Performance that got me thinking. The comment came from a frustrated system administrator, who wrote:
Chucking resources at a problem is not solving that problem, it’s just temporarily masking the symptoms of that problem.
Funnily enough, I’ve heard the exact same thing from developers who are facing a tough problem on a dramatically undersized SQL Server. The basic gist of what they ask is:
Doesn’t it make me a bad developer if I can’t solve this problem with code?
Everybody’s worried that adding memory is somehow cheating.
Performance Problems and Memory
There are three general categories of performance problems relating to memory:
- Problems you can solve with memory OR code / indexes
- Problems you can solve only with code / indexes and NOT just memory
- Problems you can solve only memory and NOT just code / indexes
For category #1, solving the problem with memory is often cheaper than changing code and indexes: the operational cost of the hours of development and testing can be quite high.
But I specifically started thinking about category #3. This is the category that the frustrated sysadmin and proud developer think doesn’t exist!
Here are three examples of problems that you can solve with memory, and not simply with code and indexes:
1. Memory Reduces IO On a Read-Hungry Workload
You’ve got a critical OLTP application. You’re using shared storage. Most of the time, your storage throughput is pretty good. But sometimes, other users of the storage get busy, and your read and write latencies go through the roof. Your queries slow down, blocking increases, and timeouts start hitting the application. You’ve tuned your indexes, but parts of the application rely on realtime reports that need to scan large tables.
SQL Server is designed so that in this case you can add more memory and reduce read IO to the storage, giving you more stable performance. It’s absolutely not cheating to give the server more memory — speeding up the storage would be much more expensive.
2. Some Queries Need Lots of Workspace Memory
In another database, you do lots of reporting and aggregation. You’ve got large tables. Your queries frequently join them, do sorts, and use parallelism. All of these operations need memory, and the more data you’re aggregating and querying, the more memory each of these queries can need.
Before a query starts running, it figures out how much of this workspace memory it needs, and looks at what is available given other queries that are running. SQL Server has to balance the memory used for data pages, execution plans, and this workspace memory: it doesn’t want to let one query take over! If not enough memory is available, your query has to wait.
You’ve optimized your queries and indexes and made sure the memory estimates are realistic, but when lots of people run reports, your queries can’t even get started because of a memory crunch. Solving this problem by adding more memory isn’t cheating: it’s helping the SQL Server do what it’s designed to do.
3. Some Features Need Lots of Memory Periodically
You’re managing a data warehouse where you’re using table partitioning to implement a sliding window. This has helped your users a lot: you can add and remove large amounts of data with minimal locking and blocking.
But for query performance reasons, you’ve had to add one or two non-aligned indexes that are present during the day. To do partition switching, you have to disable these indexes to do your sliding window data load and archive each night. Creating the partitioned index can consume large amounts of memory.
Using Enterprise features can require investing in Enterprise levels of memory.
Adding Memory Isn’t Cheating
Memory is an important tool for performance in SQL Server: it isn’t just something that covers up bad code, it’s often a solution to many different types of problems. Memory isn’t the only tool you need to help your queries go fast in SQL Server, but it’s certainly NOT a tool you should ignore.
Microsoft just announced a new round of D-grade VMs that have 60% faster CPU and local SSD than can go up to 7,000 IOPS in a canned IOmeter test. Before jumping to conclusions or, even worse, picking a cloud provider, it’s best to look at these numbers critically.
The new CPU is being advertised as 60% faster than the previous generation of processors. Clearly this has got to be some next generation hardware, right? Maybe we’ll get access to the new Xeon v3 – it’s not that outlandish of an idea; Amazon Web Services (AWS) had Xeon v2s in their datacenters before the chips were generally available.
Glenn Berry, a consultant who digs into computers for fun, did some initial testing with these new Azure instance types. In his investigations, he saw 2.2GHz E5-2660 chips. These aren’t even the slower end of the new generation of Intel Xeon v2 chips – they’re the previous generation of CPU… from 2012. Azure trades raw power for power efficiency.
If these not-so-fast CPUs are 60% faster, what are your current Azure VMs and SQL Database instances running on? Anecdotal evidence indicates that the current generation of A and P series VMs are running on older AMD Opteron hardware. Older AWS hardware is in the same boat, but it’s slowly being phased out.
Microsoft are reporting performance of up to 7000 IOPS per local Azure SSD but persistent storage is still rotational. During the D Series SSD VMs interview a screenshot of iometer at 7,000 IOPS is shown, but no additional information is provided. Iometer tests typically use a 4k read/write block size for tests, which is a great size for random file access. It’s not awesome for SQL Server, but we can divide that by 16 to get a representative SQL Server number…
437.5 64KB IOPS.
Or so the Azure Product Manager says in the original interview. I don’t believe what I hear, and you shouldn’t either, so I fired up an Azure D14 VM to see for myself. What I saw was pleasantly surprising:
If we dig into the IOPS provided by Crystal Disk Mark, we see a decent looking picture unfold:
----------------------------------------------------------------------- CrystalDiskMark 3.0.3 x64 (C) 2007-2013 hiyohiyo Crystal Dew World : http://crystalmark.info/ ----------------------------------------------------------------------- * MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]
Sequential Read : 705.103 MB/s Sequential Write : 394.053 MB/s Random Read 512KB : 528.562 MB/s Random Write 512KB : 398.193 MB/s Random Read 4KB (QD=1) : 16.156 MB/s [ 3944.4 IOPS] Random Write 4KB (QD=1) : 26.506 MB/s [ 6471.1 IOPS] Random Read 4KB (QD=32) : 151.645 MB/s [ 37022.8 IOPS] Random Write 4KB (QD=32) : 167.086 MB/s [ 40792.5 IOPS] Test : 4000 MB [D: 2.0% (16.2/800.0 GB)] (x5) Date : 2014/09/23 0:24:10 OS : Windows Server 2012 R2 Datacenter (Full installation) [6.3 Build 9600] (x64)
What’s it really mean? It means that the 7,000 IOPS number reported was probably for 4KB random writes. It’s hardly representative of SQL Server workloads, but we also can see what kind of numbers the drives will pull under significant load.
Comparing AWS and Azure Performance
AWS offers an instance called the r3.4xlarge. It comes with 16 cores and 122GB of memory. The AWS instance type is about the same as the D14 (16 cores and 112GB of memory). The D14 is $2.611 / hour. The AWS instance is $1.944 / hour.
All prices include Windows licensing.
So far, the Azure D-grade instance costs 70 cents more per hour for 4.8GHz fewer clock cycles and 10GB less memory. Not to mention the computational differences between the current generation of CPU and what Azure is running.
Surely the SSD must be amazing…
Not so fast. Literally.
Some AWS local SSDs benchmark have reported numbers as high 20,000 16KB IOPS for random write and 30,000 16KB IOPS for sequential read. Sure, the AWS instance only has a 320GB disk, but it’s capable of performing 5,000 64KB IOPS compared to the 440 IOPS (I rounded up to be generous) that Azure supplies.
In my testing, the AWS local SSD beat out the Azure SSD on random I/O by a reasonable margin:
How about those IOPS?
----------------------------------------------------------------------- CrystalDiskMark 3.0.3 x64 (C) 2007-2013 hiyohiyo Crystal Dew World : http://crystalmark.info/ ----------------------------------------------------------------------- * MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s] Sequential Read : 404.856 MB/s Sequential Write : 350.255 MB/s Random Read 512KB : 348.770 MB/s Random Write 512KB : 349.176 MB/s Random Read 4KB (QD=1) : 21.337 MB/s [ 5209.3 IOPS] Random Write 4KB (QD=1) : 38.448 MB/s [ 9386.7 IOPS] Random Read 4KB (QD=32) : 261.320 MB/s [ 63798.8 IOPS] Random Write 4KB (QD=32) : 237.201 MB/s [ 57910.4 IOPS] Test : 4000 MB [Z: 0.0% (0.1/300.0 GB)] (x5) Date : 2014/09/23 1:05:22 OS : Windows Server 2012 R2 Server Standard (full installation) [6.3 Build 9600] (x64)
So… First – Azure offers really good local SSD performance if you decide to purchase the entire instance. Using a D14 instance type is a reasonable expectation for customers deploying SQL Server – SQL Server is a power hungry monster and it deserves to be fed.
Despite their truth, the Azure numbers aren’t all they’re cracked up to be. Here’s how it breaks down:
Cost: 34% more expensive
Sequential Reads: 74% faster
Sequential Writes: 12.5% faster
Random Reads: 42% slower/fewer IOPS
Random Writes: 30% slower/fewer IOPS
Azure has a history of mediocre performance, but it’s well-documented mediocre performance. Azure persistent storage currently maxes out at 500 no-unit-given IOPS per disk (compared to AWS’s 4,000 256KB IOPS for EBS volumes), but these limits are well-documented.
The Bottom Line
Not all clouds are created equal and 60% more doesn’t mean that it’s any better than it was before. It’s up to you, dear reader, to determine what 60% faster means and how that applies to your environment. For companies dipping their toes in the cloud waters, be very wary with the new improved Azure performance. You may find that you’re deploying far more VMs than you thought, just to handle the same workload.
Want to see our sessions in Seattle? Add all of our calendar events to our schedule:
Or if you’d rather add individual events:
Every single thing you do with a virtual SQL Server starts with one very important concern: how are you going to license it?
The 3 Common Ways to License Virtual SQL Servers
Developer Edition, by the guest – Developer Edition has all the power of Enterprise Edition, but it’s way cheaper (like in the $50/server range) and you’re not allowed to use it for production purposes. Because the licensing is so cheap, just mix these VMs in with the rest of your guests on a shared pool of hosts. Developer Edition licensing is handled differently than other SQL Server editions, typically bundled with your developers’ MSDN subscriptions.
Enterprise Edition, by the host – When you have 5 or more guests, it usually makes sense to buy a dedicated pair of host servers in an active/passive configuration. For example:
- Dell R720 Primary – 2 sockets, 4 cores each, 768GB memory – ~$25k USD
- Dell R720 Secondary – same hardware config – ~$25k USD
- 8 physical cores of SQL Server Enterprise Edition licensing and Software Assurance – ~$55k USD (you only have to pay licensing on the physical cores regardless of whether you enable hyperthreading)
- Hypervisor, Windows, management software licensing, shared storage – left as an exercise for the reader
You can configure your hypervisor (VMware, Hyper-V, Xen) to use one of the hosts as the primary, and when it fails, automatically boot up the guests on the secondary R720. This way, you only have to license one of the two hosts, and you can run an unlimited* number of SQL Servers on this host. (* – Not really.) Even better, even though they may be small VMs, you can still use Enterprise Edition on these guests and get access to cool EE-only features like online index rebuilds.
As your SQL Server needs grow, you can license the Secondary and balance the VM load across both, and even start to add additional licensed hosts. The key, though, is to avoid running any non-SQL-Server loads here because these are your utmost expensive host cores.
Standard Edition, by the guest – When you have 4 or fewer SQL Server instances, and they don’t need enough hardware horsepower to merit a dedicated host pool, then you’re usually best off licensing each guest with Standard Edition. This way, you can intermingle them on the same hardware that the rest of your virtual guests use.
At around $2k USD per core, it’s roughly 1/4 the price of Enterprise Edition, but you have to keep an eye on the number of virtual cores you’re using. You may read training material that says “just add vCPUs when you have performance pressure,” but this is expensive advice when you’re licensing each guest by the core. As you approach 16 vCPUs of Standard Edition licensing, you need to build a plan to migrate over to the host-based Enterprise Edition licensing approach. 28 vCPUs of Standard costs about the same as 8 vCPUs of Enterprise, but only the latter lets you run an unlimited number of guests, use over 64GB of memory on SQL 2012 and prior, do online index rebuilds, etc.
How Licensing Affects Your Administration
If you need SSRS, SSIS, or SSAS, and you’re licensing hosts with Enterprise Edition, you simply deploy these other services on separate virtual servers. You never make these other services fight with the core SQL Server engine for the same memory. However, if you’re on Standard, you saved money on licensing, but you spend extra performance troubleshooting time. You’ll need to install these services on the same VM as the engine to keep costs down, but you’ll need to understand how to triage performance.
When you design a high availability solution, the edition determines your choices as well. If you’re on Standard, and you need automatic failover for OS & SQL problems, you’ve backed yourself into a corner of either failover clustered instances or synchronous database mirroring. These solutions add complexity that shops without a DBA often can’t afford, so they end up going without OS/SQL protection and sticking with the simpler hypervisor-based failovers. On the other hand, once you’ve decided to license the hosts with Enterprise Edition, you can take advantage of AlwaysOn Availability Groups and asynchronous database mirroring without extra licensing costs – even for your smallest VMs.
Knowing your licensing means faster decision-making on architecture, and that’s why you want to start your virtualization projects here. Licensing is usually the most expensive and most restrictive part of the project – get it right first. To learn more about licensing SQL Server, check out the 2014 Licensing Guide PDF.
Until now, SQL Server training usually meant registering for a course, showing up, hearing a lecture from a podium (often from someone who’s only done demos on their laptop, not production systems), working through a few workshops, and then going home with a bunch of ideas. It was like trying to ride a rollercoaster by jumping on as it came speeding toward the platform, having a great time, and then jumping off at high speed again. No graceful transitions, just a shocking experience.
We wanted to do better.
We wanted to ease students into the experience, get them more involved with the material ahead of time, connect the dots between the demos and their real environment, and empower them to help each other with their studies. We wanted to mix up the training so that it was less about theory, and more about their real world environment. Finally, as students left the classroom, we wanted to ease them back into the real world in a way that empowered them to succeed.
We couldn’t accomplish all of those goals immediately – doing this kind of practical, realistic, and comprehensive training is really hard work, so we’ve been gradually experimenting with more and more tactics this year. Here’s what we’ve come up with so far.
What It’s Like to Attend Our Training
We start mixing things up as soon as someone registers for one of our courses. For example, our How to Be a Senior DBA students were assigned a series of private pre-requisite training videos as soon as they registered for the course. They get started learning about RPO, RTO, and SQL Server’s options to meet their availability goals. (Behind the scenes, it took us years of work to get our e-commerce setup and training videos to this point, but the integration is definitely paying off now.)
On the first day of the in-person class, we started by discussing a couple of real-life case studies – then turned their own environment into a real case study. Each attendee used worksheets to describe their current production environment’s requirements to the person sitting next to them, and that person acted as a consultant. The team worked together to sketch out the right long-term solution for a company’s needs, and then they switched roles. Each student was able to sit on both sides of the issue – describing a problem, and building a solution for someone else.
In just one hour, this combination of pre-learning plus collaborative exercises accomplished three goals: it reinforced the student’s knowledge, helped them get a second opinion about their systems, and built a relationship with other people in the room. The relationship-building paid off throughout the course as the students carried on lively discussions at every break, over every lunch, and hanging around the flipcharts to talk shop. I wish I could say this was part of our giant master plan, but it was just a happy discovery that we made back in San Diego, and we’ve been using it ever since.
Another discovery this year was that students learned better when we assigned them script homework before the course. For example, before the SQL Server Performance Troubleshooting class, students were assigned scripts to run on their production servers and copy the results into Excel.
In Chicago, one of the students came up to me and said:
“I’m so glad you gave us the wait stats homework before class. I’d run the queries on several of my slow production servers, and I didn’t understand the results at the time, but now it all makes perfect sense. I understand why the data warehouse has PAGEIOLATCH and the web app has CXPACKET, and I know exactly what to do next.”
As we’d been covering wait stats, she’d been comparing her results with what we were discussing, and she was able to make a simple set of steps for her next maintenance window – all while sitting in class. Some of our students even used our lab scripts on their live production servers, fixing issues as we discussed them. (That might be taking the concept a little too far!)
Our Next Round of Training Experiments: Seattle
In our 2-day Seattle class, Make SQL Server Apps Faster, we’re running a couple more experiments.
We’re starting off with a day of lectures on performance tuning. We cover how to think like SQL Server, using wait stats to know why SQL Server is slow, tuning with the plan cache, beating anti-patterns, building the best indexes, and a challenge for attendees.
We weren’t content with that. People keep asking for more hands on sessions like “Watch Brent Tune Queries.” You really want to see our thought processes at work. We’ve done just that. Attendees think through the problem and sketch out their answers. Then they spend the day watching us solve it – they watch me doing query tuning, Kendra doing index tuning, and Jeremiah working through execution plans. At the end of the day, we judge their answers together.
We’re also offering a package deal – some attendees have said they want to watch recordings of the sessions later to reinforce what they’ve learned. (They already get the demo scripts, but sometimes it’s better to watch us doing it.) For an extra $200, the attendees get our 7-hour Developer’s Guide to SQL Server Performance videos that cover about half of what we’re teaching in-person, and we’ve extended similar deals to our 2014 training class attendees too.
For 2015, our new week-long courses represent another new experiment. This year’s attendees kept telling us, “As long as I’m out of the office, I might as well be out for the whole week.” Sounds good! So we’re taking more time to dive deeper into more topics.
We’re excited about how we’re doing SQL Server training differently than anybody else on the market, and we can’t wait to share it with you.
Got a high performance SQL Server 2012 instance? Check out the brand new KB article for recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads.
Make sure you expand the following plus sign – this is where all the good stuff is stored:
After expanding it, you’ll get a huge list of categorized advice for trace flags, cumulative updates, MAXDOP settings, and much more:
I haven’t read through the details on this yet, but I’d note that this advice is focused on high performance workloads. Don’t go enabling trace flags without understanding what they do and their side effects.
I continue to be impressed by the documentation Microsoft is putting out. Books Online keeps getting better and better, and this is the coolest KB article I’ve seen in a long time. Way to go, Microsoft!
Let’s play the quiz! We’re using the honor system: be honest with yourself.
Five Simple Questions
- When I say the first name “Hilary”, you immediately know his last name is ____.
- Have you successfully set up replication so that you can add and remove articles without running a snapshot for everything, and done this before? (Yes or No). _____
- Do you have monitoring set up that notifies you immediately if a subscriber is behind? (Yes or No) ____
- How many books on SQL Server replication do you own (which you have read)? _____
- Do you know exactly how long it would take to re-initialize all your publications? (Yes or No) ____
Score Your Answers
- One point if you thought of this man.
- One point for Yes
- One point for Yes
- One point if the answer is 1 or higher.
- One point if your answer is “I know exactly how long, and I wish I didn’t know.”
Here’s how to interpret your score:
- 1-3 points: You’re just beginning, but you cared enough to take the quiz. You can get there.
- 4 points: You’re good at replication, AND you’re honest
- 5 points: You’re an expert
Ready to learn more about replication? We’ve written a lot about it.