THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Old Performance Tuning Recommendations Die Hard

It’s interesting to me that old and inaccurate performance tuning recommendations seem to have a life of their own.  In some ways, old performance tuning recommendations are like the Undead from some kind of cheesy, 1970’s zombie movie – no matter how many times you shoot them, they just keep coming back.

 

Here’s a good case in point, http://msdn2.microsoft.com/en-us/library/ms345118.aspx, a white paper discussing Performance Optimizations for the XML data type in SQL Server 2005.  The document states:

 

Multiple tempDB Files for Better Scalability of XML Variables and Parameters

XML variables and parameters use main memory as storage as long as their values are small. Large values, however, are backed by tempdb storage. In a multi-user scenario, if many large XML blobs occur, tempdb contention may become a bottleneck for good scalability. Creating multiple tempdb files reduces the storage contention and yields significantly better scalability. The next example illustrates how multiple tempdb files can be created.

Example: Creating Multiple tempdb Files

This example creates two additional data files for tempdb, each with an initial size of 8 MB, and two log files with an initial size of 1 MB.

Copy Code

USE TEMPDB
GO
 
ALTER DATABASE tempdb ADD FILE 
   (NAME = 'Tempdb_Data1', 
      FILENAME = 'C:\temp\Tempdb_Data1.MDF', SIZE = 8 MB),  
   (NAME = 'Tempdb_Data2', 
      FILENAME = 'C:\temp\Tempdb_Data2.MDF', SIZE = 8 MB) 
GO
 
ALTER DATABASE tempdb ADD log FILE 
   (NAME = 'Tempdb_Log1',
      FILENAME = 'C:\temp\Tempdb_Log1.LDF', SIZE = 1 MB),  
   (NAME = 'Tempdb_Log2',
      FILENAME = 'C:\temp\Tempdb_Log2.LDF', SIZE = 1 MB)
GO

These files can be removed by using the ALTER DATABASE tempdb REMOVE FILE command. For more information, see SQL Server Books Online.

 

 

There was a time when this recommendation made sense.  The intent behind multiple tempdb files prior to SQL Server 2005 was to avoid GAM contention on very high throughput scenarios.  Later, SQL Server 2005 introduced segmented GAMs even within a single tempdb file. 

 

This recommendation still holds for data files, especially on systems using NUMA processors, where IO should be aligned with one data file per NUMA socket.  (It also does no harm on an SMP system to organize your data files this way, hence the standardized recommendation.)

 

However, the recommendation fails when you get to the log portion of the equation.  Why?  It’s because data file IO is written using the proportional file algorithm where each data file has data written to it in round-robin style.  On the other hand, log files are written using the active file algorithm where LogFile1 is written first until full, then LogFile2 is written next until full, and so on…  Long story made short (too late, I know) – you get no performance gain from having multiple files in the log because all writes occur on only one file.  You can only get a performance gain from multiple files on the data portion of a database.

 

As an aside, Simon Sabin (http://sqlblogcasts.com/blogs/simons/) has a wonderful collection of addition myths, misunderstandings, and miscommunications.  I’m not sure if they’re put together in a single blog post, so put his blog on your watch list.

 

Cheers,

 

-Kev

 Twitter @ kekline

 

P.S.  Thanks to Tony Rogerson (http://sqlblogcasts.com/blogs/tonyrogerson) and Geoff Hiten for the inspiration on this blog post, btw.

 

P.P.S.  I like how the MSDN article refers to SQL Server 2005 as SQL Server 9.0.  ;^)

Published Tuesday, June 23, 2009 10:19 AM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Joe Chang said:

Great post Kevin, to many people collect and regurgitate best practices without the original data, or current supporting data,

I have had my deep suspicions on this one for a while, but can make no headway because of all the Microsoft people repeating it.

Most of my big servers with 16+ cores (when cores were processor sockets) ran fine with 4-8 tempdb data files. I did one tempdb per LUN used by data file for peak IO capability, not for the rule of 1 per core.

I am also inclined to think that this problem really pertained to code where almost every RPC required a tempdb, explicit or implicit.

A well designed app should only require infrequent use of temp.

But we really a central site to kill stupid, out-of-context best practices. Few people seem to appreciate that long established best practices were established long ago. In computer world, 10 years is 5 generations of Moore's law ago, ie, 32X

June 23, 2009 10:58 AM
 

Joe Webb said:

Well said, Kevin. I ran across a similar issue the other day where a very credible source was proffering really old SQL Server tempdb advice.

Another major is when people offer configuration recommendations in a blog or forum without specifying the version. Since it lives on the internet, someone may find it years after the fact and consider it still relevant.

June 23, 2009 11:38 AM
 

Linchi Shea said:

> It’s because log file IO is written using the proportional file algorithm where LogFile1 is written first until full, then LogFile2 is written next until full, and so on

Hmmm ... I believe the proportional fill algorithm only applies to the data files within a filegroup.

June 23, 2009 12:24 PM
 

ALZDBA said:

I think you meanth the active log portion is written to a single file, in stead of "proportional fill".

With some instances we enabled autogrow for multi data file tempdb.

Still we see the first datafile grow vastly more than the others.

(startsize 1GB, extend size 100MB)

After a week, we notices the first tempdb file being 23GB whilest the others were still 1GB.

The refs I use for tempdb data files:

 help protect SQL Server performance by adding one file to tempdb for every processor core,

 or maybe every 1.5 processor cores,

up to a maximum of 8 files.

http://blog.sqlauthority.com/2007/04/01/sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive/

http://support.microsoft.com/kb/187824

http://support.microsoft.com/kb/224071

http://www.tech-recipes.com/rx/2342/sql_server_2005_move_tempdb/

- "Concurrency enhancements for the tempdb database"

  http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en (shorter story)

- Working with tempdb in SQL Server 2005", paragraph "tempdb size, including the right number of files and file size",

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

- related to max_degree_of_parallelism ? http://support.microsoft.com/kb/329204/ (only if needed)

June 24, 2009 7:03 AM
 

KKline said:

Great responses, guys!

Linchi and AlzDBA, thanks for pointing out that error.  (You knew what I meant though!)  I've fixed it.

-Kev

June 24, 2009 10:04 AM
 

KKline said:

June 30, 2009 1:21 PM
 

Kevin Kline said:

Before I jump onto the Goals and Themeword meme started by my buddy, Thomas LaRock ( blog | twitter ),

January 5, 2010 11:42 PM
 

Aaron Bertrand said:

This week we're going to look at some issues involving tempdb Provide a tempdb per database #176241 :

May 9, 2011 8:27 AM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement