So I find myself performance tuning queries and statements all the time for SQL Server. Many times I run into easy statements that are missing an index or calling a function 20 million times and causing problems. These queries typically move from 20 seconds to 1-2 seconds very easily with some quick changes. My problem this week was a query that took 1.5 seconds. I needed to get this to run under 1 second consistently. I knew could get a little more out of the query by looking at the execution plan but I also knew I would have to consider the Architecture and the application itself to really get it the way I wanted it.
I was surprised to find something in the execution plan that helped me out more than I expected. The Key Lookup (In 2000 they are called Bookmark Lookups). These show up in the execution plan and look like this.
You will usually find these in your execution plans along with an Index seek/scan. But what really do they mean? SQL Server has found your data in the index but some of the values you have asked for in the query are not located in the index pages and it must go and lookup the data. This will take a little more time as SQL Server goes and fetches your data. The funny thing about this query is that for the Execution plan it only put cost at 10% on this particular lookup. Which seemed odd to me because when I looked at the IO STATS ( Set STATISTICS IO ON) it seemed like many of my reads were to pull back this data. I decided I would ignore the Cost % and place a covering index on the fields that are getting pulled back. I changed the index that it was using in the seek to add columns in my select statement as INCLUDE columns in my index. This made a larger improvement than just the 10% that it had estimated. It removed about 500ms of time from the query. Which is a lot when your trying to go from 1.5 seconds to <1 second.
Of course you do have a trade off here. By adding the columns to the index I’ve made my index larger and take up more space so don’t go around just adding all your fields into the INCLUDE of a index. You need to evaluate and find out if the trade off is worth it. In this case it is. I’m also making changes to the application so that it doesn’t request columns it doesn’t need then I can remove them from the INCLUDE and save myself some space.
If there is one thing I’ve learned from performance tuning and reading execution plans is never take them at face value. They are a tool that was created to help you make better decisions. They are not always right so make sure to do your homework and really test your procedures and break them down to find the real bottlenecks.
Happy Performance Tuning