TempDB and Disk IO Tips and Tricks for SQL Server

SQL Server

One common issue with database performance is TempDB tuning. There are some basic tips and tricks to getting the best possible performance from your TempDB, but you need to understand that each instance will need tuning. There isn’t a checkbox in the server configuration that enables peek performance. You will need to investigate the specifics of your database instance and tune the server so that you get the best performance from your combination of hardware, software, and user load.

Proper configuration of IO subsystems is critical to the optimal performance and operation of any SQL Server system. Some of the most common best practices that recommended are best completed during instance configuration and installation.

  1. Understand the IO characteristics and requirements of SQL Server – In order to be successful in designing and deploying storage for your SQL Server installation, you need to have an basic understanding of SQL Server IO characteristics and patterns. Performance monitor is the best place to capture this information for an existing application, which will allow you to start asking specific questions about your instance.
    • What is the read vs. write ratio of the application?
    • What are the typical IO rates (IO per second, MB/s & size of the IOs)?
    • Monitor the perfmon counters:
      • Average read bytes/sec, average write bytes/sec
      • Reads/sec, writes/sec
      • Disk read bytes/sec, disk write bytes/sec
      • Average disk sec/read, average disk sec/write
      • Average disk queue length
    • How much IO is sequential or random in nature? Is this primarily an OLTP application or a Relational Data Warehouse?
  2. For peak performance, more and faster spindles are better – Additional drives with additional spindles will always allow for more performance and reduce the likelihood that drive speed is a source of the any bottleneck to peak performance.
    • Ensure that you have an adequate number of spindles to support your IO requirements with an acceptable latency.
    • Use filegroups for administration requirements such as backup and restore, partial database availability, etc.
    • Use data files to “stripe” the database across your specific IO configuration (physical disks, LUNs, etc.).
  3. Simpler designs generally offer good performance and more flexibility. than complex IO designs – This is basically the “Keep it simple stupid” approach to IO design.
    • Unless you understand the application very well avoid trying to over optimize the IO by selectively placing objects on separate spindles.
    • Make sure to give thought to the growth strategy up front. As your data size grows, how will you manage growth of data files, LUNs, or RAID groups? It is much better to design for this up front than to rebalance data files or LUN(s) later in a production deployment.
  4. Validate and test configurations before deployment – Do basic throughput testing of the IO subsystem prior to deploying SQL Server. Make sure these tests are able to achieve your IO requirements with an acceptable latency.
    • SQLIO is one tool which can be used for performance testing. A document is included with the tool with basics of testing an IO subsystem.
    • DiskSpd is another recent addition to your available toolset.
    • Understand that the of purpose running these tests is not to simulate SQL Server’s exact IO characteristics but rather to test maximum throughput achievable by the IO subsystem for common SQL Server IO types.
  5. Always place log files on RAID 10 disks –  The peak performance offered by RAID 10 will really help overall log performance. It isn’t always possible, but you should investigate if the additional cost is possible for your environment.
    • Best protection from hardware failure
    • Better write performance.
    • In general RAID 10 will provide better throughput for write-intensive applications. The amount of performance gained will vary based on the hardware vendor implementation of RAID. Most common alternative to RAID 10 is RAID 5.
  6. Isolate log from data at the physical disk level – You need your log files to be on different physical disk than your data disk, not just different logical disks.
    • In a consolidated SQL environments this may not be possible. You should then consider IO characteristics and group files with similar IO characteristics (i.e. all logs) on common spindles.
    • Combining heterogeneous workloads (workloads with very different IO and latency characteristics) can have negative effects on overall performance (e.g., placing Exchange and SQL data on the same physical spindles).
    • You will need to understand the disks at the physical level to reach the best possible configuration, so you really need to understand the virtual hardware configuration if your server is in a virtual environment.
  7. Consider configuration of TempDB database
    • Make sure to move TempDB to adequate storage and pre-size after installing SQL Server.
    • Performance may benefit if TempDB is placed on RAID 10 (dependent on TempDB usage).
    • For the TempDB database, create 1 data file per CPU
    • Place TempDB on a separate disk to user databases.
    • Place TempDB on the fastest IO subsystem possible.
    • Size TempDB accordingly and configure autogrow. This is especially important if your system cannot tolerate performance degradation. If you size the database too small with autogrow enabled, tempdb will automatically grow according to the size increments you have set up.
    • Keep tempdb data files equally sized and have autogrow increments configured equally across data files.
    • Don’t put the TempDB on the system folder (usually the C: drive), because you will see general Windows UI consuming available IO on the drive, and the TempDB can cause issues that could cause the system drive to fill unexpectedly. This could cause a system crash.
    • If your SAN administrator can provide it, split the multiple data files over different LUNs as opposed to holding everything on one LUN.
  8. Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads – Additional data files improved performance
    • It is recommended to have 1 data files (per filegroup) for each CPU on the host server.
    • This is especially true for TempDB where the recommendation is 1 data file per CPU.
    • A dual core CPU counts as 2, a quad core CPU counts a 4, etc.
  9. Remember SQL Server basics – You should use a mental (or written) checklist of things to check when attempting to achieve the best possible server performance.
    • SQL Server uses a proportional fill algorithm that favors allocations in files with more free space, so data files should be of equal size
    • Pre-size data and log files and not rely on AUTOGROW. If possible, instead manage the growth of database files manually. You may leave AUTOGROW ON for safety reasons, but you should proactively manage the growth of the data files.
  10. Don’t overlook storage configuration bases
    • Use up-to-date HBA drivers recommended by the storage vendor
    • Utilize storage vendor specific drivers from the manufactures website
    • Tune driver settings as needed for your IO volumes.
    • Ensure that the storage array firmware is up to the latest recommended level.
    • Use multipath software to achieve balancing across HBA’s and LUN’s and ensure this is functioning properly

4 thoughts on “TempDB and Disk IO Tips and Tricks for SQL Server”

  1. Thanks for sharing excellent informations. Your web-site is so cool. I’m impressed by the details that you’ve on this web site. It reveals how nicely you perceive this subject. Bookmarked this web page, will come back for extra articles. You, my friend, ROCK! I found just the info I already searched everywhere and simply could not come across. What an ideal site.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s