SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability34
The temporary database for SQL Server 2005 is named tempdb. This database is used by queries to execute large join, sort, and group operations when the SQL Server buffer pool cannot provide enough memory. tempdb is also heavily used by SQL Server 2005 base features, including snapshot isolation levels, which are used for online index maintenance or to assemble data that is pushed into text/image or varchar(max)/varbinary(max) data type columns.
SAP products such as mySAP™ Customer Relationship Management (mySAP CRM), and SAP BW each stress tempdb in different ways and have different performance requirements. The use of tempdb differs according to the type of workload. The two types of workloads are an online workload, as in the case of mySAP™ CRM, and mySAP Enterprise Portal (mySAP EP) and an online analytical processing (OLAP) oriented workload such as those created by SAP BW:
Online workloads. mySAP products with online workloads, including mySAP CRM and mySAP EP, use tempdb infrequently, such as for larger join operations, aggregation, and smaller sorts. tempdb typically does not load or sort gigabytes of data and does not affect performance. The only exception is when a physical consistency check is run on a SAP database. For installation, set the tempdb space from 1 to 2 GB with six to eight spindles throughput. For Storage Area Network storage, tempdb can share space with the tempdb log files.
OLAP-oriented workloads. For SAP BW, tempdb can expand to larger sizes. For example, join operations, especially those that fully scan the fact table of a cube, can use gigabytes of space to store temporary result sets, large sorts, or hash probes or to build aggregates. In these cases, tempdb is used to perform most of the work because the memory space usually is not sufficient to process gigabytes of data. In extreme cases, when the whole fact table is read to build an aggregate, tempdb can grow to the size of the fact table, up to several hundred gigabytes.
SAP BW tempdb performance. For SAP BW, tempdb I/O performance can become a major bottleneck when executing reporting queries that use the fact table or that perform aggregation. To prevent bottlenecks, set the tempdb size to 1.5 times the space of the largest fact table. Manage tempdb strictly as a normal SAP BW database. Use one data file of tempdb on the same partition with each data file of the SAP BW database. In this case, the tempdb space should provide fast read/write performance. In addition, do not place tempdb on the same partition and disks that contain the transaction log.
Use with SQL Server 2005. Some features of SQL Server 2005, such as online index maintenance, use tempdb. In contrast to earlier SQL Server releases, tempdb is also used to insert BLOB fields into database tables. (BLOB fields are raw fields longer than 3,800 bytes in SAP products).
Microsoft Corporation © 2005