[Video] Office Hours in North Bay, Ontario
I’m up in North Bay, Ontario, Canada for the 2026 Can-Am Curling Cup run by Aaron Bertrand – and my team actually won!
Let’s bask in the warmth (cough) of our glory as we go through your top-voted questions from https://pollgab.com/room/brento.
Here’s what we covered:
- 00:00 Start
- 02:07 Adrian: With all the storage on the same SAN with nVME, having separate virtual disks for data, log, temp data and temp log still makes sense? Our SQL servers have 7-8 drives each and managing free space is boring.
- 03:44 Newish Jr DBA: I’m a 7 month old SQL/DBA baby, but I just heard about policy management (PBM). After searching your blogs I did not find much. Do most shops not use it? Could you please elaborate on the pros and cons and primary use case for this tool?
- 05:05 Silent but Threadly: If a DBA never speaks at conferences or blogs, does that limit their career growth?
- 06:07 A Rose: Is “Database Engineer” just a rebranded DBA, or is it actually a different skill set?
- 07:02 MyTeaGotCold: Got much left to do for updating your mastering classes for SQL Server 2025?
- 08:49 Not Brent O: At what size or number of servers do you recommend clients use Central Management Servers (CMS), and any other advice regarding CMS?
- 09:20 Meatbag: How is AI changing your training classes and conference sessions?
- 11:35 For a Friend: How many years of experience does someone really need before they can call themselves a “Senior DBA”?
- 13:11 MidwestDataPerson: I use SP_Rename to switch tables. Any gotchas about this approach? What about indexes and statistics? e.g. ‘Table1’ becomes ‘Table1_old’ and ‘Table1_new’ becomes ‘Table1’. Indexes are built on ‘Table1_new’ prior to rename.
7 Comments. Leave new
The change rate of tempdb being a problem for san replication isn’t something I ever considered – hadn’t thought of replicating tempdb before. Wondering if optimized locking + RCSI/snapshot poses a latency problem for replication in the same way
Regarding the first question (separate disks with SAN SSDs):
I’m using the Dell PowerStore as SAN and made 3 “drives” per database: Node A, Node B and Log
Each filegroup in the database consists of two (or an even number) of files, one on Node A, one on Node B. The transaction log file is stored on the log disk.
In the PowerStore I assigned the two data drives explicit to one of the Nodes (simplified “CPUs”) in the PowerStore and are set to medium performance tier while the log disk is set to high performance (and takes whatever Node is available at the moment)
All 3 drives are part of one volume group and I take regularly (automated) SAN snapshots of this volume group. This way I can “restore” a database from one of this snapshots within 5 minutes (faster, when I hadn’t to remote connect to the server / PowerStore first :-)) and have a constistent version of this database.
The tempdb is placed on SSDs inside the server because it is faster and I don’t want to bother the SAN with encypting / compressing this stuff.
Initally I created the backups on the PowerStore too, but my backup time was reduced by ~20-30 % when I changed it to local SSD drives too. Of course I have another server that gathers those local created backups, copies it to its own RAID 6 HDDs and does a test restore / checkdb etc.
So: yes – data distribution over several disks is still relevant, but compared to our old server which had several SSDs, some fast HDDs, some slow HDDs etc. where I manually had to copy / move the database files every year to get the best performance for the hot data, it is MUCH easier (and SAN snapshots are excellent, when it would take hours to restore your 20 TB database the usual way)
on-prem: simpify your disk structure by having mounted drives.
SQLOS DB’s – useful to sperate your system DB’s – very useful in recovery situations, protected from issues from your userDB’s
TempDB – you don’t want this near your data or logs when things get busy – preempt whats going to hit your system
Data – mounted drives allow you to easily attach another disk and file – sql likes parallel IO operations. You could even extend to different HBA cards if your system was that busy – infrasturcture is important.
Log – sperated from data – you’ll be glad when you have done so when you have to do some sort of recovery.
It easy to see growth and manage that going foward.
These principals of logical seperation have been around for years. They still hold up. Just becasue you think you have fast disk doesn’t mean you should throw away solid foundations for structure and giving your DB’s the best possible chance for IO performance.
Psst: https://www.brentozar.com/archive/2025/01/does-separating-data-and-log-files-make-your-server-faster/
https://www.brentozar.com/archive/2017/06/separating-data-log-files-make-server-reliable/
It’s really not. The OS and SQL do a much better job of managing a given amount of i/o from a single disk, than dividing the same i/o into smaller pools of i/o in multiple disks.
In dev servers I have gotten noticeably improved disk performance by putting everything onto a single disk that maxes out the instance type’s available disk performance – as much as double the performance. (based on duration of jobs/etls/reports/etc)
If you are coming up against the limits of the HBA, that is one thing, but not many SQL Servers will be able to saturate a quad channel 12 Gbps SAS HBA, nevermind the PCI bus.
Now I still do separate the files out, but its not to improve performance, it’s to manage storage. I don’t want an *ahem* 15 Gigabyte report to run, saturate the storage i/o then have log redo pile up, I would rather the report run out of i/o on its own disk and let the logs keep going. or have the report use up all the i/o reading from the mdfs and ndfs then not have available i/o for applications to keep reading and writing, etc
I like the Office-Hours + what-I’m-doing-now pictures.
It reminds me of Filippo Valsorda’s newsletter (https://words.filippo.io/claude-debugging/) in which he mix deep knowledge and private life.
It makes everything so human.
Hi Brent, I’m an avid curler, so it’s awesome to see you giving curling a try. Have you been doing it for years, or is this your first time? It’s one of those sports that’s a lot harder than it looks, especially if you didn’t learn when you were young.