Episodes / Episode 8 of 9
SQL Server Life Cycle, Documentation & Tuning
Once the fires are out, things grow, get old, and change. Standardization, automation, server life cycles, documentation, cross training, and predictive tuning for storage, CPU and RAM.
Watch on YouTubeFull course playlistSubscribe
In this episode
- Two magic words: standardization and automation (PowerShell)
- Server life cycles, why you can’t do an in-place upgrade, CNAMEs and subnets
- Documentation that’s live, and cross training so nobody hoards knowledge
- Tuning: predict storage, CPU and RAM runway, and what to do about each
- Page life expectancy scaled to your SQL Server memory
Chapters
Transcript
Read the full transcript
Things are going to grow
Now that all the fires are out, you might be thinking, sweet, nothing left to do, right? Wrong! Things are going to grow. Things are going to get old. Things are going to change. Now we need to figure out how to keep them going.
Standardization and automation
As you become responsible for more and more SQL servers, you will be surprised to find out that you will not get more and more DBAs.
Magic word number one is “Standardization.” Make everything as much the same as you can. Every server, same naming scheme. Every service account, same naming scheme. Every server has the same drive layouts. The more one offs you have, the harder your job will be.
Magic word Number two is “Automation.” PowerShell is your friend. Anything you have to repeat over and over again should be automated. Install SQL on new servers, automated. Clean up older backups that didn’t purge, automated. Install Windows and SQL updates, automated. Release source code, automated.
SQL and Windows Life Cycle
Remember back in Y2K, and you were like Windows 95 is so old. It was old, five years old. Some of you are still running Windows and SQL 2008. They’re old enough to vote. Stop it.
In a perfect world, a virtual server should only live 2 years, and a physical server should only live 5 years.
The world is not perfect. In the real world, I would give you a gold star and an A+ if you can get your virtual servers replaced every 4 years, and your physical servers every 10 years.
Microsoft will force your hand after long enough by stopping support of older software, meaning no more security updates. NO, YOU CAN’T DO AN IN PLACE UPGRADE. The server is old, it has rust in places you don’t see. You’ve learned a lot since that server was made, and the servers you make today are way better.
Build each server knowing that it’s going to be replaced. Create CNAMEs, and use those for all connection strings. There will almost always be a CNAME to a listener. 100% of your connection strings should be CNAMEs.
Try to hint to the network architects to have small sensible subnets for the SQL Servers. Maybe they share the subnet with the app servers. When you open up firewall rules, open them up subnet to subnet, that way you can just build the new servers in the same subnet, and like magic they will have all the firewall rules they need. This won’t work if you have giant subnets, or your subnets are shared with risky servers like web servers.
Documentation
You can’t standardize anything if you forgot, and have to reinvent a new way to do something every time you do it. Any process you do should be documented. You should use the document every time you do it, no you don’t have it memorized.
Use something live for documentation that will update all locations when a change is made, and will keep a history of changes made. Don’t use Word, or some Brad will keep a copy of them on their desktop, and will keep doing five year old processes that haven’t been correct for a long time. He will be like, “But I followed the document.”
You don’t need screenshots. Screenshots make it harder to make documentation. If things are harder, then you are less likely to do them. Screenshots are very hard to update when something changes. Even if you put text right under the screenshot that says “Hey Brad, screenshot is old, use these settings instead!!” Brad will still do whatever the screenshot says.
Write the documents for the next DBA. That way when Brad gets fired, you don’t have to spend so much time training the next guy.
Cross training
Don’t hoard knowledge. Make sure you and the other DBAs you’re working with are cross training each other. You don’t want there to be a process or a work effort that only you can do, or that you can’t do. This is very important if you ever want to take a vacation. It’s also very important for when your DBAs quit. DBAs that hold each other up will always outperform the DBA that hoards knowledge. Those knowledge hoard dudes always seem to get fired or laid off anyway. It’s not a good strategy.
Tuning
Tuning. Where to start? Figure out what SQL servers cause people to run around like their hair is on fire if they aren’t working well. They power apps that make a bunch of money or power apps that make lots of people scream when they stop working. Those two things again are:
Make a bunch of money. Make people scream.
Looking at your hair on fire apps, look back at data to determine three things: storage, CPU, and RAM.
Storage. Looking at mainly the drives that hold MDF and NDF files, determine how fast they are filling up. You will want to be able to say something smooth like, “At our current rate of growth, we can continue on our current drives for X months”.
Now figure out what database is taking up all the space. I use a free tool called WinDirStat. You will also figure out what databases are growing, it’s most likely the biggest one, but not always. The easiest way is to query the default trace. Figure out what tables are taking up all the space. There is a built in report in SQL Server Management Studio, SSMS, that does this for you.
After you have this information, explain it to people that make decisions about the budget. The options will be: To make the storage bigger, (you can always throw hardware at bad code). This will cost money, not much. You can write less data. You would be surprised how often we write data that will never be read again.
Compression. There are a few different compression methods SQL Server supports. The only one really worth attention is column store. If done properly it can reduce the space required to 10% of the original. Retention. Most developers have a lot of pressure to get something to market fast. That time to market pressure often causes a buildup of technical debt. A common piece of technical debt is apps going live with no retention. Most performance issues go away if you get rid of lots of data.
Retention options: One Record at a time. Partitioning. Partitioning will allow you to do partition switching, and truncating.
To look at CPU, you first need to figure out what flavor the server is. They come in two flavors OLTP (Online Transaction Processing), and OLAP (Online Analytical Processing). The way you tell the difference is people get angry at OLTP servers if they don’t return a query in seconds. People get angry at OLAP servers if they don’t return a query in minutes.
Seconds = OLTP. Example: My app times out if this stored procedure that gets a user’s full name takes more than 3 seconds. Minutes = OLAP. Example: I built a report that normally runs in 30 minutes, but today it’s taking 90 minutes. For OLTP servers, you want to look at average CPU. Every app is different, but ball park if the average CPU on an OLTP server is higher than 50%, then it’s having short spikes of 100% enough to make things timeout. You will want to be able to say something like, “At our rate of growth, we have enough compute to last X months before we are at 50%, and users start feeling timeouts.” For OLAP servers, high CPU typically isn’t a concern as long as the products it produces are still being produced within the time that is needed.
After you have this information, explain it to people that make decisions about the budget. The options will be: Add CPU cores (you can always throw hardware at bad code). Adding cores is very expensive. Compute in general is expensive. It also increases the number of the SQL server licenses, those are not cheap.
Do less on the SQL Box. If the SQL server is doing any heavy computing that can be done on an app server, it’s almost always significantly cheaper to use the CPUs in the app servers vs the SQL servers. Create new indexes. Go take Brent Ozar’s “Fundamentals of Index Tuning” class, or recommend developers to take it. It’s what I use.
Modify queries. Go take Brent Ozar’s “Fundamentals of Query Tuning” class, or recommend developers to take it.
RAM. I use page life expectancy. Plotted over time it can tell you if you’re under memory pressure. Every application is different, but I typically start worrying about memory pressure if PLE averages below 300 seconds for every 4 gigs of SQL Server memory on OLTP servers. For OLAP servers, the conversation is always different. Typically memory pressure doesn’t matter unless people want things to run faster on OLAP servers.
For OLTP servers, you will want to be able to say something like “We will start seeing memory pressure that will likely impact the performance of this application in X months.” What to do about it? You can always add more RAM, something something throw hardware at bad code.
