Enterprise TechSQLSQL Server Disk Configuration SAN SSD and Partitioning Best Practices January 19, 201712540 views0Share By IG ShareSQL Server Disk configuration settings are one of the most important aspects of SQL Server performance tuning. Typically, disks are one of the slowest parts of the entire SQL subsystem. Without proper disk configuration, SQL Server can slow down, increase locks and waits. It can also significantly increase the overall memory and CPU usage. In short, disk management and optimization are crucial for SQL Server performance. In this post, we are going to focus on just that. Read along to know more.Note: If you buy something from our links, we might earn a commission. See our disclosure statement.Also Read: Why is SQL Server 2016 Faster than all earlier EditionsAlso Read: Performance Best Practices for SQL Server on Azure Virtual MachinesTable of Contents ToggleStandard SQL Server Disk Configuration SettingsSQL Server Disk Configuration Settings for TempDBSQL Server Disk Configuration Settings for RAID, Storage Pools, SSD’s and Storage SpacesCommon FAQ’s on SQL Server Disk Configuration SettingsStandard SQL Server Disk Configuration SettingsBefore going into the specifics, there are some generic requirements that are recommended for SQL Server Performance. These are as follows:Use separate disk drives for different purposes. (Log File, Data Files, Backup & TempDB)Maintain strict Disk latencies. For Database Files (MDF) 15-25 ms. For Log files, between 2-5 ms. This is applicable to both standard & tempDB data & log files.Format all the disk drivers (that will contain Data & Log files) with 64 KB cluster allocation.If you are running any corporate anti-virus programs, put *.mdf, *.ndf & *ldf under exemption list.TempDB should be planned accordingly based on the optimizations defined by Microsoft. See below:Also Read: Performance Tuning Best Practices for SQL Server in a Virtual MachineSQL Server Disk Configuration Settings for TempDBStorage Planning for TempDBSet the recovery model of TempDB to SIMPLE. This model automatically reclaims log space to keep space requirements small.Disable to default allow settings for TempDB files to automatically grow. This reduces the CPU overhead of managing a dynamic file growth.Create multiple data files for TempDB. How Many? Total Number of TempDB Primary Data File = SQL Processor Count not exceeding 8 files. Which means, if you have 16 cores for SQL Server, create only 8 files.Each data file should be of equal size.Try to keep each data files on separate disk drives for achieving high IO Parallelism.TempDB Data and Log files should be kept in faster disk drives (Preferably RAID 1 if possible)Use RAID-10 or SSD Disks.Pre-size TempDB files25% of largest DB size.Set Auto Growth to fixed size < 200 MBYou should have the same number of data files as the number of CPUs up to a maximum of 8.Note: The new SQL Server 2016 Installation program allows you to create seperate TempDB files during the setup process.SQL Server Disk Configuration Settings for RAID, Storage Pools, SSD’s and Storage SpacesIn the last couple of years, storage pools gained a lot of popularity. Instead of complex and expensive RAID implementations, storage pools are the way to go in the future. With storage pools, you can add disks/spindles as you need more performance. However, even though there is a common storage pool across workloads, it is important to isolate them at the logical level. This, in turn, would help you to isolate disk bottlenecks of a slow performing query. If you put everything in a single pool, there is no way to tell what is causing the disk performance to degrade. On a high level, isolate the System DB’s, User DB’s, TempDB’s and Log files at the logical level.Modern storage system controllers already do a great job of distributing workloads across different disks internally and implementing RAID on top of it does not make a lot of sense. However, it is important to contact your storage vendor for the best practices.Common FAQ’s on SQL Server Disk Configuration Settings<Question>If you add more RAM than the actual dB Size, it stores the dB in memory (RAM) instead of going to disk to retrieve data? <Answer>No, typically the SQL Server Buffer Manager decides what to keep in memory and what not to. There is no administrative control over it. However, more RAM is good for SQL Server performance. Especially for repetitive queries.<Question>How to measure the “Disk response time” in milliseconds? <Answer> You can use free tools for SQL Server available on the Internet to measure this. For example, In a spindle disk configuration, if data is fragmented and random read/writes patterns are observed, the Disk seek time/response time would be very high.How many TempDB files needs to be created? Should we create 8 tempdB files (or 16 to match the core count?). What size would you suggest for each? <Answer> The number of Tempdb files should not exceed a maximum of 8, even if core count is 16.With NVMe based storage pools, Do we still need to look at separate disks for data, log & tempdB devices these days? Can they not all be stored on a single 20 spindle RAID10 array and separated logically? <Answer> We still recommend them to be physically isolated. All of these workloads have very different access patterns. It is, therefore, beneficial to keep these workloads in isolation.<Question> Do you still need a Physical Server to get maximum SQL Performance? <Answer> If you follow all the best practices while building your SQL Server, you can get similar performance from a SQL Server running in a VM.Hopefully, this was helpful. If you have any comments or suggestions, please feel free to drop us a comment below in the comment section. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Enterprise TechList of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By IGMay 25, 2021
Enterprise TechList of the Best HPTX Cases – Huge PC Cases for Large BuildsThe computer cases have been one of the essential aspects when it comes to enjoying ...
AzureBest Industrial IoT Routers & Gateways For AWS and Azure IoT ServicesIn this article, we are going to list out the Best Industrial IoT Routers for professional ...
Enterprise TechWhat Is A DataCenter Power Distribution Unit (pdu) Server Rack CabinetsNo matter whether you call it a server closet or server room or a cabinet, ...
Storage SystemsWD SE vs WD Red Pro Specifications Comparison – Enterprise Class NASThe HDDs or the Hard Disk drives are slowly moving into oblivion with the advancements ...