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 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.
The following areas affect the performance of SQL Server:
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.
You can monitor the following areas to troubleshoot problems:
Problems can include:
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.