Measuring SQL Server Performance

Applies to LoadComplete 4.97, last modified on May 20, 2019

Many web sites work in conjunction with database servers. For instance, an e-shop uses web pages to provide user interface and store information on goods and customers in database tables. When running load tests against your web site, you may also want to check if the database server experiences bottlenecks or not. This topic describes which server performance counters you can use to estimate the database server performance.

Metrics to Measure

To analyze the performance of tested ASP.NET applications, you can measure the following metrics:

  • The number of database transactions
  • Effectiveness of caching and memory managing
  • The impact of table- and row-locking on the database performance

Below you can find information on counters that will help you estimate these metrics for your web site.

You need to add these counters to your project in order for LoadComplete to be able to monitor them.

Counter Descriptions

  • Transactions/sec

    Category: SQL Server:Databases

    The number of started database transactions per second. This value indicates the database server throughput.

  • Active Transactions

    Category: SQL Server:Databases

    The number of active database transactions of the update type.

You can either select a counter for individual databases or choose __Total__ to monitor the summary value for all databases on the server.

Cache and Memory
  • SQL Cache Memory (KB)

    Category: SQL Server:Memory Manager

    The amount of memory used for dynamic SQL cache.

  • Cache Faults/sec

    Category: Memory

    Specifies how often the operating system fails to find data in the file system cache. This value should be low. High rates indicate insufficient memory or fragmented disks.

  • Lock Requests/sec

    Category: SQL Server: Locks

    The number of locks (new locks and lock conversions) per second.

  • Lock Timeouts/sec

    Category: SQL Server: Locks

    The number of lock requests that timed out.

  • Lock Waits/sec

    Category: SQL Server: Locks

    The number of lock requests that cannot be made immediately and make the caller wait.

  • Number of Deadlocks/sec

    Category: SQL Server: Locks

    The number of lock requests that caused deadlocks. This number should be very low. A deadlock implies that there should be a request retry or some other action. High values indicate that developers need to improve transaction isolation levels and queries.

  • Average Wait Time (ms)

    Category: SQL Server:Locks

    The average number of milliseconds a lock request has to wait.

  • Average Latch Wait Time (ms)

    Category: SQL Server:Latches

    The average number of milliseconds a request for a latch has to wait. Latches are light, short-term row locks. High numbers indicate a contention for resources.

Including Counters Into Monitoring Tasks

For information on how to add these counters to runtime graphs, see Managing Server-Side Performance Counters.

When adding counters to the project, make sure that you connect to the database server computer. In general, it may differ from the computer where the tested web site is running. See below.

Specifying the Server to Be Monitored

You specify the server to be monitored when adding a counter to the project on the Counters page. Quite often, the database resides on a computer other than the one on which the tested web site is running. Its name may coincide with the web site server name or it may be different. You need to specify the database server name on the page. If you do not know the name, ask the developers or your system administrator for it.

The database server must be configured in a special way for you to be able to get access to counters. See Preparing Servers for Monitoring.

See Also

Typical Use Cases
About Server Monitoring
Server Monitoring
Managing Server-Side Performance Counters
About Test Results

Highlight search results