Comparing SQL Server Performance Baselines

Taking a baseline of your SQL Server and comparing performance over time is an important part of proper SQL Server management. By regularly comparing baselines you can identify trends, anticipate when bottlenecks are likely to occur, and quickly find the root cause of performance bottlenecks. Here are some questions that can be answered by taking a regular baseline and comparing them over time:

  • Has performance improved or decreased over time?
  • How has the latest code release changed performance?
  • Has performance changed since a hardware upgrade?
  • Where is the bottleneck, and do we need to upgrade memory, CPU, or disk?
  • Is our database running faster on server A or B?
  • How much longer will it be until we need to upgrade our server?

SQL Server baselines can be compared on two levels. We can compare the performance of individual SQL statements running on the database and we can look at the performance statistics of SQL Server and the Windows operating system as a whole.

SQL statement execution time

The ultimate measure of SQL Server performance is query execution time but there is much more to look at than just the time it takes to run a query. Here is an example of the instaDBA Baseline report. The first baseline was taken on September 10th and the second one a month later. The coloring of the cells indicates better or worse performance, with green indicating an improvement.

Image Missing

This is an example of a database on which we did some performance tuning. Let’s look at the important numbers of a single SQL statement, the one starting with “SELECT DISTINCT AWSDXDATE…”:

  1. The average duration of the query went from about 1 second to .06 seconds.
  2. This query was executed 3,856 times during the first baseline and only 463 tunes during the second baseline. This makes sense because the first baseline was taken for about 7 hours and the second one for only about 30 minutes.
  3. Average reads each time this query was executed decreased from more than 11 K to 268. This is very important because it tells us that the statement is using better indexes to retrieve the data.
  4. We also see that CPU usage per query execution went down from 806 to 56.
  5. Finally, the total improvement of this query is about 94%.

One of the most important things about these numbers is the difference between execution time and reads. Each one tells us a different thing:

  • A change in execution time tells us the query is running faster but we don’t know if it is because the server is running faster or if it is because of a change in the physical database structure, indexes, or statistics.
  • By looking at reads (or writes) we can tell whether a code or index change has resulted in better performance, regardless of whether the server is running faster or slower.

Performance Counters

SQL Server and Windows operating system performance counters can be collected by using performance monitor. Samples can be taken anywhere from once per second to every 5 minutes or more. Here is a comparison of two performance counter baselines.

ImageMissing

Let’s now zoom in on two of the performance counters:

%Processor Time: sqlservr

processor_time

This data is from the same two baselines that were described earlier. We can see that the September baseline was taken for approximately 7 hours and the October one was taken for about 30 minutes. The data is overlaid so that it can be compared on a time series chart. We can see a reduction in CPU usage by SQL Server from an average of 62 to 15.

Full Scans/sec

Scans can occur when a query selects data from a table and no appropriate index is found. If an appropriate index exists on the table then this results in an index seek. So this counter gives a good idea of whether the database has good indexes. We can see that the average scans per second has gone from 47 to 18 and the maximum scans per second has gone from 217 to 47.

Full_Scans

As we can see, running a regular baseline of your SQL Server is essential to good server management because of the wealth of insight it provides. Without doing regular assessments of our server’s performance there really is no way of knowing how performance is evolving over time. As the saying goes, “you can’t improve what you can’t measure”.