I’m writing this post so I can reference it later. A lot of discussions I have with folks out there need this vital context and in the spirit of transparency, I’d like to make it perfectly clear that I work for a storage solutions vendor: Pure Storage.
Month: May 2020
Earlier on I was doing some CHECKDB-related work in our old SQL Server lab and I realized that the CHECKDB process was not really pushing the array hard. I then had one of those synapses that have seemingly become less frequent as I age — I remembered that there’s a way of making DBCC CHECKDB go much faster than it typically does, at the expense of running less in-depth checks. It’s called the PHYSICAL_ONLY option. And boy did it make a difference versus running the whole thing!
I also remembered that there are some trace flags documented here to improve performance of DBCC CHECKDB with PHYSICAL_ONLY, and Microsoft also indicates that you may see some gains on full CHECKDB if you use them (Narrator voice: I didn’t). So I tested them (both TFs enabled or disabled together, not individually – others may have done this elsewhere) and I noticed a small gain against this test ~1.7TB database.
Run times below for your delight.
|Scenario||Time to Complete|
|PHYSICAL_ONLY, No Trace Flags||54:03|
|PHYSICAL_ONLY, Trace Flags Enabled||51:24|
|Full, Trace Flags Enabled||1:27:29|
|Full, Trace Flags Disabled||1:27:24|
You can also note in the graph below the bandwidth utilization on the array. It’s a really old //m20 and the SQL Server box is connected to it using 2x 8Gbps Fibre Channel connections, so in your case YMMV when it comes to throughput.
Word of caution, though. This will make DBCC CHECKDB run faster, but it will also hit your array harder. Make sure you have the necessary performance capacity to do this without impacting concurrent workloads. A great way of doing this is using Pure1’s Workload Planner.
Until next time,
Well, it’s been years since I’ve participated in one of these, but never too late I guess 🙂
Several weeks into the COVID-19 pandemic I went through a bit of a low personally. I was struggling to concentrate, feeling tired and generally not resting well. During that period I saw my 44th birthday approach and thought it would be a good thing for me to try to make my work at home environment better. Then I guess the midlife crisis kicked in, because I spent way more money than I should have on this thing:
OFR — the Orange Folding Rig, is a ridiculously fast “personal” computer. An AMD Ryzen 3900X CPU with 12 physical cores (yes, there are double the number of logical cores), 128GB of DDR4 RAM at 3600Mhz, an m.2 SSD that screams, and an NVIDIA GeForce RTX 2080 SUPER GPU. The motherboard is an ASRock X570 Creator , and the power supply is a Seasonic PRIME PX-1000.
This thing is insane. I run VMs, Spotify, all of my work-related applications – including video recording apps like Camtasia – at the same time that the rig runs it main intended purpose: The [email protected] software. And I run it at full throttle. OFR doesn’t even break a sweat.
I gotta admit that Glenn had a lot to do with my decision to build the rig and contribute to the FAH project. Thanks, my friend. It’s an honor and a privilege to be able to help under the umbrella of team #SQLFamily.
Stay tuned though, friends. I will have news on this front very soon. Very, very heartwarming news.
~1.8TB SQL Server database sitting on a 10TB virtual disk on a 64TB VMFS datastore cloned live, without stopping I/O on the source database at all. In 49 seconds. The power of Pure Storage FlashArray. Questions, comments? Ping me on Twitter at @DBArgenis or reply to this post.