Skip to Content
J A

SQL Server Installation

Hello everyone,

I've got the folllowing question regarding SQL Server 2012 Installation:

During installation the SWPM asks to increase the size of TEMPDB (tempdev and templog files) to at least 300 MB for tempdev. Assuming a database size of 200GB what should be the recommended values for these files?

Regards,

JAM

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    Posted on May 05, 2014 at 06:34 PM

    The right size for TempDB is very dependent on what kind of system it is. BW systems, for instance, use TempDB considerably more than ECC systems, and there are a number of Notes and documents out there with recommendations on tuning TempDB for BW. Likewise, ABAP systems tend to use TempDB a lot more than Java/Portal systems, at least in my experience.

    What I've found to work for a moderately busy ECC production instance with a database around 525 GB in size is to have TempDB at about 10 GB. For my production portal, with database sized around 25 GB, a TempDB of 1 GB is more than enough (maybe even the 300 MB default would have been enough, but I sized it to 1 GB anyway).

    For the TempLog files, I like to start with about 10% of the TempDB size.

    If you see autogrowth occurring, then consider upsizing the initial file sizes.

    Add a comment
    10|10000 characters needed characters exceeded

    • That should work. There is no real advantage to having more than one transaction log file, and SAP generally recommends keeping it to one, though they do support multiple files.

      As for the database files, however, yes, there is an advantage to spreading it over multiple files. The DBMS and the OS are able to better parallelize (not sure if I have the correct word there!) the I/O operations if they spread it to multiple files, and furthermore the OS can generally do a better job if the load is spread across multiple LUNs, even if in reality the disk volume is on a SAN and already spread across numerous physical spindles for a single drive letter. So, multiple files, one per drive letter, is a good way to go. There is some debate about how many files are ideal, and it has to do with how many logical CPUs your server has (it's not one-to-one, though). It is discussed in the whitepaper SAP with Microsoft SQL Server 2008 and SQL Server 2005: Best Practices for High Availability, Maximum Performance, and S….

      Regards,

      Matt

  • author's profile photo Former Member
    Former Member
    Posted on Jun 09, 2014 at 04:39 AM

    In addition to Matt's reply:

    Having multiple database files on different disks works only if you have those disks configured on separate LUNs. So finally you should have different LUNs for different database files, it gives you the better I/O and you can use the parallelism effectively.

    Regards,

    Nick Loy

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.