SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability49
Performance Monitoring and Tuning
SQL Server 2005 does not require additional tools or extra resources to maintain SAP applications. SQL Server 2005 offers advanced query optimization that automatically improves query performance and response time.
In addition, SQL Server 2005 autotuning adjusts resources dynamically and tunes database parameters to respond to changing workloads and usage characteristics without manual intervention. Routine tasks are automated. Memory and lock resources and file sizes are adjusted dynamically.
SQL Server performance monitoring
This section describes the SQL Server 2005 performance monitoring tools and features. In particular, SQL Server 2005 introduces a number of new dynamic management views (DMVs). These DMVs permit a wide variety of options for monitoring SQL Server internals. Many of the DMVs will be implemented into the SAP Database Monitor over time.
SQL Server Profiler
SQL Server Profiler monitors the query performance of Transact-SQL statements executed by SQL Server 2005. However, this tool cannot aggregate query performance data. Instead, SQL Server Profiler lists each execution of a query separately, causing enormous amounts of data to be written.
Because of SAP user management and its scheduling of user requests, it is not possible to restrict profiling on user names or specific connections to SQL Server. For this reason, SQL Server Profiler should be used only to profile and monitor a system with low activity. It is not generally used in SAP production systems.
In most cases, SAP systems are monitored by using SAP tools such as SAP Database Monitor or the features described in in this paper.
Dynamic management views
SQL Server 2005 contains a number of new DMVs. These DMVs give greater transparency and visibility into the database for proactive health and performance monitoring.
The sys.dm_exec_query_stats DMV stores aggregated performance data for queries that were run since SQL Server was started. This data can be used to provide a trend analysis of how the system is performing.
The data provided in this view is similar to the data from SAP Database Monitor. This view also contains additional data that is not available in earlier versions of SQL Server and SAP including:
A SELECT statement against this view displays performance statistics on statements in the statement cache and on some statements that were already flushed out of the cache. However, data on queries that were executed a number of hours earlier on a busy system can be flushed.
Microsoft Corporation © 2005