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

[Former Member]

Hi

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

Our approach:

C: OS + SQL Server Software

😧 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

[Matt_Fraser · Accepted solution]

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
• 😧 SQL Server application
• E: SAP application (including application logs)
• F: TempDB (including TempDB log)
• G: Transaction Log
• H: - O: Database 1 - 8

Cheers,

Matt

[Former Member · Accepted solution]

Hi Matt,

Thank you for your replies, it is very helpful.

Question1: I went thru the installation guides, nowhere they say use different drives for sapdata files.  So does it matter to seperate SAPDATA files across different drives? is there any best practice document on that?

Thanks

[kaus19d · Accepted solution]

Regarding this matter, I found some contents, just thought you might find that useful,

Moving SAP Database Files to New Disks - SAP/ MS SQL Server DBA in CCMS - SAP Library

Optimizing tempdb Performance

https://www.brentozar.com/blitz/tempdb-data-files/

Some know answers to some relative questions in this matter,

Managing TempDB in SQL Server: TempDB Configuration | SQL Server Database Engine Blog

Note: That you can always set the growth of the TEMPDB or set auto-grow mode, depending on your requirement & choice.

Thanks,

Kaushik

[Matt_Fraser · Accepted solution]

Senthil,

This wasn't specifically something for SAP, but rather an old "best practice" for SQL Server and Windows. In the old days of early Windows Servers (think, NT and win2000), the operating system managed disk I/O per drive letter. This probably didn't make much difference in the typical SCSI drive systems of the day, but once super-fast SAN systems with the capability to make large volumes striped across many spindles became the norm, the question naturally arose, "Well, why not just put all the data in one big file? After all, the SAN is already spreading it across a dozen (or a dozen dozen) spindles."

The answer to that was that Windows itself managed the I/O per volume, so even if all the volumes were "virtual" and existed in the same physical disk array, it would still be faster to separate them logically. There was only so much I/O that a single CPU core would handle effectively, of course, so there was an upper limit to the number of drives based upon the number of cores beyond which there would be no more performance gain. However, these days a typical server has so many cores that instead of x drives per core, it's more likely to be x cores per drive.

It's not clear if this is still a consideration in more recent editions of Windows; probably not. However, it remains a very real consideration to separate your database into multiple logical files, with the number of files chosen being loosely based upon the number of CPU cores available on the database server (not 1:1). If enough cores are available, then database I/O will be optimized if it can be spread across multiple I/O streams to different files. Whether those files can all reside on a single drive or volume, or whether they should be separated to different volumes, remains a bit of an open question for today's servers -- in the past, yes, they definitely should have been on different drives.

You can find a good discussion, and a benchmark test, of this effect at Benchmarking: do multiple data files make a difference? - Paul S. Randal.

Further worthy discussions are at How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes – CSS... and How to improve SQL Server's IO performance by up to 40% , though they don't as directly address the original question.

It also seems to make a difference to always use an even number of data files, never odd (unless it's just a single file by itself), and for many installations, the sweet spot seems to be at around 8 files. I think at 16 files the gain reaches a point of diminishing returns and your management overhead starts to be more of a concern.

One thing to note: for BW systems, the TempDB typically comes into much more use than it does for ECC systems, so you will want to make sure that you make it large enough, and you will want to consider breaking this one up into multiple data files as well. Even for ECC systems, I find that the default size of TempDB from the SAP installation program (300 MB) is far too small; I usually go for 10-15 GB.

Cheers,

Matt

[luisdarui · Accepted solution]

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.

[Former Member]

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

[luisdarui]

If I would setup an On Premise SQL Server database for an Large Enterprise company, I would go for RAID10 for the data files. It offers 4x read speed against 2x of RAID5, better disk faulty tolerance (depending on which disks, it can be two instead of one). The downside is the 2x times for writing (RAID5 depends on the controller settings), but for the data files you don't write synchronously like the T-Log file, so it shouldn't be considered a "con" for RAID10, IMHO.

But if I had a choice, I would go for cloud instead of On Premise, of course!

Cheers,

Luis

[Former Member]

Thank you Luis, yes we are going for RAID 10.

I'm still wondering, how placing datafiles, tempfiles in a different drives gives good performance, when we already spread the data in LUN especially in a SSD.

Thanks

[Former Member]

Thank you Manas.

I read thru the  installation document, SAP speaks only about the LUN separation, I'm looking more about Drive separation.

Regards

Senthil

[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

[Former Member]

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.