All day long, you’ve been watching and waiting, thinking you could resist a killer deal.
Coupon LastHoldout299 gets you $299 off any of our $299 training videos, including:
- How to Read SQL Server Execution Plans
- How to Tune Indexes and Speed Up SQL Server
- The Developer’s Guide to SQL Server Performance
- Virtualization, SANs, and Hardware for SQL Server
- And more!
Move fast – it’s only good for the first 5 buyers.
And with that, we’re done for the day! See you next year.
So you didn’t get in on the midnight doorbuster sale for 50-90% off our training videos, and you’re jealous of all the cool kids who got up early.
Coupon FunTrainingAtHome will take 40% off any of our online training videos including:
- How to Tune Indexes and Speed Up SQL Server
- The Developer’s Guide to SQL Server Performance
- SQL Server Reporting Services Basics
- Virtualization, SANs, and Hardware for SQL Server
- The DBA Job Interview Q&A Kit
- And more!
This discount is in place for the rest of the day, with no limit on buyers. Go check ‘em out!
So you didn’t get in on the midnight doorbuster sale for 50-90% off our in-person classes, and now you’re kicking yourself because your boss gave you permission to attend.
Coupon EasyPainRelief will take 30% off any of our 4-5 day in-person training classes. Now, instead of the first number in their price being a 3, it’s a more palatable 2. Probably even more palatable than that cranberry salad you’re warming up.
This discount is in place for the rest of the day, with no limit on buyers.
Good luck, and hope to see you in Chicago, Denver, or Portland!
You’re comfortable writing queries, but some of them are slow – and you need more ways to tune than just adding indexes. You’ve heard that you should read execution plans, but you don’t know where to start.
In this 5+ hour video class taught by Microsoft MVP Jeremiah Peschka, you’ll:
- Learn how to read SQL Server execution plans
- Understand how different query syntax changes plans
- Explore the plan cache to find problem queries
- Learn solutions to common query problems
You even get sample code to download, plus queries to check your own system and find the worst execution plans that need help.
You can watch it for 18 months on your desktop, laptop, iPad, or iPhone.
The first five people who use coupon code LearnToRead99 get the course for just $99. Go!
(Oh, and we may have activated a few more of those Doorbuster50 codes for 50% off, too.)
SQL Server’s Maximum Degree of Parallelism (MAXDOP) controls the number of processors that are used for the execution of a query in a parallel plan.
Pop quiz: what’s our favorite knowledge base article for recommendations and guidelines for MAXDOP?
That KB# is your coupon code good for $29 off our training classes or videos – even the $29 videos! Good luck – it’s only good for the first 10 buyers.
SQL Server 2012 introduced AlwaysOn Availability Groups, a way to achieve high availability, disaster recovery, and scale-out reads. SQL 2014 brought some improvements around higher uptime and more scale-out, and all signs point to continued improvements in the next version of SQL Server, too. (I love it when Microsoft brings out features like this and continues to invest in them over time.)
A lot of the emails I get start with, “I’d like you to help me implement AlwaysOn AGs,” but it’s funny – most of the projects don’t end up actually deploying AGs. There’s a few barriers to adoption, and even when you’ve built an Availability Group, management can be a little tricky. Don’t get me wrong – I love the feature – but it comes with some surprises.
Rather than me prejudicing you, I’ll just put it out there as a question:
How would you change AlwaysOn Availability Groups?
Leave your answer in the comments. (And yes, Microsoft is watching.) Bonus points if you link to your Connect request.
James Serra caught three new SQL Server 2014 Fast Track Data Warehouse Reference Architecture designs released by EMC, HP, and Lenovo. I love reading these because they show each vendor’s state-of-the-art storage infrastructure.
Two of them have remarkably similar goals – to hold a 28 TB data warehouse:
- From HP: HP DL380 G8 with local PCI Express solid state storage (PDF)
- From EMC: HP DL580 G7 with EMC VNX 5600 shared storage (PDF)
Here’s a simplified summary of their results:
A few things to take away here – first, and obviously, the HP storage wipes the floor with the EMC storage. It’s not clear from the limited test results if the EMC solution would have been more competitive had it used modern CPUs. The EMC one was built with an HP DL580, a 4-socket server, using older CPUs, and it left two of the CPU sockets empty. That’s quite an odd choice for a benchmark test.
The EMC solution takes up dramatically more rack space than the simple HP 2-u server solution, and involves dramatically more management complexity.
However, if you want automatic failover with minimal downtime and no data loss, local solid state storage probably isn’t going to cut it. It’d be relatively easy to add high availability in the form of Windows failover clustering to the EMC solution, but complex to build reliability into HP’s. (It’d require AlwaysOn Availability Groups or database mirroring, both of which would impact the workload speeds seen here.)
Cost is a tougher question – your mileage may vary given pricing discounts on gear like this, but note that HP’s solution uses four of these $28k USD cards. The solid state storage alone is $100k, which sounds like a lot, but remember that we’re talking about 24 cores of SQL Server licensing anyway – in the neighborhood of $165k just for the software.
Solid state changes the game for everything in databases, and you don’t have to buy the ultra-expensive cards, either. I’d love to see a reference architecture built with Intel’s new PCI Express drives, but since Intel hasn’t been involved in Microsoft Fast Track Data Warehouse Reference Architectures, that’s left as an exercise for the reader.
If you don’t license all of your SQL Server’s CPU cores, you need to pay particular attention to your server hardware.
Say you’re using an HP DL580 with four CPU sockets, each of which has a Xeon processor plugged in with 10 cores. That’s a total of 40 real cores, or 80 logical processors if you’ve enabled hyperthreading.
In SQL Server’s log at startup, if you’re using an upgraded core-based license capped out at 20 cores, you’ll see a message like this:
SQL Server detected 4 sockets with 10 cores per socket and 20 logical processors per socket, 80 total logical processors; using 40 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
To understand the performance implications, it helps to look at a diagram of the server’s system board:
The 4-3-2-1 across the top are the four Xeon processors, each of which has 10 cores (20 with hyperthreading.)
The 8-7-6-5-4-3-2-1 across the bottom are the memory banks. Memory is not shared across all CPUs – each CPU has its own local banks of memory, a critical concept in NUMA – non-uniform memory access. To learn more about NUMA, check out my MCM video on the SQLOS.
Here’s what that concept means in practice for our server – say we’ve got 1TB of total memory:
Each processor gets its own local memory.
And when SQL Server says, “Hey, you’re only licensed for half of these cores, so I’m only going to turn on half of them,” it will only use the memory in half of the NUMA nodes. In this case, we’re licensed for 40 logical processors, so we have to dig further into DMVs and logs to figure out which NUMA nodes got turned on, and whether or not our memory is even available to us. See, if memory is hooked up to a NUMA node that isn’t enabled, that memory won’t be accessible to SQL Server.
Similar problems exist when you run Standard Edition on a server with more than 4 CPU sockets. Sounds crazy – most of us don’t use servers that large – but this situation pops up a lot when VMs are misconfigured with 8 virtual CPUs, each of which is presented to the guest as its own CPU socket.
How to Fix It
One option is to license all of your sockets and cores. (Hey, listen, you’re the one who wanted a solution.) Don’t buy hardware with more cores than you actually need because CPUs with more cores typically have slower clock speeds (often by 1/3 or 1/2) compared to CPUs with less cores.
Another option is to use affinity masking to tell SQL Server, “These are the specific cores I want you to use,” and manually load balance the work across all NUMA nodes.
8:25AM Pacific: Goooood morning Seattle! It’s time for day 2 of the PASS Summit, and this morning we’ve got a technical keynote lined up. I’m a much bigger fan of these than the day 1 marketing keynotes.
Dr. Rimma Nehme from the Microsoft Gray Systems Lab is talking Cloud Databases 101. Here’s the abstract:
Cloud computing has emerged as an extremely successful paradigm for deploying applications. Scalability, elasticity, pay-per-use pricing, and economies of scale are some of the major reasons for the successful and widespread adoption of cloud. A cloud database is a type of database service that is built, deployed and delivered through a cloud platform. In this talk, I’ll give rapid introduction to cloud databases: what they are, where they came from, how they are implemented, and give a rough taxonomy. We’ll look at some of the specific examples of the cloud databases running out there in the wild and the latest state-of-the-art cloud database research.
I’ll be liveblogging it this morning, taking notes. I’m not going to transcribe it line-by-line because these technical keynotes are typically much more … technical … and you can watch the session live on PASS TV anyway.
Refresh the page every few minutes to see the updated notes at the bottom of this page.
8:31AM – The SQLPASS PASSion Award goes to Andrey Korshikov, who’s done all kinds of Russian event organizations in the last few years. Sounds really cool.
8:35AM – The next PASS Summit will be back in Seattle again on Oct 27-30th, 2015.
8:36AM – Dr. Rimma Nehme taking the stage.
8:42AM – The clicker isn’t working. Who cares. Confession: before the keynote, she said to me, “You’d better go easy on me.” I told her she has nothing to fear because I love technical keynotes. Yes, dear readers, I’m a softie for keynotes that aren’t trying to sell me things, only teach me things. That’s why we try to do the presentations we do as a company, too. Help by teaching.
8:44AM – Cloud computing characteristics: on-demand self-service, location transparent resource pooling, ubiquitous network access, rapid elasticity, and measured service with pay per use. (See, this is how you kick ass with a technical keynote – that one slide alone teaches everyone in the room something, and it’s accessible at all levels. That slide is awesome.)
8:46AM – Quick & easy deployment means that you can be agile with shorter development cycles and no need to wait for provisioning. Technically, you can do this on-premise in large corporations, but the beauty of the cloud is that it brings large-company IT scale without the red tape and red P&L statements.
8:48AM – “Storage costs are rapidly approaching zero.” For example, Amazon Prime’s free unlimited photo storage is a great example of this – companies can make enough off the storage of your data that they don’t mind eating the storage costs. Think of it as banks that can sit on your money and make money with it – cloud utilities can sit on your data and use it for interesting revenue purposes. While that makes a lot of the geeks in the room feel a little dirty, the public seems to feel it’s a fair tradeoff.
8:50AM – Showing Microsoft’s container-based data center in Chicago.
8:53AM – Dr. Nehme is explaining how container-based data centers improve power efficiency by letting servers run hotter, plus cool them down with more ecological methods like evaporative cooling. Again, this is how you do a killer database keynote – show concepts that are very interesting to your audience regardless of their expertise. Most of the audience has something in common, but not enough expertise on that topic. Play to that, and you can keep them entertained and educated.
8:57AM – Explaining PaaS, IaaS, SaaS etc by breaking them down into infrastructure, platform, software, and applications (Office). I’d love to see a pop quiz to know how many people know the differences between those. That’s our job as data professionals – we do need to know these – and I think a lot of us don’t. (I’m talking about you, dear reader.)
9:00AM – Explaining them with Pizza as a Service as an example.
9:08AM – Dr. Nehme’s focusing on virtualizing SQL Server in the cloud with shared resources. My notes will be less frequent here because I think the topic is covered online pretty well in a lot of resources (not to mention her talk) and I’m not going to add a lot of value here. Nothing against the talk – this actually gets to the heart of what I mentioned earlier, that so many folks need to understand more about services.
9:16AM – “Container-based hardware is 99.9% reliable, but we need 99.99% software.” That means you have to expect whole containers and data centers to fail, and assume you’ll need redundant data centers with the same services and databases. It’s thinking about reliability at a much higher level – for example, you might not bother with redundant power supplies or fans in a server. You can save a lot on power and cooling for the whole data center.
9:18AM – To achieve reliability in this mixed scenario (99.9 vs 99.99), Azure DBs have a primary database, plus three replicas. (It’s not explicitly stated on the slide, but you would assume there would be one or two in the same data center, plus one or two in other data centers or regions, based on how much you want to pay for geo-redundancy.
9:20AM – Each SQL DB node has one database, and multiple tenants are in that same database. They’re just logically separated – they all share the same data file and log file, but that’s not visible to you as a consumer.
9:24AM – “The number of DBAs is significantly smaller than the demand.” This is totally true – all of us are overworked, and there’s plenty more DBA jobs. The cloud (just like virtualization) is designed to offload work from you.
9:26AM – “Do the cost/benefit analysis.” This is one of the tough things for DBAs – most of us aren’t able to precisely identify the costs with on-premise databases. The cloud makes that way easier.
9:27AM – Dr. Nehme is wrapping up, and thanking Dr. DeWitt, who just took the stage. Great job!
We had so much fun with this last year that we’re doing it again. Starting at midnight Eastern (11PM Central,
6AM UTC 5AM UTC) on Friday, November 28th, we’re going to offer a day of special deals on our training videos and our in-person classes:
Coupon Doorbuster90 will take 90% off the cost of any of our 4-5 day in-person training classes. Instead of $3,195-$3,995, you’ll be paying just $319.50-$399.50. This sale will be good for only the first ten people – you’ll need to set an alarm and move fast to get a deal this good.
Coupon Doorbuster50 will take 50% off everything – our videos and training classes – for the first fifty buyers, or until 6AM Eastern, whichever comes first.
We’ll be posting more deals throughout the day, but we wanted to give you the heads-up on this so you could start making your plans and convincing the boss. During the day, you’ll want to be following @BrentOzarULTD or watching our Facebook page to know when the deals come out – they’ll be first come first serve, announced every 4 hours.
Good luck, and we’ll see you when the doors come busting open!