Selecting, testing, and benchmarking storage for SQL Server.
43 associated posts42 primary posts
When you've got questions about architecture, development, or best practices, post 'em at https://pollgab.com/room/brento, and upvote the ones you'd like to see me cover. It's like free bite-sized consulting.
https://www.youtube.com/watch?v=lCbPSYmCTHg
I've already explained that no, it doesn't make your database server more reliable - and in fact, it's the exact opposite. But what about performance? The answer is going to depend on your hardware and workload, but let's work through an example. I'll take the first lab workload from the Mastering Server Tuning class and…
I am waaaay overdue for a haircut, but instead of being a responsible adult, I stopped to take your questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=e2ZQisZJp5M
Wow, y'all have been posting some great questions at https://pollgab.com/room/brento lately!
https://youtu.be/eIjMqm0b02s
You ask questions at https://pollgab.com/room/brento and I answer 'em from the beach in Cabo San Lucas:
https://www.youtube.com/watch?v=EQe6IWeTCTo
You've heard these terms thrown around about storage, and you're not sure what they mean, and how they relate to each other. Let's make it simpler by using an analogy. Say you want to measure a shipping company. You're trying to decide whether to use DHL, FedEx, UPS, or your local postal service. You could…
Since the dawn of time, database vendors have called out to developers, "Store everything inside the database. You know you want to. Here, we'll make it easy for you by adding data types like binary and features like filestream."
Ah, but this siren song leads you to your eventual doom.
I bet you wanna know whether your storage is hot or not, and there's no quicker, easier way to get a rough idea of your storage's capabilities than to fire up CrystalDiskMark. This month, CrystalDiskMark released an all-new version 7, and it makes for even better testing.
Start by downloading it and installing it - sadly, there's no zip file version anymore that you can stick on a network share and run remotely.
It sounds really simple:
Capture a bunch of production queries
Replay those same queries over in a load test environment
So you just built a brand new SQL Server, and before you go live, you wanna check to see if it's going to perform as well as your existing server.
The easy way: test your maintenance jobs
It's as easy as 1, 2, 3:
This week, Brent and Tara discuss separating data and logs on separate drives, statistics time, scalar UDFs, licensing, encryption, gathering SQL error logs and agent job history, replication, upcoming training courses from Brent Ozar Unlimited, and what DBA topics they struggle with the most.
Here’s the video on YouTube:
The old advice went something like this: "Put your data and log files on separate drives and your server will be more reliable. If you lose the data drive, you can still do a tail-of-the-log backup, and you won't lose any data."
It's advice. But is it actually good advice?
Let's start with definitions
Many SANs have caching built in. What kind of cache is important, because if you're dealing with non-SSD storage underneath, you could be waiting for a really long time for it to respond.
Let's start with some definitions of the most popular caching mechanisms available for SANs. I'm not going to say 'only', because some vendor out there might have some proprietary stuff going on that I haven't heard of.
True story A long time ago, I had to actually do stuff to databases. One thing I had to do was move data files around. Maybe some knucklehead had put system databases on the C: drive, or a LUN was filling up, or we got a new LUN. You know, whatever. Natural curiosity oft leads…
Diskspeed, or diskspd.exe, is Microsoft’s replacement for SQLIO. While I’m not going to replace our SQLIO tutorial, you can use the information in here to replicate the same type of tests on your systems to get the information you need. During the Dell DBA Days, Doug and I used diskspd as one of our techniques…
We've all had to move a database. For databases above a small size, this is a painful process. While there are a number of ways that you can move a database without downtime, they all involve a lot of extra work. What if there were something easier? What's the Real Problem? For most people, moving…
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:
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. CPU Speeds The new CPU is being…
SQL Server 2014 contains some interesting new features. Although SQL Server Standard Edition is limited to 128GB of memory, teams deploying on Standard Edition have an option to fit more of their working set in low latency storage - SQL Server Buffer Pool Extensions. How SQL Server Normally Deals With Data During SQL Server’s normal…
The other day I was running a script I'd written to expand out some tables in the AdventureWorks sample database for some testing. The script was taking a long time, and at first I thought I was just spoiled because I usually run my tests on my laptop, whose storage is backed by SSDs. In this case I was running my test against a SQL Server instance up in AWS.
