When performing a Unicode conversion of an SAP system running on MS SQL Server database, you get pointed to the
Unicode Collection Note 1319517.
This note points to a MS SQL Server specific note (
Note 1054852 – Recommendations for migrations to MS SQL Server) which covers a couple of different scenarios, such as moving to SQL Server from another database provider, or just performing an R3load export/import (like me) to the same platform.
Now from experience, I know that the R3load import is actually pretty quick (especially on Oracle with DIRECTPATH). What takes the time is the index creation afterwards. Lots of scanning and I/O needs to be done. This is where you waste time, and where you could save time.
The note 1054852 mentions the use of the MAXDOP (Maximum Degree of Parallelism) SQL Server parameter that could benefit any subsequent index creation/rebuild tasks performed during an R3load import.
The recommendation in note 1054852 is to change the MAXDOP from the SAP recommended setting of 1, to 4 for the entire SQL Server instance. NOTE: The maximum MAXDOP in 2008R2 is 1024 (see here and SAP note 1654613).
This is the “hammer” approach and should be used with caution. There is a nice blog here on the use of MAXDOP with SQL Server. The blog shows how setting this to a value greater than 1 can actually increase fragmentation. This is completely understandable. However, the reader must note that the fragmentation is only an issue if the index is specifically set with ALLOW_PAGE_LOCKS to “OFF” (the default in 2008R2/2012 is “ON” (see syntax here)! ).
There is another blog article that shows how this fragmentation problem is overcome by setting the ALLOW_PAGE_LOCKS option. The article states that by default this is “ON”. However, according to Microsoft KB 2292737, the default is “OFF” by design.
So which is it? In fact, the MS syntax for “ALTER INDEX” specifically states that it is not possible to reorganise an index with ALLOW_PAGE_LOCKS set to “OFF”.
Here’s how to check the value of the ALLOW_PAGE_LOCKS setting for an index (there is no global setting):
use <SAP DB>
go
select name,type,allow_page_locks from sys.indexes
where allow_page_locks != 1
order by 1;
And the results… well, some of the largest SAP tables in my system have their indexes set with ALLOW_PAGE_LOCKS to “OFF“. Great!
|
NAME
|
TYPE
|
ALLOW_PAGE_LOCKS
|
| ARFCRDATA~0 |
1
|
0 |
| ARFCSDATA~0 |
1
|
0
|
| COVREF~0 |
1
|
0
|
| COVREF~001 |
2
|
0
|
| COVREF~002 |
2
|
0
|
| D010INC~0 |
1
|
0
|
| D010INC~1 |
2
|
0
|
| D010TAB~0 |
1
|
0
|
| D010TAB~1 |
2
|
0
|
| EDI40~0 |
1
|
0
|
| EDIDC~0 |
1
|
0
|
| EDIDC~1 |
2
|
0
|
| EDIDC~2 |
2
|
0
|
| EDIDC~3 |
2
|
0
|
| EDIDC~4 |
2
|
0
|
| EDIDS~0 |
1
|
0
|
| EDIDS~1 |
2
|
0
|
| EDIDS~2 |
2
|
0
|
| REPOLOAD~0 |
1
|
0
|
| REPOSRC~0 |
1
|
0
|
| REPOSRC~SPM |
2
|
0
|
| TRFCQIN~0 |
1
|
0
|
| TRFCQIN~1 |
2
|
0
|
| TRFCQIN~2 |
2
|
0
|
| TRFCQIN~3 |
2
|
0
|
| TRFCQIN~4 |
2
|
0
|
| TRFCQIN~5 |
2
|
0
|
| TRFCQIN~6 |
2
|
0
|
| TRFCQOUT~0 |
1
|
0
|
| TRFCQOUT~1 |
2
|
0
|
| TRFCQOUT~2 |
2
|
0
|
| TRFCQOUT~3 |
2
|
0
|
| TRFCQOUT~4 |
2
|
0
|
| TRFCQOUT~5 |
2
|
0
|
| TRFCQOUT~6 |
2
|
0
|
| VBDATA~0 |
1
|
0
|
| VBHDR~0 |
1
|
0
|
| VBMOD~0 |
1
|
0
|
I can understand the VB* tables might not want index locking and this is also hinted at in the older SAP R/3 Performance Tuning Guide for Microsoft SQL Server 7.0 web page. However, where did the other tables come from?
I took a look on SAP Notes but I was unable to find anything definitive. The system I was working on was recently copied and used SWDM (SAP Software Deployment Manager) to perform the post-copy steps, so it’s possible that the indexes were automatically adjusted (ALTER’d) to try and ensure a consistent approach.
What to do next? Well, some of those tables can be ignored since they are supposed to have ALLOW_PAGE_LOCKS set to “OFF”, some, like REPOLOAD are re-populated only during a system-copy with R3 tools (e.g. Unicode conversion), so you could try adjusting the setting during the R3load import.
The others, well in theory you would try to minimise data in those tables (like TRFC*) before you perform a Unicode conversion, so the indexes wouldn’t be massive anyway.
At the end of the day, all we are talking about here is a little fragmentation. So let’s move on.
Let’s go back to the MAXDOP setting recommendation mentioned in SAP note 1054852.
Now, my system happens to be a BW type system (it’s actually SEM, but this is just BW in disguise), so I found SAP Note 1654613 – SQL Server Parallelism for SAP BW which suggests that for SAP BW systems, you can now manage the MAXDOP parameter settings in table RSADMIN through report SAP_RSADMIN_MAINTAIN by setting parameters MSS_MAXDOP_QUERY, MSS_MAXDOP_<cubename> and MSS_MAXDOP_INDEXING.
The note 1654613 also goes on to say that by applying the note corrections (or the related support package stacks) the default MAXDOP for BW queries is set to 2, and for (process chain) index creations is set to 8.
Aha!
So the Unicode collection note’s setting of 4, could actually contradict the setting of 8 in BW systems!
The note 1654613 also states that you may increase the MAXDOP for queries to more than 4, but this depends on the number of worker threads configured in your SQL Server instance.
The worker threads setting in my SQL Server 2008R2 instance was set to “0”, which means the number is dynamically calculated (see here).
You can use the sp_configure procedure to check your setting:
sp_configure @configname='max worker threads';
select count(*) from sys.dm_os_threads;
You should also note the setting MSS_MAXDOP_INDEXING = 0 means that all available threads will be used during (process chain) index creation.
Summary:
We are advised to set MAXDOP to 4 when moving an SQL Server database or performing a system-copy or Unicode conversion.
However, more detailed analysis has shown that for BW systems specifically, we can potentially adjust the MAXDOP setting even higher than 4 during our R3load import, to ensure that we make the best use of the available CPU.
This is underlined by the fact that defaults within the BW system are sometimes higher than the recommended setting of 4.
Therefore, I will be trying out a value of 12 (default of 8 for index creation + 50%) in my Unicode conversion:
sp_configure 'max degree of parallelism', 12;
reconfigure with override
