Why Disk Layout Still Matters More Than People Think

After 25 years working with databases — SQL Server, Oracle, PostgreSQL and MySQL — I can tell you something that many younger engineers underestimate today:

Disk architecture still matters. A lot.

Cloud environments, SSDs, NVMe and distributed storage changed many things, but poor file configuration can still destroy the performance of a database server.

I’ve personally walked into environments where companies spent hundreds of thousands of dollars on powerful hardware, but the SQL Server instance was running terribly simply because:

• TempDB was on the same disk as data files
• transaction logs shared disks with backups
• autogrowth was misconfigured
• storage latency was ignored
• file sizes were never planned

The result?

High CPU, massive waits, IO bottlenecks, and constant firefighting.

Let me walk you through the real best practices for SQL Server file configuration — not the theoretical ones, but the ones I learned through real production incidents.

---

TempDB: The Most Abused Database in SQL Server

TempDB is one of the busiest databases in SQL Server.

It is used for:

• sorting operations
• hash joins
• temporary objects
• row versioning
• internal engine operations
• index rebuilds
• many query processing tasks

If TempDB is poorly configured, the entire instance suffers.

And yes — I have seen systems where TempDB was the root cause of almost every performance issue.

---

Always Use a Fast Dedicated Disk for TempDB

TempDB should never compete with user database files.

When TempDB shares disks with MDF files or transaction logs, you create unnecessary IO contention.

The best practice is simple:

TempDB should live on the fastest storage available.

Today that usually means:

• NVMe
• high-performance SSD
• premium cloud disks

---

Multiple TempDB Files

One of the most common problems in SQL Server is allocation contention in TempDB.

Historically this appears as contention on:

• PFS pages
• SGAM pages
• GAM pages

The typical starting configuration is:

1 TempDB data file per CPU core up to 8 files.

Example:

If the server has 16 logical cores:

Start with 8 TempDB files.

If contention persists, increase gradually in multiples of 4 files.

But don’t blindly create 32 files just because you have 32 cores.

That creates management overhead and sometimes worse performance.

---

Equal File Sizes Are Critical

Another mistake I still see constantly:

TempDB files with different sizes.

SQL Server allocates pages proportionally based on file size.

If one file is larger, it will receive most allocations, defeating the purpose of having multiple files.

All TempDB data files should:

• have identical initial size
• grow at the same rate

---

Autogrowth Configuration

Never use percentage autogrowth for TempDB.

It causes unpredictable file expansion and fragmentation.

Instead configure fixed growth increments such as:

• 512 MB
• 1 GB
• or larger depending on workload

In very stable environments with dedicated disks, some DBAs even preallocate most of the disk space and disable autogrowth entirely.

In those cases we create a small emergency file that can grow if something abnormal happens.

If that file starts growing, it’s a sign something is wrong.

---

Data Files (MDF / NDF)

Data files store the actual tables, indexes and metadata.

One of the worst configurations I still see is when data files share disks with logs or backups.

That creates mixed IO workloads:

• random reads
• random writes
• sequential writes

Mixing those patterns destroys storage efficiency.

---

Dedicated Storage for Data Files

Data files should be placed on separate storage volumes optimized for random IO.

Large OLTP systems often require extremely high random read performance.

When data files share disks with logs, the sequential log writes compete with random data IO, and everything becomes slower.

---

Planning Initial File Size

Autogrowth events are expensive.

Every time SQL Server grows a file:

• new space must be allocated
• the file may need to be zeroed (depending on settings)
• transactions may pause

I’ve seen systems where files grew hundreds of times per day, causing constant performance spikes.

A better strategy is to:

Estimate at least 6 months of expected growth and allocate space ahead of time.

---

Transaction Log Files (LDF)

If there is one thing every DBA eventually learns the hard way, it is this:

The transaction log is sacred.

The log records every change made to the database.

It is the backbone of:

• recovery
• replication
• high availability
• backups

When log IO becomes slow, the entire database slows down.

---

Log Files Must Be On Dedicated Storage

Transaction logs perform sequential writes.

Sequential IO benefits enormously from dedicated disks.

When logs share disks with data files, the random IO patterns of data access interrupt sequential log writes.

The result is slower commit times and higher latency.

---

Log File Size Planning

Another painful situation I have seen many times:

Transaction logs growing uncontrollably.

Reasons include:

• long transactions
• missing log backups
• large batch operations
• index rebuilds

A good rule of thumb is to size the log file to handle large operations comfortably.

A common starting point is:

20–30% of the database size

But this depends heavily on workload.

---

Backup Storage

Backups should never run on the same disks used by active database files.

Why?

Because backup operations generate large sequential reads.

If those reads compete with active database IO, performance suffers.

Backup storage should ideally be:

• dedicated disks
• remote storage
• cloud storage
• backup appliances

And above all:

Backups must always be tested with restores.

Because a backup you never tested is not a backup — it’s just a theory.

---

RAID and Storage Redundancy

Storage architecture also matters.

Typical configurations include:

• RAID 10 for data
• RAID 1 for logs
• cloud redundancy options

RAID 5 is generally avoided for heavy write workloads due to parity overhead.

---

Lessons Learned From Production Incidents

Some of the worst production issues I’ve seen were caused by poor storage design.

Examples include:

• TempDB filling the system drive
• logs competing with backups
• databases pausing due to constant autogrowth
• IO latency spikes during peak traffic
• replication delays caused by slow log disks

These problems rarely appear in development environments.

But in production, under heavy workloads, they can bring systems to their knees.

---

Final Thoughts

Database performance is not only about writing good SQL.

It is also about building the right infrastructure around the database engine.

Proper disk layout helps ensure:

• predictable performance
• stable IO patterns
• faster recovery
• lower latency
• fewer production incidents

After 25 years working with databases, one thing remains true:

A well-designed storage architecture can prevent many problems before they even start.

And preventing problems is always better than fixing them at 3 AM.

🚀 Ready to boost your career in data?

👉 DBAcademy – DBA & Data Analyst Training
Over 1,300 lessons and 412 hours of exclusive content.
Includes subtitles in English, Spanish, and French.

🔗 https://filiado.wixsite.com/dbacademy

💡 Start learning today and become a highly in-demand data professional.