— by Lori Brown @SQLSupahStah
For the most part, I stick to my daily DBA duties of making sure that SQL Server maintenance is running and that the proverbial lights are on in all my SQL boxes. However, sometimes it is fun to do some tuning and it’s even better when you have a success.
I have a SQL Server that hosts some Microsoft ERP databases. There was a problem with some slow performance and after collecting some performance data, I narrowed the root cause down to a view (vp_03400Exception1) that was used in a stored procedure. Out of the 15 million reads performed by the proc when I captured it, 12 million were directly from the offending view. In checking the execution plan for the view I found that a large table (APTran) was scanned in one spot and there were key lookups going on in other places (APTran & APDoc).
Bad stuff in bad plan…thick arrow indicates that almost 4 million records were scanned
Key lookups are not good (**on small tables these might not matter but on large tables they do) and should be eliminated if possible. Key lookups occur when you have an index seek against a table, but your query wants extra columns that are not in that index. This causes SQL Server to have to go back and get those extra columns. Lots of trips back to get extra columns results in slow performance. Of course scans are not good either so I wanted to try to get rid of that too.
Since the key lookups have an output list in the execution plan that lists what extra columns were needed, I took a look and simply added those columns as included columns on the existing indexes that were being used in the plan. In effect, I created a covering index using included columns. The included columns are only used when specific queries need it. If other queries use the index with included columns but doesn’t actually need them, those won’t be pulled in.
Here are the modified indexes with the included columns.
With the new columns included in the indexes, index seeks with no key lookups are now being performed. The thick arrow has disappeared because instead of needing to scan the whole APTran table, it knows to return only 4 records. This means that the rest of the query does not have to perform anywhere near as much work as it did before. See the new execution plan below:
Much better stuff in more efficient plan…small arrow indicates that only 4 records were needed from APTran so much less work is being done.
Below you can see that overall the new plan uses less memory, is smaller and just plain old more efficient.
Boom!! Mic drop!!
You might be thinking that I should not celebrate since my query only returned 1 row. However, what if the results were hundreds or thousands or millions of rows? The work done by the old execution plan would be significant and the users would feel it. So, what’s the bottom line here? If you have queries accessing large tables, check execution plans to see if they are being accessed efficiently. Sometimes a small change to an existing index will work wonders. Sometimes you have to create a new index to make queries efficient. The execution plan might just tell you what it needs if you know where to look.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!