Today we received an email that came from a Microsoft consultant who is working with a SAP customer moving the SAP landscape to Windows Server 2008. With this upgrade of the Operating System, the customer also moved a newer model of application server into the SAP ERP system. All application servers, except the new one were 4-socket Dual-Core AMD Opteron based servers. So more or less we are looking at 3-4 year old servers. The customer added a more recent version of a 4-socket Quad-Core Intel based server. So basically a server which is around 18 month in age and has double the number of cores in comparison to the other servers. The old servers are running on Windows Server 2003, the new one runs on Windows Server 2008. The customer did very diligent stress tests and started to complain quite a bit to our consultant. The customer was disappointed about the response times on the new server. According to ST03 those response times were even a touch worse than on the older servers with Dual-Core processors. In a first shot the customers saw Windows Server 2008 as the root cause for the fact that the response times measured in ST03 were not meeting expectations.
The explanation to this question is a classical one. It has to do with the metrics for standard benchmarks we are executing. The metrics always circle around number of transactions we execute and number of users, eventually considering a maximum response time. That is what usually gets published as ‘performance’. Reality is we are publishing throughput measures of a server. These describe the speed a processor core can execute a request only indirectly. Throughput of transactions or users simplified is a result of the number of cores or CPUs and the speed those CPUs or cores execute a request. In an ideal case, the result of doubling the # of processor cores or CPUs should result in doubling the throughput assuming that the speed of processing per core or CPU is the same. Sure there are scalability factors which usually end up giving a throughput increase which is more on the 1.8-1.9 level assuming the same speed of processing a request. But so much to the theory on the hardware. What about the SAP application side?
Let’s assume payroll calculation of a single employee. This will be a dialog step in a SAP dialog workprocess. The SAP workprocess will execute this process single threaded. Means the OS at any point in time can schedule this process only on one processor core or CPU. This is opposite to parallel query execution of a single query which Database systems can provide. In those cases a query gets partitioned and executed in parallel using multiple CPUs or processors cores. In the granularity of a SAP dialog step something like parallel execution of that dialog step is not possible. Hence the response time in the execution of this dialog step is solely dependent on the speed a processor core or CPU shows. The slower it is in processing the higher the response time will be.
Let’s apply all this to the customer case mentioned above. Result of the Benchmark was that the newer Quad-Core server was resulting in just 80% more throughput than the old Dual-Core servers. Hence we need to assume that the per core processing speed of these two different processors as best are on the same level, if not even a touch slower on the newer Quad-Core processors. This is more or less what the customer did get evidence about, when reading the data out of ST03. What ST03 unfortunately didn’t tell was the fact that the overall CPU resource consumption on the new server was drastically lower than on the old servers. However in order to capitalize on those, the customer would have needed to load the server higher. But purely looking at the response times per dialog step didn’t reveal the truth behind the better ‘performance’ aka throughput of the new server. It all depends how we define ‘performance’.
So if we want to make predictions on response time impact by new servers we always need to look up the benchmarks and correlate the throughput with the # of CPUs or processor cores. This will give a good idea on whether we can expect better response times or just better throughput.
Hello folks, I wish you all a Happy New Year 2010. I only wanted to point your attention to a blog about an improvement in SQL Server 2008 R2 which will reduce the probability of deadlocks and blocking lock situations in some scenarios which we encountered in SAP platform migrations and some other scenarios of mass data loads with dozens of parallel tasks. Since the description of the feature describes a very common scenario, we decided to publish the blog on the SQL Server Storage Engine blog under this link:
http://blogs.msdn.com/sqlserverstorageengine/archive/2010/01/18/improvement-in-minimizing-lockhash-key-collisions-in-sql-server-2008r2-and-its-impact-on-concurrency.aspx
Cheers Juergen
In the last article I announced Microsoft running productive on SQL Server 2008 R2. In this article I’d like to tell more about how this upgrade was done with 5min downtime.
Database Configuration of the Microsoft SAP ERP system
As described in an earlier article on this blog (http://blogs.msdn.com/saponsqlserver/archive/2008/03/28/how-does-microsoft-perform-backups-in-their-sap-system-landscape.aspx), the HA configuration is based on synchronous SQL Server Database Mirroring. This means there are three SQL Server instances which need to be upgraded. To cover an outage of the main datacenter there also is another SQL Server instance in our DR site. Means all in al,l we are looking into four SQL Server instances with three databases of a volume of around 5TB each. Only one of these databases is accessible (Principal of the Database Mirroring configuration). The databases on the other 2 instances are in a non-accessible mode since steady recoveries are executed.
What needs to be changed during a SQL Server In-Place Upgrade?
Let’s assume that the SAP Application already is on the correct Basis Support Package Level and also is running with the minimum necessary patch for SQL Server 2008 R2 (the same procedures also applies to SQL Server 2008 In-Place Upgrades). Focusing on SQL Server, the following components need to be upgraded:
· Client side connectivity layer of SQL Server (SNAC): Every new SQL Server release comes with a new SNAC layer, which can exist in parallel to older versions on a server. The SAP Database porting layer will choose the correct SNAC version dependent on the database it connects to. Therefore the SQL Server 2008 SNAC DLL necessary, can be installed on the application server while the system is up and running. The MSI installer package for SNAC10 can be found on the SQL Server installation DVD under <platform>\setup\<platform>. Look for the file sqlncli.msi. Just start the installation. It usually finishes within less than 1min and no reboot is required.
· SQL Server Management tools: In order to keep downtime low, most customers today stopped installing the SQL Management tools on all application servers. The only real SAP application server which requires some SQL Server Management tools actually is the server from which a SAP release upgrade is run. Only during a SAP release upgrade, SAP is using a component of the SQL Server Management tools. Hence for a normal SQL Server release upgrade, the only servers where the SQL Management tools need to be installed on are the database servers. And that is done with the SQL Server In-Place upgrade
· SQL Server Relational Engine, SQLAgent, SQL Browser, SQL VSS Writer: These are the components which usually run on the database servers and which will be upgraded in the SQL Server In-Place upgrade.
Sequence of Upgrade in Microsoft’s SAP ERP configuration
The goals of the upgrade from SQL Server 2005 to SQL Server 2008 and now to SQL server 2008 R2 were:
· To have minimal impact on HA and DR configuration: Up to SQL Server 2005, SQL Server did not support the upgrade of non-accessible databases. This meant that during each Upgrade logshipping or secondary databases in a database mirroring setup were lost and had to be rebuild from a backup after the upgrade of the primary was finished. During the time of the upgrade of the primary and the restore of the backup on the mirror and logshipping databases the system was exposed with only 1 version of the database available.
SQL Server 2008 and SQL Server 2008 R2 finally allow an In-Place upgrade of in-accessible databases and thus eliminate the problem of the extended time of exposure.
· Minimize the downtime during the SQL Server upgrade to a few minutes (downtime depending on release and target version of SQL server).
So the sequence of steps executed two weeks ago looked like:
· One day before the actual downtime for the upgrade was taken:
o Suspend SQL Server Log-Shipping
o In-Place Upgrade of the Log-Shipping instance
o Resume Log-Shipping – The transaction log backups of a lower SQL Server release can be applied by SQL Server 2008 R2 (or SQL Server 2008) to a passive database
o Install SQL Server SNAC10 on all application servers
o Disable automatic failover in the database Mirroring configuration
o Execute the In-Place upgrade of the SQL Server instance which functions as Witness in the DBM configuration
o Enable Witness again – The SQL Server 2008 R2 (or SQL Server 2008) witness can guard over a DBM configuration of a lower SQL Server release
· Some hours before the downtime is taken:
o Suspend Database Mirroring
o Execute the In-Place Upgrade of the Mirroring instance
o Resume Database Mirroring
o Before downtime is taken, make sure that the principal and the mirror instance are synchronized
· Downtime phase:
o Stop the SAP application
o Execute a manual failover. This will initiate the following actions:
§ Since the failover goes from a lower to a higher SQL Server release, Database Mirroring is getting suspended
§ The database is opened. Despite the upgrade to SQL Server 2008 R2 (or SQL Server 2008) the passive database schema is still on the state of the source version of SQL Server. This is changing in this activity. Now the SQL Server system tables of the SAP database are upgraded to the state of SQL server 2008 R2 (or SQL Server 2008). Experience wise this takes around 8-10min in the case of upgrading from SQL server 2005 to SQL server 2008 and less than 5min in the case of upgrading from SQL Server 2008 to SQL Server 2008 R2.
§ After the schema upgrade is finished, the database opens. This is the moment the SAP CI and application instances can connect against SQL Server again
§ In case of upgrading from SQL Server 2000 or SQL Server 2005 to SQL Server 2008 or SQL Server 2008 R2, one needs to run the SAPTools for MSSQL Server as described in the SAP upgrade documentation on page 20. However this steps usually takes a few minutes only
§ If this all works fine, the downtime is finished essentially after 5-15min
· After Downtime:
o Ensure Log-Shipping from the new principal to the Log-Shipping destination is working – All changes which were done to the SQL Server system tables in the SAP database are recorded in the transaction log. Hence those changes become part of the next transaction log backup, which gets copied to the Log-Shipping destination and then restored to the SAP log shipping database on that instance
o Now is the time to get former principal upgraded. Since the SQL Server Management Tools are upgraded as well, this step could take as long as 20-25min. But SAP can be running w/o impact during this upgrade since is connected to the new principal server.
o After this step is successfully finished, Database Mirroring is getting resumed. The old principal will realize that there is a new principal and that it now is in the role of a mirror. Changes applied to the new principal will be synchronized with the ‘new’ mirror. – As with the Log-Shipping destination, changes done to elevate the SQL Server system tables to the more recent SQL Server release are recorded in the transaction log and now via DBM get applied to the database which before still was on the source SQL Server state.
o If both instances are synchronized, enable the Witness again in the DBM configuration
Around two years ago when our SAP Basis team practiced this sequence for the first time, our down time was around 10 min before the SAP CI could connect again. In the current case moving from SQL Server 2008 to SQL Server 2008 R2, the downtime for our SAP ERP was less than 5minutes. Reason is that there hardly are any changes on SQL Server system tables going from SQL Server 2008 and SQL Server 2008 R2
Using Windows Clustering instead of SQL Server Database Mirroring, Rolling Upgrades also are possible. Documentation which covers that case can be found on: http://msdn.microsoft.com/en-us/library/ms191295.aspx
Two weeks ago Microsoft’s SAP ERP system went productive on CTP2 (also named August CTP) of SQL Server 2008 R2. This move represents the most important step of 3 months testing of SQL Server 2008 R2 in sandbox systems, development and test systems. As throughout the whole testing period, the move to production was eventless. No issues have been detected so far in production. From SAP side Basis Support Package 18 does work with SQL Server 2008 R2 without any problems. Also patch level 221 of the 7.00 SAP kernel works perfectly without any issues. Not too surprising since the changes in the SQL server Relational Engine are very limited. The motivation for Microsoft IT going productive with SQL Server 2008 R2 as early as possible was twofold:
· As usual with new SQL Server Releases, our SAP team wanted to contribute to the success of SQL Server by testing the new release on one of Microsoft’s most important software systems. We did the step of moving our SAP ERP productive with Beta coding of SQL Server for the last 11 years. The philosophy behind it is that if our own IT wouldn’t be able to run new SQL Server releases with our most important business systems, how could we ask customers to do so? The bar is that we even need to be able to do it with pre-released SQL server software. From development side, the step of taking our SAP ERP productive with pre-released coding is an important milestone and data point to determine the quality of our product.
· The other motivation for our SAP basis team was disk space considerations. We hope to slash another 15-20% disk space of our 5TB volume in the SAP ERP database by leveraging UCS2 compression as introduced with SQL Server 2008 R2. Looking back considering the fact that we have been at a volume of 6.5TB already before we started with SQL Server 2008, one can state that database compression saved us tremendous money.
As far as our development of SQL Server 2008 R2 goes, we are on track to release in the middle of 2010.
Happy Thanksgiving
Given that our own SAP Basis Team is pretty aggressively working with SQL Server 2008 R2 and plans to go productive on CTP2 of SQL Server 2008 R2 in 5 weeks from now, I needed to rework the stored procedure sp_use_db_compression a bit. As I described in the former blog article, the way to get ALL data of a table into UCS2 compression which has been compressed under SQL Server 2008, is to rebuild the clustered index. So far the logic of sp_use_db_compression didn’t allow rebuilding an index with the same type of compression. This was more or less a step of optimization to safe resources and time. Hence for the specific case of rebuilding indexes in the same type of compression I introduced the option ‘force_rebuild’. This option is required in order to rebuild indexes with the type of compression the indexes already are compressed in. So the typical case for SQL Server 2008 R2 where one wants to rebuild many indexes and tables with the same compression type to get ALL data into UCS2 compressed format, this option would be required. A typical call of the procedure could look like:
sp_use_db_compression ROW, @maxdop=1, @online= 'ON', @verbose=1, @force_rebuild=1
Default for the new option is 0 and hence not to rebuild indexes which already are compressed in the requested type. I also did a bit code clean-up and fixed some smaller bugs. Microsoft’s own SAP Basis team will use this procedure pretty soon since we want to get the database smaller again. After archiving it currently is around 5.1TB completely ROW level compressed data plus three tables Page compressed. Once on SQL Server 2008 R2, we will go through all tables again in order to get the benefit of UCS2 compression. We think that we’ll get the data volume down to less than 4TB again.
Have fun Juergen
Attachment(s): sp_use_db_compression_23.zip
I described UCS2 compression in the last article. It actually didn’t miss on questions on it as reaction of the blog. So let me try to address two of the questions in this blog
One of the questions was how one would be able to detect whether UCS2 compression is used? The best answer one can give sounds like: For the introduction of UCS2 compression two of the goals were to have UCS2 compression transparent and the immediate usage of it on tables which were compressed either ROW or PAGE Dictionary as soon as the database is running under SQL Server 2008 R2. This resulted in the fact that there was no sense in having an extra indicator for UCS2 compression in one of the system tables. The fact that a table is ROW or PAGE compressed already states that UCS2 compression is used when running on SQL Server 2008 R2.
The answer given above immediately triggered the second question: ‘How do I get the data which had been compressed under SQL Server 2008 UCS2 compressed’. The answer is: All data which will be modified in a compressed table under SQL Server 2008 R2 will be stored in UCS2 compressed fashion. However this doesn’t take care of the data which is read only since it might be older fiscal data or payroll data. The only way to get the contents of these tables completely into UCS2 compressed format is to rebuild the clustered index on those tables. As with deploying database compression originally under SQL Server 2008, one would need to rebuild the clustered index on SQL Server 2008 R2 to get ALL the data into UCS2 compressed form.
Cheers out of Wells, Nevada where I have an overnight stop on my drive to Phoenix, AZ to attend SAPTechED
We just released CTP2 of SQL Server 2008 R2. What is this CTP? Think about it as Beta2 of SQL Server 2008 R2. There will be another CTP which should be released in the last quarter of this calendar year. Final release time frame should be very early in the second half of next calendar year. As a background, the R2 version of SQL Server 2008 only has minimal changes in the SQL Server Relational Engine. Most of the changes have to do with new components in the overall SQL Server package. However there is one change in the Relational Engine which could have significant impact on TCO. As most of you know, SQL Server stores Unicode strings in columns of the datatype nvarchar. As encoding we use UCS2. This encoding usually stores every character in 2 bytes. This is different to other encodings like UTF8 where characters out of single byte code pages get stored as one byte on disk. (However on the other side UTF8 can use up to 4bytes in typical double byte collations like Kanji.) – The current solution had room for improvement: Save space for characters out of single byte code pages.
The goal we set ourselves for SQL Server 2008 R2 looked as follows:
· Spend one byte of disk storage for single byte collations like the typical Latin1 collation
· Improve storage efficiency even for double byte character collations
· Make the usage of the new way of storing nvarchar content completely transparent. Means beyond the usage of either ROW or PAGE compression, no further steps need to be applied.
· For already existing ROW or PAGE compressed tables in databases which were attached or restored from a SQL Server 2008 source newly inserted or modified tables will be stored in the new format even in combination with row in the old format on the same page
· Performance impact shouldn’t be measurable in productive scenarios
In order to achieve our goal, we implemented a new way how we actually store our UCS2 encoded nvarchar datatypes on disk. The algorithm applied is widely known as SCSU (Simple Compression Scheme for Unicode). It is applied at the moment a row is placed on a page and it is absolutely transparent to the application and other layers of SQL Server. We achieved the goal of making the usage transparent with coupling our UCS2 compression with ROW and PAGE compression. Means if one chooses to compress a table in SQL Server 2008 R2 with ROW or PAGE compression the new UCS2 compression comes with it automatically. Not using any compression against a table also doesn’t give any Unicode compression. So we implemented UCS2 compression as an extension of our database compression features we implemented already in the SQL Server 2008 release.
In terms of performance impact I spent a lot of time over last Christmas testing based on the SAP SD benchmark and some examples of typical BW databases. One really can say that the impact of the UCS2 compression on top of our existing compression methods is in the extreme low single digits. This is basically a change that in a productive system isn’t really recognizable. These tests and measurements conducted with other workloads, including standard TPC based benchmark workload, gave us enough confidence to simply couple the new UCS2 compression with our existing methods because the increase on CPU consumption proofed negligible.
So what is the benefit we measured in some of the comparisons? See this table:
|
Locale |
Pure UCS2 in SQL Server 2008 |
UTF-8 |
UCS2 compression in SQL Server 2008 R2 |
|
English |
1 |
0.5 |
0.5 |
|
Kanji |
1 |
1+ |
0.85 |
|
Korean |
1 |
1 |
1 |
|
Turkish |
1 |
0.53 |
0.52 |
|
German |
1 |
0.5 |
0.5 |
|
Vietnamese |
1 |
0.68 |
0.61 |
|
Hindi |
1 |
1 |
0.5 |
What does this really mean for a SAP ERP system? In order to answer this question I took a copy of our own 5+TB SAP ERP database. I completely defragmented the database and did a series of exercises which ended up with the results presented in the following table:
|
Compression Type |
Percentage of original |
|
No compression |
100% |
|
ROW Compression SQL Server 2008 |
83.35048047 |
|
ROW Compression SQL Server 2008 R2 |
64.28181983 |
|
Page Compression SQL Server 2008 |
46.35964575 |
|
PAGE Compression SQL Server 2008 R2 |
44.05743641 |
Please keep in mind that the percentages are based on a completely reorganized database. In real productive systems we usually see a larger gain due to the compression plus additional effects of data reorganization when enabling database compression. A many of our customers already confirmed, ROW compression didn’t introduce a measurable more on resource consumption and already reduced the volume by a nice portion. Using SQL Server 2008 R2, the effect will even be larger: The used space within the database will basically shrink to 2/3 of its original size; without measurable increase on CPU resource consumption. As expected with Page Level compression the benefit of UCS2 compression is not as significant since Pre-Fix and duplicate optimizations already do a great job. These are measurements conducted with compressing the clustered indexes (which include the data layer) only, as supported by SAP at this point in time. We will work in the next 12 months to get the non-clustered indexes compressed as well. Especially with Page Level compression we will see another dramatic impact on database size.
Please see my next article explaining more about how one can use UCS2 compression and apply it to already existing ROW/PAGE compressed tables after running the database on SQL Server 2008 R2.
First of all apologies for not being more active on our blogs site for the last two months. Reason is that we are in the usual annual review phase and also went through a reorganization. Therefore I was pretty busy.
Nevertheless I was able to spend some time improving the stored procedure one can use for applying SQL Server Database Compression to SAP or non-SAP databases. Attached to this blog you can find the new release of the sp_use_db_compression script where I fixed one bug and added another feature. Working with a customer, it figured out that the procedure didn’t work when it was created in the dbo schema and the SAP schema was in the <sid> schema. Even the option @schema set to the value ‘ALL’ did not work. This is corrected in the new version attached.
There was another request from one of our customers. They wanted to have all the T-SQL commands for executing compression stored in a table. But they didn’t want to have the compression executed. So what I implemented is a new option called @write_verbose_table. Setting this option to a value of 1 and also having the option @verbose_only set to a value of 1 will go through the tables, just create the T-SQL commands and write all the commands into a table sp_use_db_compression_sql_commands. This table is created in the same schema the stored procedure sp_use_db_compression resides in.
There will be another version of the procedure within the next two months. The procedure will deal with a specific case for SQL Server 2008 R2. But more about SQL Server 2008 R2 in the next Blog entry
Attachment(s): sp_use_db_compression_22.sql
We finally made it to the finish line with the new Whitepaper describing SAP applications running on SQL Server 2008 and SQL Server 2005. Part of the reason why I was so silent on the blog over the last months certainly can be attributed to the effort in finishing his paper. We basically took the existing paper in its second version and extended it by changes which are needed for SQL Server 2008. We also went pretty deep into describing new SQL Server 2008 features. However we also decided to split the original paper into 3 parts. We already started writing at the second part which will be all around Availability and Disaster Recovery. Part 3 will finally cover Performance Monitoring and aspects of Performance Tuning. The new paper can be downloaded from here:
http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SAP_SQL2008_Best%20Practices_Part_I.docx
Thanks to all the folks who contributed with content and reviewing. Have fun in reading it.
Cheers Juergen
We already discussed when an execution plan is created and how long it is valid. We also had a look on the factors which have an influence on the execution plan. SAP almost always executes parameterized statements on SQL Server. For details see:
http://blogs.msdn.com/saponsqlserver/archive/2009/02/09/sql-execution-plans-part-1-sql-statement-execution-on-an-sap-system.aspx
http://blogs.msdn.com/saponsqlserver/archive/2009/02/22/sql-execution-plans-part-2-sql-compilation.aspx
Figuring out the execution plan of a long running SQL statement in an SAP system is much more complicated than one would expect in the first instance.
There are several places in an SAP NetWeaver system, where you can see SQL Server execution plans. The most famous place is the SAP SQL trace. Since this trace is started using SAP transaction ST05 it is often simply called ST05 trace. Every single ABAP programmer knows how to use this trace and to see the execution plan of a traced SQL statement. However, almost no one is aware of the fact, that you will always get an estimated execution plan based on the parameters submitted to the queries while the traces was running. The actual used execution plan is only visible in the DBA Cockpit (SAP transaction DBACOCKPIT). You can either see the actual execution plan of a currently running request or an execution plan which is stored in the SQL Server statement cache. In the DBA Cockpit you can also start a SQL Profiler trace (including the execution plan). Due to the performance overhead of a SQL Profiler trace we typically do not recommend this.
The user interface of the SAP database monitor has changed a lot over the years. In the following we will have a closer look at SAP transaction DBACOCKPIT, which is available in NetWeaver 700 basis support package 13 and newer SAP releases. However, the concepts described here also apply for older SAP releases and for transaction ST04 (the predecessor of DBACOCKPIT).
SAP SQL trace (ST05 trace)
A ST05 trace can be activated per SAP instance (application server), SAP user and database table. The trace is running on the SAP application server and storing all executed SQL statements in a file. It contains the SQL statement, the actual used parameters, the number of rows returned and the time needed to run the statement in microseconds. The measured time includes all kind of waits, such as I/O, blocking database locks and network traffic. The ST05 trace is very popular because the user interface is identical for all database platforms supported by SAP (SQL Server, Oracle, DB2, Informix, MaxDB). An ABAP programmer can perform a ST05 trace without having a deeper knowledge of the underlying database system. However, in order to check and understand a SQL Server execution plan you actually need to get a better idea how ST05 works for SQL Server.
Let’s have a closer look how a ST05 trace looks like. One great feature of a ST05 trace is the summarization function. You can easily figure out the SQL statements with the highest overall time, time per execution or time per row returned:
When pressing the “Explain” button an estimated execution plan is displayed. Therefore SAP creates temporarily a stored procedure for the traced SQL statement, using a SP name starting with sap_XPL. Then the stored procedure is executed with the traced parameters, using a database session with option SET SHOWPLAN_XML ON. As a result, SQL Server returns a XML execution plan for the query. This estimated execution plan is not necessarily identical to the execution plan used by SQL Server when the traced SQL statement was originally executed. It is the plan SQL Server would create now, considering the used parameters and the actual index statistics.
The XML execution plan is parsed by SAP in order to create the “Explain Tree” as you can see in the screenshot above. This kind of display is often better readable, since you can collapse branches of the tree individually. Alternatively you can display the execution plan as plain text by changing to the “Text Explain” tab. By pressing the “XML” button the original XML explain is opened using Internet Explorer (or the application which is associated with the .xml extension). You can even display the execution plan graphically using Management Studio. When pressing the “SSMS” button, SAP stores the XML plan as a file with the extension .sqlplan and opens it with SQL Server Management Studio which is installed on your computer:
“SQL statements” in DBA Cockpit
Using the SAP transaction DBACOCKPIT you can easily access the runtime statistics of all parameterized SQL statements and the currently cached execution plan. SAP retrieves this information from SQL Server’s Dynamic Management View (DMV)”sys.dm_exec_query_stats”.
When opening the screen “SQL statements” in DBA Cockpit you will get a list with the 300 most expensive SQL statements regarding the total elapsed time. Changing the number of rows or the criteria results in re-reading the list from SQL Server’s DMVs. Therefore a specific SQL statement may not be in the result list any more after changing the criteria from “Total elapsed time” to “Average Logical Reads”.
DBA Cockpit can use the standard list functionality of SAP. When clicking on the icons in the list above, you can sort by any column or apply an additional filter on the current list (without re-reading the data from SQL Server). After sorting and filtering the screen may look like this:
By pressing the “Explain” button the XML execution plan is read from the SQL Server statement cache (procedure cache). This is the execution plan which is currently in use. If you want to know how long it has been in use, then you can simply look at the last column of the list. This column called “Comp date” contains date and time of the plan creation (compilation). The SQL Server statement cache is typically large enough to keep most execution plans for days or weeks.
The user interface in SAP for the SQL explain is the same in ST05 and DBA cockpit. Per default the execution plan is displayed as an explain tree. You can use Internet Explorer to see it as plain XML or use SQL Management Studio to get a graphical view of it. The used explain method is explicitly mentioned. When clicking on the tab “SQL Code” you will get the SQL statement and the parameters which were used during the compilation. These parameters are extracted by SAP from the XML execution plan.
“SAP SQL Statistics” in DBA Cockpit
In SQL Server 2005 and newer releases you can easily figure out expensive SQL queries. They can be queried from the SQL Server statement cache using DMVs. In older SQL Server releases there was no feature like this. Therefore SAP implemented its own statement cache in the SAP database interface for OLEDB. The cache is used to collect statement execution statistics on the application server. In DBA Cockpit you can see the SQL Server statement cache in “SQL Statements” and the SAP statement cache in “SAP SQL Statistics”. Since it is not needed any more, the SAP statement cache was removed for SQL Server 2008 and newer releases. Therefore “SAP SQL Statistics” is grayed-out in DBA Cockpit for these SQL releases.
Each SAP instance (application server) has its own statement cache, containing statistical information like the number of executions and min/max/average execution time per query. Since this data is measured by SAP, it cannot include the number of physical/logical reads per query or the used execution plan. The size of the SAP statement cache is configurable, but has a fixed size. Therefore a query may be flushed out of the SAP statement cache. The statement caches of the SAP instances are independent from each other and from the statement cache in SQL Server. Therefore a particular query may be found in the SAP statement cache and not in the SQL Server statement cache, or visa verse.
You can reset the “SAP SQL Statistics” per SAP instance. This is useful for tracing a long running SAP batch jobs without the overhead of a SQL Profiler trace. Unlike the SQL Server 2005 DMVs, it contains the number of rows selected per statement.
The “SAP SQL Statistics” has the same user interface as in “SQL Statements” and ST05 to display the execution plan of a query. However, you should be aware that this is an estimated execution plan. It is created using the parameters stored in the SAP statement cache of the current SAP instance. The SAP statement cache contains the parameters of longest execution for each query. If there is a problem with a changing execution plan caused by changing parameters then you typically will not see the right execution plan here. Therefore you should search for the same query in “SQL Statements” and check the execution plan there.
“Database Processes” in DBA Cockpit
To check the current activity on SQL Server you can use “Database Processes” in DBA Cockpit. For SQL Server 2000 you can see a list with the content of the SQL Server system table “sysprocesses”. For SQL Server 2005 and newer a join of the DMVs “sys.dm_exec_sessions”, “sys.dm_exec_connections” and “sys.dm_exec_requests” is displayed. You can filter by SAP system (using the SQL Server login), application server and SAP work process number. Each SQL Session contains the total number of physical reads, writes and consumed CPU time. Using the “Reset” and “Since Reset” buttons you can easily see which SQL session is currently consuming CPU.
If you want to check the execution plan of a currently running query then you should first press the button “Active Requests”. This filters the output list in DBA cockpit. As a result, only active SQL requests are visible. After this you can display the currently used execution plan of an active request by pressing the “Explain” button.
As long as you do not press “Active Requests” you can see all SQL sessions and connections, including those which do not have an active request. For those connections you also see a SQL statement. However, this is not an actual running statement. It is the most recent statement which ran in the past. Since SQL Server does not have a valid plan handle any more for those SQL statements, you can only create an estimated execution plan for them. Therefore the “Explain” button for the most recent SQL statement is not available any more in the newest release of DBA Cockpit.
Best practices for SAP NetWeaver 700 and newer
The easiest way to find slow SQL statements for an ABAP programmer is the ST05 trace. A database administrator should check “SQL Statements” in DBA Cockpit. For SQL Server 2000 you should use “SAP SQL Statistics” instead.
The best way to figure out an execution plan is “SQL Statements”. For SQL Server 2000 (and SAP NetWeaver 700) you can only be sure about the execution plan when performing a SQL Server profiler trace. For supportability reasons we strongly recommend to upgrade SQL Server 2000 to a newer release when running SAP NetWeaver 700 (or newer).
I just wanted to point all of you to a case study we just released about our customer Quanta out of Taiwan. It is a great write-up on how one of our customers used SQL Server 2008 Database Compression to reduce their disk space usage by large extents. It also is one of the first customers using Page Dictionary Compression in their productive SAP ERP system.
Please enjoy the read here: http://www.microsoft.com/casestudies/casestudy.aspx?casestudyid=4000003962
Have fun
Since the 'Green-IT' movement is becoming serious, leverage of server resources or power consumption of existing resources is right in the middle of attention. One of the things analyzed is how much CPU is used on each of the servers and whether one could load a little bit more onto a server when one finds sufficient free CPU resources. Another angle people start looking into is the memory consumption of servers. As shown here, analyzing servers with the SAP Netweaver stack on memory usage is not that easy as it seems from the outside. When the analysis is done by people who are less familiar with the SAP implementation, the SAP Basis team suddenly could find itself confronted with the statement that there hardly is any memory used by the SAP application instances. As it happened with one of our customers. The folks performing the analysis sent the SAP Basis Team statistics stating that out of the 64GB real memory there were steadily 54GB memory available according to Windows Performance Monitor counters. Something that the SAP Basis Team knew that it could not be true. But how to show the truth? Here is how you show the real memory consumption and what the traps are, people are falling into.
In Windows Server 2003, the Windows Task Manager is partly right and partly wrong. Reason for this is the way how SAP creates their central part and largest chunks of memory. The so called Extended Memory is created either as as memory mapped file or as a huge chunk of shared memory (PHYS_MEMSIZE parameter in the SAP instance profiles). The memory defined as Memory Mapped File or Shared Memory is not subtracted from the 'Available Memory' in the Windows Task Manager (see below) or the Windows Performance Counter with the same name (Perfmon--> Memory -->Available Bytes or Available KBs or Available MBs). Let’s look at a screenshot of the Task Manager and go through a few of the numbers
We are looking at a server with 64GB real memory (Physical Memory block - 'Total'). However the row 'Available 'claims still to have 54GB available despite the fact that there are two SAP ABAP instances with 12GB Extended Memory each. Reason for this discrepancy is that 'Available' is calculated by subtracting memory associated to processes from the real memory. Both memory mapped files and shared memory as used by SAP are not completely associated to a process. Hence only fractions of it are taken into account. Nevertheless the memory configured for the SAP instance needs to be taken into account. This can be done by using Performance Monitor and focus the monitoring on Memory--> Committed Bytes. This counter will correctly reflect the memory allocated for the SAP instances and might show like in the case above around 37GB. These 37GB also are reflected in Task Manager under the graph of PF Usage (Page File Usage) or in the block 'Commit Charge' in the lower left corner of Task Manager. Both are giving the accurate numbers on real memory consumption and are the numbers of interests. If the peak value of committed memory exceeds the real memory, then it indicates that there were situations where Windows had to page out memory of processes into the page file in order to make space for other processes in real memory. Something which can hurt performance. Especially on SAP JAVA application instances. Ideally we don't see the Peak number of committed memory going beyond the real memory of the server.
Unfortunately Windows Server 2008 did take a step back in showing how memory is consumed
Would one believe it, we are running 2 SAP ABAP instance on this system. Every instance has 5GB Extended Memory. Instead of ‘Page File Usage’, the ‘Physical Memory Usage’ is displayed now. This one unfortunately is not based on the committed bytes and hence doesn't count the shared memory segments and extended memory of SAP ABAP instances completely. Besides the performance Counter 'Committed Bytes', the correct numbers in the W2K8 Task Manager are in the last item in the 'System' block. It is called ‘Page File’. The first value of ‘Page File’ gives you the REAL committed memory and the second gives you the maximum which can be committed (real memory plus page file(s)). Again the Perfmon item ‘Committed Bytes’ is the one to monitor.
That only is in regards to application servers without SQL Server. With SQL Server we wouldn't confuse any of those values above since the memory allocated by SQL Server steadily is attached to a process. However where confusion kicked in was when looking on the memory associated with the sqlservr.exe process in the Processes pane of the Windows Task Manager. SQL Server leveraging Large Pages on 64Bit or AWE under 32Bit has the effect that you can’t see how much memory SQL Server uses when you are looking in the ‘Processes’ tab in Task Manager. See this screenshot:
It is not apparent that on this dedicated Database Server the SQL Server process on this system allocated around 58GB for SQL Server's Buffer Pool. A way how to find that out is to check these Performance Monitor Counters:
Memory --> Committed Bytes or Memory --> Available MBytes in the case of SQL Server and the way SQL Server allocates the memory, these counters indeed work perfectly.
SQL Server --> Buffer Manager --> Total Pages will also show the size of the SQL Server Buffer Pool (however not the memory SQL Server allocates outside the Buffer Pool)
Whereas the SQL Server allocations are honored in the Page file Usage Section of Task Manager in Windows Server 2003 and Physical Memory Usage in Task Manager for Windows Server 2008
Hope this helps a bit to evaluate Memory Usage of systems running SAP software.
Lately we had a strange incident when one of our customers for more than 10 years tried to apply SQL Server 2008 Database Compression against the SAP ERP test system. The history of the system is that it first got installed against SQL Server 7.0. The customer was extremely early moving to SQL Server 2000. They also moved very fast to SQL server 2005 and now are in the process of upgrading to SQL Server 2008. Over those years that system saw quite a few SAP release upgrades as well. One of the driving forces for the upgrade was SQL Server 2008 Database Compression. The system is multiple Terabytes in size, so the space savings could be significant and hence investments in storage could be reduced.
As one of their SAP Basis Administrators tried applying ROW Compression for all the tables in the test system, he ran into this failure on SQL Server side:
Msg 21, Level 22, State 1, Line 1
Warning: Fatal error 682 occurred at Feb 4 2009 5:47PM. Note the error and time, and contact your system administrator.
The error occurred while trying to compress the table MARC. So they tried to manually apply ROW compression on the table with this command:
ALTER TABLE tr3.MARC REBUILD WITH (MAXDOP=8, ONLINE=ON, DATA_COMPRESSION = ROW);
Again the same error. Looking deeper into the problem, it figured out that there seemed to be extreme large values in a minor number of rows in the columns GLGMG and VKGLG columns of table MARC. Columns which were not even in use by their SD functionality. The values in those columns were in the Billions ('Milliarden' in Germany). Completely unrealistic numbers. After correcting these numbers to the default value of 0.0 everything went fine.
But what was the problem? How did these numbers get into the system. It was Sven, the SAP Basis Administrator who tried to apply compression, who found OSS note #425946 describing exactly what the problem of the big numbers. The problem could have happened with SQL Server 2000 RTM and SP1. So far the effect of the issue only has been seen on table MARC in the two columns GLGMG and VKGLG. Hence not a worry at all for customers whose systems never ran on SQL Server 2000 RTM or SP1. For those people who want to apply Database Compression to SAP systems which did run on SQL Server 2000 RTM or SP1, we would recommend to check the values which are in those 2 columns of table MARC before going ahead with compression.
Thanks a lot to Sven Otromke for working with us and finding the OSS note.
Today we want to have a closer look how an execution plan for a given SQL statement is created within SQL Server and how this affects an SAP system. The idea of this discussion is not to highlight in detail the phases of the plan creation. The intention is to give an SAP administrator or ABAP/JAVA programmer an idea, which factors have an impact on the execution plan.
The component of SQL Server which creates the execution plan is called (query) optimizer. The procedure of creating an execution plan is called compilation. Once a new plan is re-created for an existing query it is called a re-compilation. The most important decision which has to be made by the optimizer is to decide which database indexes are used and how they are used. SQL Server may perform one or several index seeks. In some cases an index range scan on the index may be even faster. For JOINs the optimizer has to decide the join method (nested-loop, merge-join, hash-join) and the join order.
A compilation can be a very time consuming operation. That’s the reason why SAP uses stored procedures or parameterized statements, which allow SQL Server to re-use an existing execution plan. However, a new execution plan has to be created once no valid execution plan exists:
· After restarting SQL Server.
· After clearing the procedure cache using DBCC FREEPROCCACHE. This command can also be executed in the SAP transaction DBACOCKPIT.
· Once an execution plan is flushed out of the SQL Servers statement cache due to memory pressure. This can happen at any time.
· For SAP applications running 4.6D kernel or older kernel releases, once the name of a global temporary stored procedure is flushed out of SAP’s stored procedure (SP) name cache. This can happen at any time. As a result SAP creates a new temporary SP which a different name. SQL Server will then create a new execution plan for the new SP.
· For SAP applications running 4.6D kernel or older kernel releases, once the connection is closed, which originally created a global temporary SP. When SAP restarts a work process, all its database connections are certainly closed. As a result the dependent SPs will be dropped. SAP has to re-create these SPs and SQL Server has to re-create their execution plans.
An existing execution plan may become invalid:
· After updating the index (or column) statistics, which were relevant for the query optimizer during plan creation. It makes no difference here whether this is an automatic or manual update statistics.
· Once the structure of the table changes. This includes creating or dropping an index.
· Once the SP executing a query or a table accessed by the query is explicitly marked for recompilation. This can be done using the system stored procedure sp_recompile. It is not recommended to mark a table for recompilation, because a “sp_recompile <TABLE NAME>” holds and requests database locks. We have seen blocking situations for hours at SAP systems caused by this.
· For SAP applications running 6.40 kernel or older kernel releases, If the SAP profile parameter “dbs/mss/max_duration” was set. Whenever the average runtime of a query exceeded the specified threshold, SAP executed a “sp_recompile <PROCEDURE NAME>” for the stored procedure taking too long. This parameter caused rather trouble than solving issues. Therefore it was removed from the SAP kernel.
Generally speaking, the decisions made by the query optimizer are based on the estimation of the number of rows selected and cost calculations (CPU/memory/IO needs) for the potential execution plans. There are several factors which have an influence on the chosen execution plan. Let’s have a closer look on them:
· Existing indexes
This is the most obvious factor. Without a proper index you cannot expect a fast query execution. In the other hand, having too many indexes is much more often an issue on an SAP system. This may result in unstable (changing) execution plans. Inserts and deletes become more expensive the more indexes you have. Defining proper indexes is therefore often a compromise. You have to find a balance between speeding up a single query and minimizing the impact on the other queries. Typically you therefore create a few combined indexes to tune your most important queries running in dialog. Queries running in a batch job are often not expected to have optimal performance.
· Clustered index property
SAP (almost) always creates the primary key as a clustered index on SQL Server. You can have at most one clustered index per table since the data is physically sorted by the clustered index. Having a clustered primary key has several advantages and reduces the cost for using the primary key. All other indexes are non-clustered and called secondary indexes. A secondary index stores the clustered index key (which typically does not change) rather than a physical pointer to the data row. This results in better update performance. On the other hand you have to traverse two index trees when using a secondary index: First of all the index tree of the non-clustered index to receive the clustered index key and then the clustered index to access the data row. In a nutshell, having a primary clustered key in SAP reduces the cost of using the primary key and increases the cost of using a secondary index. In extreme cases a secondary index will not be chosen by the optimizer due to higher costs of accessing the secondary index.
· Index statistics
The index statistics of SQL Server basically consists of the density information of the combined n index columns and a histogram of the first index column. A density is the reciprocal value of the number of distinct values. If a combined index consist of three columns A, B and C then the index statistics contains the density of (A), the combination of (A,B) and the combination of (A,B,C). The histogram is only available for column A. It contains a sample of up to 200 values and the number of rows which have this value. If required, SQL Server automatically creates additional column statistics including a histogram for other columns, too. However, the database option “auto create statistics” is to be set for it, as recommended by SAP.
Having not up-to-date index statistics is the most common suspect when seeing bad query performance. However, on an SAP system the index statistics are typically innocent. SAP strongly recommends to turn on the database options auto update/create statistics. This results in automatically updated statistics and works pretty well on an SAP system. There are only a few scenarios where SAP recommends performing a manual update statistics in addition. One example is a client import of a relatively small client (column “MANDT” means “Mandant” in German) into a huge SAP database. The same may be necessary when creating a new company code (column “BUKRS” means “Buchungskreis” in German) on a system which only has a few distinct customer codes.
Updating the statistics only makes sense when the selectivity of the updated columns changes significantly. For example, the document number (column “BELNR” means “Belegnummer” in German) is almost always very selective. Even when updating a one year old index statistics of this column you will not see any significant difference in the selectivity. Therefore updating the statistics of this column is not relevant for optimizer decisions.
· Parameters used for compilation
The SQL Server query optimizer estimates the number of selected rows based on the parameters passed at compilation time. This is very useful in many scenarios. A typical example is a delete flag (column “LOEKZ” means “Loeschkennzeichen” in German). There are many tables in ABAP which have such a column, containing either the value “ “ (for delete flag not set) and “X” (for delete flag set). There is often an index containing “LOEKZ” in order to speed-up selecting the rows, with have the delete flag set. Just having a look on the density 0.5 of this field, this index looks useless. Once you take the parameter value “X” into account and know from the histogram that only 0.01% of the rows have the value “X” (and 99.99% have the value “ “), the index becomes very attractive. This SQL Server feature of creating execution plans dependent on the parameters passed at compilation time is called parameter sniffing.
Although parameter sniffing helps in many cases, it may result in unexpected execution plans in some scenarios. Running a query the first time with untypical parameters results in an execution plan which may not fit for the subsequent executions of the same query. Dependent on the parameters passed by chance at compilation time, totally different execution plans may be used.
· Other factors
It is self-evident that an optimizer hint or a plan guide has a huge impact on the execution plan. But also SQL Server configuration and connection settings are important. SAP recommends to turn off parallel query execution by setting the configuration option “max degree of parallelism” to 1. Setting this to a different value may result in different (parallel) execution plans.
All of the above factors influence the execution plan. For an SAP system you will rather see an issue with parameter sniffing than with outdated index statistics. However, customers often think that they have solved a performance issue by updating the statistics. They are not aware of the side effect of the statistics update. The optimizer will create a new execution plan due to the new statistics. This time the parameters used for compilation are probably different from the previous compilation. It’s just like rolling the dice. Probably a DBBCC FREEPROCCACHE would also have (temporarily) solved the issue.
You may probably ask yourself what to do when running into a performance issue. It is the same as with any other issue. You first have to analyze it before taking action. With SQL Server 2005 you can easily figure both, the execution plan and the parameters used for compilation. Once you detect instable execution plans due to varying parameters you should force an execution plan, for example by adding an optimizer hint. You can add all possible SQL Server optimizer hints into SAP ABAP code. You will even find SAP Standard coding which will force certain indexes for different databases by using ABAP Query hints (please see OSS notes #129385, 133381)
In these hard economic times we get to hear one question very often these days: Why do we need SQL Server Enterprise Edition for SAP Netweaver based applications? Background to this question is that SAP mandates the usage of SQL Server Enterprise Edition for applications based on the SAP Netweaver Stack. Especially by customers who did not purchase SQL Server from SAP, but through other channels, this question is asked frequently. Those customers would like to use SQL Server 2008 Standard Edition, especially for their smaller systems. Let me give you some ideas why SQL Server Enterprise Edition is needed for SAP Netweaver.
Latest with SQL Server 2005 SAP Netweaver took hard dependencies on certain features of SQL Server which usually are not present in Standard Edition. For SQL Server 2005, it started with SQL Server's Table Partitioning which is an Enterprise Edition only feature. SAP implemented the usage of this feature in SAP BI/BW. In older Basis releases like 6.40 (SAP BI/BW 3.5), the usage of table partitioning was on customer demand only (non default). However with SAP BI 7.0, the usage of SQL Server Table Partitioning for Fact tables and Staging tables became the default behavior. Means there is a hard dependency on the presence of the Table Partition feature in the SQL Server 2005/2008 version.
Other Enterprise Edition features SAP customers and to a part SAP relies on, is 'parallel index creation' introduced with SQL Server 2000 or Online Index Maintenance introduced with SQL Server 2005. Especially Online Index Maintenance is something one hardly would like to miss today with database sizes in the Terabytes.
Now with SQL Server 2008 we introduced Database Compression and Backup Compression. Both features don't exist in SQL Server 2008 Standard Edition. In order to use storage in a space efficient manner, SAP by default takes advantage of the new SQL Server Row Compression. This means if you install new SAP systems against SQL Server 2008, SAP will create the tables with using the new and more space efficient Row format. This is true for all the SAP products using the ABAP stack. It represents a hard dependency against a SQL Server feature which doesn't exist in Standard Edition.
But think about the benefits of features like Database Compression and Backup Compression: They can save you a lot of money. Backup Compression was something a lot of customers requested for a long time. Hence a lot of customers spent money on 3rd party applications to get compressed backups. This isn't necessary anymore with SQL Server 2008. A net saving one should consider when looking at SQL Server Enterprise Edition. Readers of this blog might have seen articles on Microsoft IT applying the Row compression (new more efficient Row format) to our SAP ERP system. We saved quite a lot of space. Space which now extends the life span of the storage used in production, test and different sandbox systems. All in all around 400K-500K USD of investments in storage could be saved by applying the new Row format (Row Compression) to Microsoft's productive SAP ERP system alone in this fiscal year. Another customer reported a reduction of 35% volume of their SAP BI/BW system by just applying Row compression. More and more experiences around compression come back, reporting reducing larger tables to a fraction of their original size by either applying ROW compression or Page Dictionary compression. The potential savings achievable by SQL Server's database compression features and Backup compression could pay easily for the more expensive initial investment of SQL Server Enterprise Edition. We also don't make a secret about it, there will be more features about lowering the storage footprint of SQL Server in the future.
For a detailed matrix on features supported by different editions of SQL Server please checkout this document: http://msdn.microsoft.com/en-us/library/cc645993.aspx
Hope this gives some good background
