X hits on this document

Word document

SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and ... - page 59 / 76

296 views

0 shares

0 downloads

0 comments

59 / 76

SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability55

Database server causes slowdown

If the database is the main contributor to the slow performance, check CPU consumption and I/O performance. Observe the disk I/O performance counters in Windows System Monitor (Performance Monitor in Windows NT) for each disk separately. These counters report the volume of the I/O and its performance (response time). Often disk access statistics measured directly on Storage Area Network devices show faster access times. However, SQL Server 2005 experiences response times such as those reported by Windows System Monitor. If the response times are not satisfactory, SQL Server 2005 experiences slow I/O performance.

CPU is pegged

When the CPU is pegged, check the SQL Server configuration max degree of parallelism option. See SQL Server Installation with SAP earlier in this paper. If this setting is other than 1, set it to 1. Having the parameter set to other than 1 could permit a few queries to use most of the CPU on the server, thereby increasing the I/O volume by a factor of 2 to 4.

If the max degree of parallelism option is set to 1, check the threads that can be run in SQL Server 2005 by executing the SQL statements described below. In system processes such as the SQL Server 2005 DMV, the start time of a statement and the handle to the statement are displayed.

In the SQL query window, execute:

select * from sys.dm_exec_requests

Check statements that are running the longest on threads.

Use sql_handle to get the SQL statement.

In the SQL query window, execute:

select * from sys.dm_exec_sql_text(<sql_handle)

Check the statements for their selectivity. Determine if there are statements that do not contain specific Where clause restrictions and return large amounts of data.

Microsoft Corporation © 2005

Document info
Document views296
Page views296
Page last viewedSun Dec 11 04:43:30 UTC 2016
Pages76
Paragraphs1372
Words23715

Comments