Using SQL Server 2012 Column-Store with SAP BW
SAP COMMUNITY NETWORK scn.sap.com
© 2012 SAP AG 16
Administration
Configuring Resources
To ensure optimal system performance, you have to give sufficient system resources to SQL Server. The
most important resources are memory and CPU. For using the column-store with SAP BW, you should have
a database server with at least eight CPU threads (for example, 2 CPUs having 4 cores per CPU). In typical
customer scenarios, the database server has 16 CPU threads or more.
SQL Server memory
SQL Server memory manager dynamically allocates memory used for the column-store. An administrator
only has to make sure that the total memory configured for SQL Server is sufficient. Normal tables and b-tree
indexes allocate memory from SQL Server Buffer Pool. The column-store uses its own memory pool, the
Column Store Object Pool. You can check the size of both pools by running the following SQL command:
select * from sys.dm_os_memory_broker_clerks
The query returns 2 rows containing the total size of both pools (or only one row for the Buffer Pool, if the
Column Store Object Pool is not used at all).
As of SQL Server 2012, the configuration options min and max server memory contain all memory used by
SQL Server, not only the Buffer Pool. For more information about SQL Server 2012 memory configuration for
SAP systems, see SAP note 1702408.
SQL Server parallelism
SAP recommends setting the global SQL Server configuration option max degree of parallelism to 1. SAP
BW overwrites this global setting by means of SQL Server optimizer hints. Per default, SAP BW requests two
SQL Server threads per query and eight SQL Server threads for index creation. However, you can change
the default behavior by setting RSADMIN parameters:
MSS_MAXDOP_INDEXING
This RSADMIN parameter sets the maximum number of CPU threads that are used during index
creation in SAP BW (for column-store indexes and b-trees). Index creation is performed when
originally creating the column-store index, during BW process chains, and during cube compression.
The default value of MSS_MAXDOP_INDEXING is 8. SAP does not recommend decreasing this
value. However, you may want to increase it, if your database server has more than 16 CPU threads.
MSS_MAXDOP_QUERY
This RSADMIN parameter sets the maximum number of CPU threads that are used for a single SQL
query as part of a BW query. The default value of MSS_MAXDOP_INDEXING is 2. The degree of
parallelism was chosen relatively low, since a single BW query typically results in many parallel
running SQL queries. You may want to increase MSS_MAXDOP_QUERY (to 3, 4 or 6), if your
database server has more than 16 CPU threads. However, you should never decrease it below 2.
The easiest way to set a RSADMIN parameter is to use SAP report SAP_RSADMIN_MAINTAIN. SAP
strongly recommends using this report, because table RSADMIN is buffered on the SAP application servers.
Manymanuals.com
Manymanuals.de
Manymanuals.fr
Manymanuals.it
Manymanuals.pl
Manymanuals.cz
Manymanuals.es
Manymanuals-pt.com
Kommentare zu diesen Handbüchern