PHISSUG

Home of Filipino Sql Server Enthusiasts
Welcome to PHISSUG Sign in | Join | Help
in Search

[TIP] Do Not Underestimate your TEMPDB database

Last post 11-21-2006, 5:06 AM by bass_player. 4 replies.
Sort Posts: Previous Next
  •  11-15-2006, 11:59 PM 555

    [TIP] Do Not Underestimate your TEMPDB database

    Whitepapers upon whitepapers have been written on configuring the TEMPDB database to improve performance.  One such thing is to create multiple files of the TEMPDB database and transaction log files.  But what is surprising is that a lot of SQL Server professionals (what surprised me is even Microsoft PSS engineers) store these files in the same partition.  The reason we are creating multiple database and log files for TEMPDB is to take advantage of multi-processor servers to write parallel entries in the TEMPDB database.  Storing them in a single partition contradicts this.  Your I/O will now be shooting up high since SQL Server is trying to write simultaneously to these files (even on SAN).  Might as well store these database files in different partitions to take advantage of parallel writes of your disk.  Also, an appropriate value for the TEMPDB size should be set.  Remember that the default size of TEMPDB out-of-the-box is a surprisingly 2MB.  For a high transaction database, TEMPDB will be under file size increase together with other transactions being handled by SQL Server.
  •  11-20-2006, 4:06 AM 576 in reply to 555

    Re: [TIP] Do Not Underestimate your TEMPDB database

    Lest the public be confused, the TEMPDB files of production SQL servers should not be placed on the same partition with the rest of the data / log files, or even on other partitions, per se.  The key word is, in fact, not partitions, but rather SPINDLES.  Having the TEMPDB file on some other partition, but still on the same set of disk spindles will cause almost the same performance issue as having it placed on the same partition.

    It is a different story on a SAN though, and I generally ignore the spindle considerations, except on special cases.  Different SAN vendors have their own "best-practices" for SQL on SAN, and this really depends on the underlying architecture of their file system.  My experience is with the NetApp SAN, and allocating specific spindles (hence, partitions) for a particular db is not the general practice.

  •  11-20-2006, 4:39 AM 581 in reply to 576

    Re: [TIP] Do Not Underestimate your TEMPDB database

    Thanks for the clarification, sir Enteng.  It is indeed SPINDLES.  And you are correct when you mentioned different SAN vendorsd have their own "best practices"  for SQL on SAN.  Ours are running on EMC Clarion (partner kasi namin Smile [:)])
  •  11-21-2006, 12:58 AM 592 in reply to 581

    Re: [TIP] Do Not Underestimate your TEMPDB database

    I envy Enteng and bass_player of having their toys so grand and now they get to play with spindles while i think most of us here are just taking it from the white papers or online sources. Most servers i knew just have 2 harddrives on a mirror configuration. With this, i got no choice but to have all my stuff in one spindle. not unless i give away my mirror.

    The farthest that i got to experience was to have a dual channel raid controller and got 6 scsi drives on raid configurations. drive 0, 1 are usually mirrored for OS and binaries on one channel, and drives 2, 3, 4 on either Raid 5 or other possible alternatives (for data), and drive 5 for tempdb/log files.

  •  11-21-2006, 5:06 AM 596 in reply to 592

    Re: [TIP] Do Not Underestimate your TEMPDB database

    I agree with sir ggsubscribe that SAN is a very expensive "toy" to play around with but having critical data is more than enough justification for such an investment. 
View as RSS news feed in XML
Powered by Community Server (Personal Edition), by Telligent Systems