The Misconception of Slow Queries in SQL Server

By bradyupton in Performance Tuning, Query Plans

As a SQL Server DBA, one of my regular tasks is to monitor and optimize the performance of SQL Server databases. A common tool in my arsenal is a script that retrieves the top 10 slowest queries. However, there’s a crucial misconception that needs to be addressed: just because a query appears in this list doesn’t necessarily mean it’s slow or inefficient.

Understanding the Top 10 Slowest Queries

When we ask SQL Server to provide the top 10 slowest queries, it’s essential to understand what we’re actually requesting. The server is simply returning the queries that have the longest duration or highest resource usage among those that were executed during the monitoring period. This does not automatically imply that these queries are poorly written or unoptimized.

Why Optimal Queries Can Appear in the Top 10

There are several reasons why perfectly optimized queries might show up in your top 10 slowest queries list:

  1. High Frequency of Execution: A query that is executed very frequently may have a cumulative duration that places it in the top 10, even if each individual execution is fast.
  2. Data Volume: Queries that operate on large datasets may naturally take longer to execute, even if they are optimized for efficiency.
  3. System Load: External factors such as system load, network latency, or resource contention can temporarily increase the execution time of queries.
  4. Nature of the Task: Some queries are inherently time-consuming due to the complexity of the task they perform, such as complex joins, aggregations, or calculations.

Interpreting the Results

When you identify a query in the top 10 slowest list, it’s important to analyze it in context. Consider the following:

  • Execution Plan: Review the execution plan to check for any inefficiencies or areas for improvement.
  • Frequency of Execution: Determine how often the query is executed and whether its cumulative impact is significant.
  • Data Volume: Assess whether the query is handling large volumes of data and if there are ways to reduce the dataset size.
  • Resource Usage: Look at the resources the query is consuming and explore ways to optimize resource utilization.
  • Comparison with Baselines: Compare the query’s performance with historical baselines to identify any anomalies or trends.

In summary, appearing in the top 10 slowest queries list doesn’t automatically condemn a query as slow or inefficient. It’s a starting point for further investigation and analysis. As a SQL Server DBA, my role is to dig deeper, understand the context, and make informed decisions about optimization. By doing so, we can ensure that our databases run smoothly and efficiently, supporting the needs of our organizations.

Correct SQL Server TempDB Spills in Query Plans Caused by Outdated Statistics

By bradyupton in Performance Tuning, Query Plans 0 Comment

Statistics are an integral part of SQL Server and query performance. In short, the query optimizer uses statistics to create query plans that will improve the overall performance of the queries ran. Each statistic object is created on a list of one or more table columns and includes a histogram displaying the distribution of values in the first column. The histogram can have up to 200 steps, but no more regardless of the number of rows in the column or index.

In this post we’ll take a look at one specific performance issue that you might find in an execution plan of a query. If you’ve ever noticed the following warning, then this post is for you:

1 - TempDB spills
Within the AdventureWorks2014 DB, I’ll use the following query for my example:

SELECT BusinessEntityID, FirstName, LastName, EmailPromotion
FROM [AdventureWorks2014].[Person].[Person]
WHERE EmailPromotion > 0
ORDER BY LastName

Looking at this query I can already tell contention may be present so I’ll go ahead and add a covering index:

CREATE NONCLUSTERED INDEX [IX_Person_EmailPromotion_INCLUDES]
ON [Person].[Person] ([EmailPromotion])
INCLUDE ([BusinessEntityID],[FirstName],[LastName])

When adding the index above, statistics were automatically created and updated. Since the addition of this index I’ve added a few thousand rows to the Person table.

Let’s run the query and make sure the “Include Actual Execution Plan” button is selected.

2 - TempDB spills

After the query executes let’s take a look at the execution plan by clicking on the tab in the Results pane:

3 - TempDB spills
These warnings were added to SQL Server Management Studio 2012 so if you’re using an older version you may not see this. The spill data to TempDB warning means that the query was not granted enough memory to finish the operation and spilled over into the TempDB to complete the operation. We all know reading from memory is much faster than reading from disk and this is exactly what is happening here. The query read as much as it could from memory before moving over to the TempDB disk.

Click here to view the rest of this post.