SQL SERVER – NTFS File System Performance for SQL Server

Posted by pinaldave on SQL Authority See other posts from SQL Authority or by pinaldave
Published on Fri, 06 Jul 2012 01:30:14 +0000 Indexed on 2012/07/06 3:20 UTC
Read the original article Hit count: 473

Filed under:

PostADay

|

sql

|

SQL Authority

|

SQL Performance

|

SQL Query

|

SQL Server

|

SQL Tips and Tricks

|

T SQL

|

Technology

Note: Before practicing any of the suggestion of this article, consult your IT Infrastructural Admin, applying the suggestion without proper testing can only damage your system.

Question: “Pinal, we have 80 GB of data including all the database files, we have our data in NTFS file system. We have proper backups are set up. Any suggestion for our NTFS file system performance improvement. Our SQL Server box is running only SQL Server and nothing else. Please advise.”

When I receive questions which I have just listed above, it often sends me deep thought. Honestly, I know a lot but there are plenty of things, I believe can be built with community knowledge base. Today I need you to help me to complete this list. I will start the list and you help me complete it.

NTFS File System Performance Best Practices for SQL Server

Disable Indexing on disk volumes
Disable generation of 8.3 names (command: FSUTIL BEHAVIOR SET DISABLE8DOT3 1)
Disable last file access time tracking (command: FSUTIL BEHAVIOR SET DISABLELASTACCESS 1)
Keep some space empty (let us say 15% for reference) on drive is possible
(Only on Filestream Data storage volume) Defragement the volume
Add your suggestions here…

The one which I often get a pretty big debate is NTFS allocation size. I have seen that on the disk volume which stores filestream data, when increased allocation to 64K from 4K, it reduces the fragmentation. Again, I suggest you attempt this after proper testing on your server. Every system is different and the file stored is different. Here is when I would like to request you to share your experience with related to NTFS allocation size.

If you do not agree with any of the above suggestions, leave a comment with reference and I will modify it. Please note that above list prepared assuming the SQL Server application is only running on the computer system.

The next question does all these still relevant for SSD – I personally have no experience with SSD with large database so I will refrain from comment.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

Developer IT