none
SQL Server Disk Optimization to make SQL Faster in windows.

    Question

  • Hi again,

    I was said that 64 K block size is optimum for SQL Server. But Windows formats new partition with 4 KB block size by default. Probably 4KB block size is optimum for running Windows.

    I am going to setup a Physical (not Virtual) Server for our small clinic with just one Disk set (RAID 10 with 4 or 6 disks).

    Can somebody suggest me if that a good idea to create two partitions on that disk set-one (C:) for Windows OS with 4 KB block size and second (D:) for SQL Server installation with 64 KB block size?

    How it impact on my manageability (Windows updates, patches, etc)? 

    Thank you in advance,

    Alex

    Thursday, December 22, 2016 5:56 PM

Answers

  • Hello alilbas,

    For both SQL Server Database Engine databases and SQL Server Analysis Services, the recommended (and most optimised one) is 64KB clusters.

    Ideally, you should have different physical disks for OS and Data. The "perfect" solution would be different disks for {OS; pagefile; Data .MDF/.NDF; Data .LDF; TempDB}, where you might divide the Data parts into multiple disks (e.g. different tables, table partitions and/or indexes in each disk).

    Creating partitions does not normally improve performance. However, in that specific case, if you have a OS partition with 4KB clusters, and another Data partition with 64KB, that should be "slightly" better than everything in the same partition (assuming all NTFS). However, if the disk is mechanic (not SSD), it can even be slower.

    One of the ways you can benchmark performance is by using SQLIO and SQLIOSIM (see http://www.slideshare.net/technetbelux/stress-testing-using-sqliosim-and-sqlio slides).

    Hope this helps.

    Cheers.


    André Melancia | Developer / DBA / Microsoft Certified Trainer (MCT) | Andy.PT

    • Marked as answer by alilbas Thursday, December 22, 2016 6:48 PM
    • Unmarked as answer by alilbas Thursday, December 22, 2016 6:48 PM
    • Marked as answer by alilbas Thursday, December 22, 2016 10:15 PM
    Thursday, December 22, 2016 6:45 PM

All replies

  • Hello alilbas,

    For both SQL Server Database Engine databases and SQL Server Analysis Services, the recommended (and most optimised one) is 64KB clusters.

    Ideally, you should have different physical disks for OS and Data. The "perfect" solution would be different disks for {OS; pagefile; Data .MDF/.NDF; Data .LDF; TempDB}, where you might divide the Data parts into multiple disks (e.g. different tables, table partitions and/or indexes in each disk).

    Creating partitions does not normally improve performance. However, in that specific case, if you have a OS partition with 4KB clusters, and another Data partition with 64KB, that should be "slightly" better than everything in the same partition (assuming all NTFS). However, if the disk is mechanic (not SSD), it can even be slower.

    One of the ways you can benchmark performance is by using SQLIO and SQLIOSIM (see http://www.slideshare.net/technetbelux/stress-testing-using-sqliosim-and-sqlio slides).

    Hope this helps.

    Cheers.


    André Melancia | Developer / DBA / Microsoft Certified Trainer (MCT) | Andy.PT

    • Marked as answer by alilbas Thursday, December 22, 2016 6:48 PM
    • Unmarked as answer by alilbas Thursday, December 22, 2016 6:48 PM
    • Marked as answer by alilbas Thursday, December 22, 2016 10:15 PM
    Thursday, December 22, 2016 6:45 PM
  • 4 KB size is good for small files. With 64 KB blocks a short file of 100 lines of text still takes up 64 KB.

    But SQL Server files are not small. Small blocks => many blocks => more overhead and fragmentation on disk.

    Thursday, December 22, 2016 11:02 PM