This article continues from Part 1, where we looked at CPU, NUMA, and vSphere configuration best practices for SQL Server in a virtualized environment. In Part 2, we shift focus to storage and memory considerations, file initialization settings, instance design strategies, and how DNS aliases can simplify management. These optimizations build on the foundation from Part 1 and prepare us for the high availability design topics coming in Part 3.

---

NTFS Allocation Unit Sizes

What it is: The allocation unit size (cluster size) defines how data is physically stored on disk. By default, Windows uses 4KB, but SQL Server performs better with 64KB allocation units.

Why 64KB matters:

• Aligns with SQL Server’s extent size (8 pages × 8KB = 64KB).
• Reduces fragmentation and improves I/O efficiency.
• Helps ensure that reads/writes align correctly with underlying storage layers.

Best Practice: Always format SQL Server data and log volumes with 64KB allocation units.

Example PowerShell command to check allocation size:

fsutil fsinfo ntfsinfo D:

If you see “Bytes Per Cluster : 4096,” the drive should be reformatted with 64KB for SQL workloads.

---

Windows Indexing Service

What it is: A background service that catalogs files to speed up searches.

Why disable it for SQL volumes:

• SQL Server does not use indexing except for FILESTREAM features.
• Leaves unnecessary overhead on CPU and I/O.

Best Practice:

• Disable indexing on SQL data, log, and backup drives.
• Leave it enabled only if FILESTREAM or file-based search features are required.

---

Instant File Initialization (IFI)

What it is: By default, when SQL Server grows a data file, Windows writes zeros across the newly allocated space. IFI skips this zero-writing step for data files.

Why use it:

• Faster database creation.
• Faster restores.
• Faster autogrowth operations.

Why not use it:

• Security risk: previously deleted data blocks could still exist on disk and be accessible if someone gains raw access to the drive.

Best Practice: Enable IFI in most production workloads, but understand the security trade-off.

How to enable:

1. Open Local Security Policy → Local Policies → User Rights Assignment.
2. Add the SQL Server service account to “Perform volume maintenance tasks.”
3. Restart SQL Server.

---

Lock Pages in Memory (LPIM)

What it is: By default, Windows can page SQL Server memory to disk under pressure. LPIM prevents this by “locking” SQL Server’s buffer pool into physical RAM.

Why use it:

• Prevents SQL Server from losing memory during OS-level pressure.
• Critical for consistent performance in large-memory systems.

Why not use it:

• If the SQL instance is not tuned correctly, it can starve the OS of RAM.
• Requires careful monitoring of max server memory.

Best Practice: Use LPIM for dedicated SQL Servers and always configure max server memory properly.

---

Large Memory Pages

What it is: Instead of allocating memory in 4KB chunks, SQL Server Enterprise Edition can use 2MB “large pages.”

Why use it:

• Reduces CPU overhead for memory management.
• Fewer translation lookaside buffer (TLB) misses.
• Especially beneficial for instances with >32GB of RAM.

Why not use it:

• Not compatible with Columnstore Indexes.
• Can cause issues if the VM is under memory pressure.

Best Practice:

• Enable via Trace Flag 834 when using LPIM.
• Validate with:

SELECT 
    physical_memory_in_use_kb/1024 AS SQLServerMemoryUsageMB,
    large_page_allocations_kb/1024 AS SQLServerLargePagesAllocationMB
FROM sys.dm_os_process_memory;

Soft-NUMA

What it is: SQL Server 2016+ can split large NUMA nodes into smaller “soft-NUMA” nodes when there are more than 8 cores per socket.

Why use it:

• Improves scheduling efficiency.
• Helps distribute query workloads across CPU nodes.

Why not use it:

• May cause uneven CPU distribution (e.g., a 15-core node becomes 8 and 7).
• Some queries may perform worse.

Best Practice:

• Test workloads before disabling Soft-NUMA.
• Use MAXDOP hints for queries that show CPU imbalance.

Check status:

SELECT softnuma_configuration
FROM sys.dm_os_sys_info;

Multiple Instances vs. Single Instance

Single Instance per Application:

• Pros: Security isolation, simpler testing, smaller upgrade footprint.
• Cons: More VMs and OS overhead.

Consolidated Instances (many apps per instance):

• Pros: Fewer VMs, easier patching, resource pooling.
• Cons: Risk of contention (TempDB, CPU, RAM), harder to isolate workloads.

Best Practice:

• Use single instances for mission-critical apps.
• Consolidate only smaller workloads.
• Apply Resource Governor to prevent one workload from consuming all resources.

---

DNS CNAME Records for SQL Connections

What it is: A DNS alias that points to the actual SQL Server.

Why use it:

• Makes migrations and upgrades seamless.
• Instead of updating hundreds of connection strings, just repoint the CNAME.
• Provides rollback flexibility.

Example:
Application connects to SQLPROD01 (CNAME). The CNAME points to SQLVM01. During migration, update CNAME to SQLVM02. No application changes needed.

---

Summary

In Part 1, we focused on CPU, NUMA, and vSphere configuration considerations to ensure SQL Server performs well in a virtualized environment.

In this second part, we covered:

1. Storage: Using 64KB NTFS allocation units and disabling Windows Indexing where appropriate.
2. File Growth: Leveraging Instant File Initialization to speed up operations while weighing security implications.
3. Memory: Configuring Lock Pages in Memory (LPIM) and large pages for consistent performance.
4. NUMA: Understanding and tuning Soft-NUMA behavior.
5. Instance Design: Balancing single versus consolidated instances and using Resource Governor.
6. DNS Aliases: Simplifying migrations with CNAME records.

Together, these storage and memory best practices build on the virtualization groundwork laid in Part 1, giving you a stronger foundation for running SQL Server on VMware.