Sizing hardware and planning for growth, resources, and operational scale.
41 associated posts41 primary posts
You were minding your own business, and all of a sudden it happened.
You glanced at file sizes one day, and your eyes got big. The numbers got a little large while you weren't looking. This is a great time to stop and think about a few changes to the way you're managing this database.
At least once a month, I get this question from a client:
We have big data, and we want to save money. We want to move the older data down to some kind of archives that will cost us less.
If you've been in Azure or Amazon for a few years, you're probably on old, slow hardware.
In the last 3 weeks, I've had two clients who'd both been early cloud adopters. When they'd migrated to the cloud, they both used Azure Ev3 VMs - at the time, a good choice for SQL Server due to its relatively high amount of memory. When the Ev3 VM types were announced in 2017, they were hosted on Intel Broadwell and Haswell processors with 2.3-2.4GHz processing speed.
Today's batch of questions from https://pollgab.com/room/brento requires some really long answers, and some are right to the point.
https://youtu.be/O2mGmlq69B8
I'm running into something that I'm having a hard time believing.
A client was hitting CPU issues during load testing, and they swore all things were equal between their SQL Server 2016 and 2019 environments. The 2019 box was having CPU pressure issues that didn't show up on the 2016 box. I've played this game before, and every time, the root cause has been different configurations between the two servers.
You've heard that shrinking a database is bad because it introduces both external and internal fragmentation, it causes blocking, it causes transaction log growth while it runs, and it's slow and single-threaded. You understand that if it's just a matter of 10-20-30% of a database, and the database is only 100-200GB, you might as well just leave the space there, because you're gonna end up using it anyway.
When the business decides it's time to give up fixing the old server because it's not fast enough or reliable enough, stop. The worst thing you can do is jump directly to the hardware vendor's site, check a few boxes, and order a new shiny server that ends up with exactly the same problems as the last server.
In SQL ConstantCare®, we're adding a notification for when your data size grows >10% in a single week. When this happens, you want to stop for a second to ask yourself a few questions: Did someone just create a backup table? Folks often do a SELECT INTO to back up an important table right before…
When you build a VM for SQL Server in Amazon EC2, Azure VMs, or Google Compute Engine, it's all too easy to overlook the CPU speeds that you're getting. Most of the time, it doesn't really matter because SQL Server isn't usually CPU-bottlenecked: your queries are doing table scans with 16GB RAM, starving for IO throughput.
When you first hear about it, SQL Server's In-Memory OLTP sounds like a new feature that's hard to implement, requires a lot of schema changes, and takes planning and downtime.
I'm here to tell you it's simply not true, and you can do it in just 3 steps. Here's how:
When I look at a SQL Server, one of the first things I check is, "How much memory does this thing have relative to the amount of data we're hosting on here?" I've long used some seat-of-the-pants numbers, but armed with data from SQL ConstantCare® users who opted into public data sharing, let's do a little deeper analysis.
Sounds impossible, right? The saying goes that you can never be too rich or too thin or have too much memory.
However, there is one good indication that your SQL Server is probably overprovisioned, and to explain it, I need to cover 3 metrics.
Clientele
Most of out customers are on slightly (ahem) older (AHEM) hardware, and they have big questions:
How do we know what kind of hardware we need?
How do we load test it?
How do we know what to change to make it better?
Funny thing
The human body is pretty good at adaptation. When you get sick or injured, your body responds and (most of the time) heals itself up. When you stress your body in a certain way, over time, your body will feel less stressed by the same level of that activity. If you do 10 push-ups every day, after a week you'll barely notice you're doing them.
Most of you are going to hate this And TL;DR, there's a script at the end of the post. But like The Monster At The End Of This Book, it's worth it not to skip the middle. There are about a billion but-what-ifs that could come into play. I can't possibly answer all of those…
Sometimes we pile just a few too many duties onto our servers. They start to become a clown car - the classic joke where dozens of clowns come pouring out of an impossibly small car. (The physics behind it are actually really interesting.)
So how can you tell if your SQL Server has crossed the line into clown car territory? It's easy:
You may have heard the term "commodity hardware" thrown around when describing solutions like Redis, Elasticsearch, or kCura's new Data Grid.
Commodity hardware refers to cheap, standardized servers that are easy to buy off the shelf from any vendor. Here's a typical example of a 2u, 2CPU commodity hardware server:
Our first two concerns in this series were virtualization licensing and recoverability. It's not enough to have answers for these questions today, though - we have to think about how long the server will be in place.
The server's expiration date isn't dictated by IT. If it was up to us, we'd never run an old, unsupported version of anything - but sometimes the business is quite content doing just that. So how do we plan for this from the start?
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…
kCura Relativity is a software product for law firms to find interesting stuff to help their case. To get you up to speed on what it does, here's some of the posts I've written about Relativity in the past:
Performance Tuning kCura Relativity - explains what the product is for and how DBAs can help make it faster
