Evaluating Performance

Ongoing evaluation of the database performance helps you minimize response times and maximize throughput, yielding optimal performance. Efficient network traffic, disk I/O, and CPU usage are key to peak performance. You need to thoroughly analyze the application requirements, understand the logical and physical structure of the data, assess database usage, and negotiate tradeoffs between conflicting uses such as online transaction processing (OLTP) versus decision support.

Changing conditions result in changing performance. In your evaluations, you can see performance changes as the number of users increases, user access and connection methods change, database contents grow, client applications change, data in the applications changes, queries become more complex, and network traffic rises. By using SQL Server tools to monitor performance, you can associate some changes in performance with changing conditions and complex queries. The following scenarios provide examples:

• By monitoring the response times for frequently used queries, you can determine whether changes to the query or indexes on the tables where the queries execute are required.

• By monitoring Transact-SQL queries as they are executed, you can determine whether the queries are written correctly and producing the expected results.

• By monitoring users that try to connect to an instance of SQL Server, you can determine whether security is set up adequately and test applications or development systems.

Response time is 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 the total number of queries handled by the server during a specified period of time.

As the number of users increases, so does the competition for a server's resources, which in turn increases response time and decreases overall throughput.

In This Section

Topic	Description
Establishing a Performance Baseline	Describes how to establish a performance baseline for SQL Server.
Determining User Activity	Describes how user activity monitoring can help you evaluate SQL Server performance.
Isolating Performance Problems	Contains information about how to isolate specific performance problems.