When looking at a query plan (QP) in SQL Server Management Studio (SSMS), it can be overwhelming and confusing. People often ask, “What do I look for?” and “Where do I start?” There are several warning signs that your query is performing poorly. Below we’ll take a look at some of the most common types.
QP Navigation Basics
Before we discuss the query plan warning signs, lets start with a few Query Plan navigation tips.
1. Work right to left and you’ll often find the most costly items
2. For large query plans, use the SSMS zoom feature found in the bottom right corner to navigate (left mouse click and hold)
3. When you have multiple queries (such as stored procedures) take in to account the cost to the overall process (batch)
Scans? Did you say Scans?
Are you missing any indexes? Did you forget to create a Primary Key? Not all table scans are candidates. Focus on the most costly ones. Don’t ignore those pesky temp tables with tons of data.
The query plan warning signs for these are “Table Scans”, “Clustered Index Scan”, and “Index Scan”.
I already have indexes on my table!
Have you ever caught yourself saying this? Just because there are indexes, does not mean they are being used effectively. Did you design the existing indexes correctly? Perhaps, the indexes that are there served a different purpose for a different query. You may need to modify your existing indexes or create new ones.
The query plan warning signs for these are “Key Lookups” (formerly known as “Bookmark Lookups”, pre SQL Server 2005) and “RID Lookups”.
What do all of the above performance problems have in common? Indexes!! Indexes are a key component to query performance. Make sure you have the right index strategy. You’ll be on your way to better performance.