Unlocking SQL Server Performance

As an avid developer who’s always heads‑down in code and passionate about helping organizations execute strategy efficiently, I’m constantly on the lookout for tools that can help me optimize SQL Server performance. One tool that’s proven invaluable in this quest is SQL Sentry Plan Explorer.

Now, full disclosure: I’m not affiliated with SQL Sentry—not an employee, partner, or even a customer (SQL Sentry Plan Explorer is somehow completely free!). I’m simply a data and code geek who’s found that Plan Explorer cuts through the complexity of execution plans like a hot knife through butter.

In this post, I’ll take you on a deep dive into how Plan Explorer can help diagnose and resolve common query plan issues in SQL Server. We’ll explore real-world examples featuring index problems, key and RID lookups, scans versus seeks, and missing query hints. Whether you’re scaling an analytics application to serve thousands of customers or just trying to squeeze out a bit more performance, these insights should prove useful.

Understanding the Basics: What’s a Query Plan?

Before we jump into the specifics, let’s revisit what a query execution plan is. In SQL Server, every query you run is translated into an execution plan—a roadmap that shows how the database engine intends to retrieve your data. This plan includes operations like scans, seeks, joins, sorts, and lookups. Understanding this plan is key to diagnosing performance issues. That’s where SQL Sentry Plan Explorer shines: its intuitive visualizations make it easier than ever to pinpoint costly operations.

Common Query Plan Issues

1. Index Problems

Indexes are essential for query performance, but when they’re missing or misconfigured, performance can degrade significantly. Plan Explorer helps you quickly identify areas where SQL Server is performing full table scans rather than efficient index seeks.

Example: Missing Index Leading to a Table Scan

Imagine you have a table called Customers with millions of rows. Consider this simple query:

SELECT CustomerID, FirstName, LastName
FROM Customers
WHERE LastName = 'Smith';

If there’s no index on the LastName column, SQL Server may resort to a table scan—reading every row in the table—to find matches. In Plan Explorer, you might see an operator labeled “Table Scan” or “Index Scan” with a high cost relative to other operations.

Tip:

• Create a Non-Clustered Index: Adding an index on LastName can transform that table scan into an index seek, significantly reducing the number of rows that need to be examined.

CREATE NONCLUSTERED INDEX idx_Customers_LastName
ON Customers (LastName);

After creating the index, rerun your query and examine the plan again. You should see a shift from a scan to a seek, reducing the cost and improving query performance.

2. Key Lookups and RID Lookups

Key Lookups

A key lookup occurs when SQL Server uses a non-clustered index to locate rows, but then must reference the clustered index (or the heap) to retrieve additional columns that aren’t covered by the index. This extra step can be costly, especially if it happens frequently in a query.

Example: Key Lookup in an Order Query

Consider the following query on an Orders table:

SELECT OrderID, OrderDate, TotalAmount, CustomerName
FROM Orders
WHERE OrderID = 10248;

Suppose you have a non-clustered index on OrderID, but it doesn’t include OrderDate, TotalAmount, or CustomerName. SQL Server will use the index to quickly locate the row for OrderID = 10248 and then perform a key lookup to fetch the additional columns.

In Plan Explorer, a key lookup operator might be flagged, indicating an opportunity for performance improvement.

Tip:

• Covering Indexes: To avoid key lookups, you can create a covering index that includes all the columns needed for the query:

  CREATE NONCLUSTERED INDEX idx_Orders_OrderID
  ON Orders (OrderID)
  INCLUDE (OrderDate, TotalAmount, CustomerName);

This index helps SQL Server retrieve all required data from the index alone, bypassing the need for expensive key lookups.

RID Lookups

A RID (Row Identifier) lookup happens when a table doesn’t have a clustered index (i.e., it’s a heap) and SQL Server must locate a row using its physical address. Similar to key lookups, RID lookups can slow down query performance.

Tip:

• Implement a Clustered Index: Converting your heap to a table with a clustered index can eliminate RID lookups. Identify a suitable column or set of columns that can serve as a unique and stable identifier, and create a clustered index on it.

  CREATE CLUSTERED INDEX idx_Orders_Clustered
  ON Orders (OrderID);

Once a clustered index is in place, SQL Server can more efficiently locate rows using index seeks rather than resorting to RID lookups.

Scans vs. Seeks: The Ongoing Battle

Understanding the difference between scans and seeks is fundamental for SQL Server optimization.

Table Scans and Index Scans

• Table Scan: SQL Server reads every row in a table. This operation is expensive, particularly for large tables.
• Index Scan: SQL Server reads through the entire index. While typically faster than a table scan, it’s still not as efficient as an index seek if only a subset of rows is needed.

Index Seeks

• Index Seek: SQL Server uses the index to directly locate the rows needed for the query. This is the ideal scenario for performance.

Example: Scanning vs. Seeking in a Product Query

Consider the query:

SELECT *
FROM Products
WHERE ProductName LIKE 'A%';

Without an appropriate index, SQL Server might perform a scan. However, if you have an index on ProductName that supports range queries, SQL Server can perform an index seek.

Tip:

• Optimize Your Queries: When possible, design your queries to take advantage of index seeks. Avoid leading wildcards in LIKE clauses (e.g., LIKE '%A') because they force SQL Server to scan the index or table.

• Check Execution Plans Regularly: Use SQL Sentry Plan Explorer to quickly see whether your query uses a seek or a scan. If you notice scans where seeks are possible, consider modifying your indexes or query structure.

Missing Query Hints: When and How to Use Them

Query hints are directives you can add to your SQL queries to force a particular behavior in the query optimizer. While they should be used sparingly, they can sometimes lead to improved performance when SQL Server’s automatic choices aren’t optimal.

Example: Using NOLOCK

A common hint is NOLOCK, which tells SQL Server to perform a “dirty read” by not placing shared locks during the read operation:

SELECT *
FROM Orders WITH (NOLOCK)
WHERE OrderDate > '2020-01-01';

This hint can be useful in scenarios where data consistency isn’t a primary concern and you need to reduce locking contention. However, it should be applied judiciously since it can lead to reading uncommitted or inconsistent data.

Missing Index Hints

Sometimes, SQL Server might not pick up on an index that you know exists. In such cases, an index hint can force the optimizer to use a specific index:

SELECT OrderID, OrderDate
FROM Orders WITH (INDEX(idx_Orders_OrderID))
WHERE OrderID = 10248;

Tip:

• Use Hints as a Last Resort: Rely on hints only after careful analysis, as they can sometimes lock your query into a less flexible execution plan that might not perform well as your data changes over time.
• Don't Do This: OK, it's done a lot, and I've done it. But don't do this. I only included this example as it's one that so many people use. But if you're reaching for NOLOCK, think about refactoring your query, maybe using something like SNAPSHOT ISOLATION (but that's beyond the scope of this...)

Tips and Tricks for SQL Server Optimization

Here are some additional best practices to keep in mind when optimizing SQL Server queries:

1. Regularly Review Execution Plans:
   
   Utilize SQL Sentry Plan Explorer to regularly review your query execution plans. Look for high-cost operations like scans, key lookups, or RID lookups. Identify any changes in patterns that might indicate a performance regression.

2. Monitor Index Usage:
   
   Use SQL Server DMVs (Dynamic Management Views) to monitor index usage and fragmentation. This can help you identify whether an index is being used as expected or if it’s contributing to performance issues.

3. Maintain Statistics:
   
   Ensure that statistics are updated regularly. Outdated statistics can cause the query optimizer to choose suboptimal plans.

4. Optimize Index Design:
   
   Strike a balance between too many indexes (which can slow down data modifications) and too few (which can cause poor query performance). Regularly review your indexes and remove those that aren’t being used.

5. Consider Query Refactoring:
   
   Sometimes a query can be rewritten to better align with your indexes. For example, breaking complex queries into simpler components or using common table expressions (CTEs) can sometimes lead to better execution plans.

6. Evaluate Hardware and Configuration:
   
   Occasionally, performance issues stem from hardware limitations or configuration issues rather than query design. Ensure that your SQL Server instance is properly configured for your workload and that your hardware resources (memory, CPU, disk I/O) are adequate.

7. Leverage Community and Tools:
   
   Engage with the SQL Server community through forums, blogs, and conferences. Tools like SQL Sentry Plan Explorer are developed and refined by experts who understand the intricacies of SQL Server performance tuning.

8. Automate Monitoring and Alerts:
   
   Consider automating the monitoring of your SQL Server environment. Set up alerts for when query performance degrades, and use tools that can automatically capture and analyze execution plans.

A Real-World Scenario

Imagine you’re responsible for an analytics application (like ClearPoint Strategy) that serves thousands of customers on MS SQL Server. Over time, you notice that certain reports take longer to generate. You decide to use SQL Sentry Plan Explorer to analyze the query execution plans of your most frequently run queries.

Step 1: Identify the Bottleneck

In one of your queries, you notice a high-cost operator labeled “Key Lookup.” The query retrieves customer order information, and the key lookup indicates that the non-clustered index on OrderID isn’t covering all the required columns.

Step 2: Implement a Covering Index

Armed with this information, you create a covering index:

CREATE NONCLUSTERED INDEX idx_Orders_Covering
ON Orders (OrderID)
INCLUDE (OrderDate, TotalAmount, CustomerName);

After deploying this change, you rerun the query and inspect the execution plan again. SQL Sentry Plan Explorer now shows an index seek instead of a key lookup, and the query’s execution cost drops significantly.

Step 3: Optimize for Scans vs. Seeks

Another query that retrieves product information is performing an index scan instead of an index seek. You realize that the query uses a LIKE 'A%' predicate, and although there is an index on ProductName, the query optimizer isn’t taking full advantage of it due to the way the predicate is structured.

You consider adjusting the query or enhancing the index. By modifying the query to use a more sargable condition or adjusting the index design to better support range queries, you can coax SQL Server into using an index seek.

Step 4: Fine-Tune with Query Hints

In a scenario where the query optimizer is stubbornly choosing a suboptimal plan, you experiment with query hints. For example, you might try:

SELECT *
FROM Orders WITH (INDEX(idx_Orders_Covering))
WHERE OrderDate > '2020-01-01';

After thorough testing, you determine that while the hint improves performance for this specific query, it’s not a silver bullet for all queries. You document your findings, so that if the query pattern repeats, you have a proven strategy to refer to.

Conclusion

SQL Sentry Plan Explorer has become an indispensable part of my SQL Server optimization toolkit. Its ability to visualize execution plans in detail helps identify costly operations like index scans, key lookups, and RID lookups—insights that are critical when scaling applications to serve thousands of customers.

By understanding common pitfalls in query execution plans and employing best practices such as creating covering indexes, distinguishing between scans and seeks, and judiciously applying query hints, you can drastically improve the performance of your SQL Server applications. Regular monitoring, combined with the powerful features of Plan Explorer, ensures that you’re not caught off-guard by performance issues as your application scales.

Remember, performance tuning is both an art and a science. While tools like SQL Sentry Plan Explorer provide the technical insights, the true magic lies in interpreting those insights and making informed changes that align with your application’s unique workload.

Happy coding and optimizing!

—

Dylan Miyake

Co-founder of ClearPoint Strategy | MIT & Bowdoin Alum (Go U Bears)

Whether you’re a seasoned DBA or just starting out in SQL Server, I hope this exploration gives you the confidence and the tools to dive deep into your execution plans and emerge with a faster, more efficient system.