Evaluating Performance
Optimal performance comes from minimal response times and maximum throughput as a result of efficient network traffic, disk I/O, and CPU time. This goal is achieved by analyzing thoroughly the application requirements, understanding the logical and physical structure of the data, and assessing and negotiating tradeoffs between conflicting uses of the database, such as online transaction processing (OLTP) versus decision support.
Response Time vs. Throughput
Response time is measured as the length of time required for the first row of the result set to be returned to the user in the form of visual confirmation that a query is being processed.
Throughput is a measure of the total number of queries handled by the server during a given time.
As the number of users increases, so does the competition for a server's resources, which in turn causes response time to increase and overall throughput to decrease.
Factors That Affect Performance
The following areas affect the performance of SQL Server:
- System resources (hardware)
- The Microsoft Windows NT® 4.0 and Windows® 2000 operating systems
- Database applications
- Client applications
- Network
Before these areas can be monitored, you must know what level of performance is reasonable given normal working conditions. To do this, establish a server performance baseline by monitoring Microsoft® SQL Server™ performance at regular intervals, even when no problems occur.
Troubleshooting Problems
You can monitor the following areas to troubleshoot problems:
- SQL Server stored procedures or batches of SQL statements submitted by user applications.
- User activity, such as blocking locks or deadlocks.
- Hardware activity, such as disk usage
Problems can include:
- Application development errors involving incorrectly written Transact-SQL statements.
- Hardware errors, such as disk or network-related errors.
- Excessive blocking due to an incorrectly designed database.
SQL Profiler can be used to monitor and troubleshoot Transact-SQL and application-related problems. System Monitor (Performance Monitor in Windows NT 4.0) can be used to monitor hardware and other system-related problems.