SQL Server : To ensure efficient tempdb operation (Microsoft Recommendations)

While working on a performance issue, I have seen that many a times we have Long IO Errors on Tempdb database, which indicates that we have scope of optimization for our tempdb Database.

As per Microsoft, there are a few Recommendation which we should Test on Benchmark Servers for sure before making any changes in production environment.

We could see the below options for Tempdb Optimization. These are always the best practices which often increase performance. As we know the Role of Tempdb has increased Drastically after the introduction of Sql Server 2005 so , why not Try them on Bench and implement on production.

To ensure efficient tempdb operation:

·         Create one tempdb file per physical CPU core. (But should ideally be used when we have data files on separate disk spindles).
  This reduces page free space (PFS) contention.

·         Pre-size the tempdb files, and make the files equal in size.

·         Do not rely on autogrow.

·         Use startup trace flag 1118.

For more information about this SQL Server trace flag, pls. go through this link  http://support.microsoft.com/kb/328551

Note: There’s only one tempdb per instance, and lots of things use it, so it’s often a performance bottleneck. You guys know that already. But when does a performance problem merit creating extra tempdb data files?

There should be only 8 data files even we have more than 8 processor as per Microsoft recommendation unless there is  in-memory contention

Hope it will help you.

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)