Skip to Content
avatar image
Former Member

Tempdb / sapdata files best practice - MS SQL 2012/2014

Hi

We are migrating our BW system to MS SQL 2012 on windows.

Our approach:

C: OS + SQL Server Software

D: SAPDATA files for BW ABAP & PORTAL (total 16 data files) + TEMP DB

E: Database Logs + Temp Logs
F: SWAP

Would like to know the following

- Placement of Tempdb, we are using flash drive, do we still need to place tempdb in a separate drive say T: drive only for Tempdb?

- Do we need to split BW ABAP Data files and Portal Datafiles in a separate drive? or they both can reside in the same drive?

-

I went thru the recent best practice guide, nowhere they have mentioned to place tempdb in a separate drive.

Your guidance is highly appreciated.

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Sep 29, 2016 at 06:30 PM

    Hi Senthil,

    I would recommend separating TempDB from other types of files. I don't think you need to separate the TempDB DATA file and TempDB Log file, though you certainly can -- they can probably reside on the same disk, especially as it's an SSD. But I wouldn't put the TempDB data (or log) files on the same drive with your primary database files.

    I would recommend splitting your BW and Portal database files to separate drives. I would also recommend splitting their log files to separate drives, although at least with the Portal this may not be so much of a big deal (usually not so much write activity as in an ABAP system). Even if you are using SSDs, your system can benefit from separating the I/O. The exception to this could be if the data files reside on an external SAN that spreads the I/O across a large number of spindles or SSDs, but even in that case there are potential benefits to giving each their own drive letter, especially with a Windows system.

    Furthermore, at least with the BW system, I would recommend splitting the data files across multiple drives, not just one drive. This will improve your I/O.

    Here is how I have laid out our ECC ABAP system (on SQL 2012):

    • B: SQL Backups
    • C: OS / Pagefile
    • D: SQL Server application
    • E: SAP application (including application logs)
    • F: TempDB (including TempDB log)
    • G: Transaction Log
    • H: - O: Database 1 - 8

    Cheers,

    Matt

    Add comment
    10|10000 characters needed characters exceeded

    • We strongly suggest to define the size of tempdb to a greater size instead to rely on autogrow, because the time it takes for growing will slow down the performance, especially for SAP BW on SQL Server.

  • avatar image
    Former Member
    Sep 29, 2016 at 03:13 PM

    Hi Senthil,

    Please refer to the below screenshot (source is the installation guide)

    It's always advisable to have it on a different disk than data and log. Having it on same may cause issue later while replication.

    Thanks,

    Manas

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Senthil,

      You already have the details for tempdb. For data drives I normally distribute them equally in different drives to have the best I/O.

      You will also have to discuss this with your infra team as to how the drives are provisoned on the host, if they are coming from one datasource having one drive or multiple doesn't makes any difference as the actual datastore is same.

      Thanks,

      Manas

  • avatar image
    Former Member
    Sep 29, 2016 at 03:10 PM

    Hello,

    The provisioning of Flash drives for TempDB will give you the IO required for a transactional system. However, it is still recommended to place TempDB on its own drives (data and log separate) for segregating logical R/W activity and better monitoring and reporting in case you start seeing any contention.

    For a BW workload, please research Trace flags 1118

    Hope this helps.

    Add comment
    10|10000 characters needed characters exceeded