SQL Server Installation

[JAMpe]

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

[Matt_Fraser · Accepted solution]

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.

[JAMpe · Accepted solution]

Thanks for the reply Matt, the system is a NW AS ABAP 7.4 (not BW), the database sized won't be larger than 150 GB at least for the following year, so Im thinking about a tempdev around 3GB and templogsize around 300Mb.

If anyone has any other feedback, please share it.

Regards,

JAM

[Matt_Fraser · Accepted solution]

That'll probably work, or at least get you started.  TempDB is not so much dependent on your database size as it is the types and frequency of database activity that occurs.  Just make sure that autogrow is switched on and that there's room on the drive for it to grow, and then keep a watch on it.  If it doesn't grow, then you sized it well.  If it does, then beef it up a bit.

[JAMpe · Accepted solution]

Thanks for the reply Matt, I've got more question regarding my database layout.

I'm planning this:

Disk1: OS + SQL Server Software

Disk2: SAP Kernel

Disk3: TEMPDB

Disk4: Database
Disk5: Database Logs
Disk6: SWAP

My questions are:

- Is there an advantage to have more than one database log file?

- Is there an advantage to have the database (DATAX.mdf) in several Disks and not only in one(Disk4)?

Thanks for your time.

Regards,

JAM

[Matt_Fraser · Accepted solution]

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 .

Regards,

Matt

[Former Member]

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