Last year, I had the opportunity to contribute to my first book, Pro SQL Server 2012 Practices. This was a huge honor, and the list of my co-authors is remarkable. What we’ve decided to do is each take one chapter, written by someone else, and post a review. I reviewed Jason Strate’s (blog | twitter) chapter, “Indexing Outside the Bubble”.
(A list of all the blog reviews can be found here.)
When performance tuning, specifically when adding indexes, it can be very easy to get caught up in the one query or one process that you are working with. This tunnel vision can lead the best of us to forget that there is more happening within the system. There are other queries, other processes, and other users to be aware of. Jason shows us how to step outside of the bubble we are working in to focus on the full environment and the business needs.
He covers two main components in the full environment: finding missing indexes throughout the database and tuning for a specific workload. Missing indexes can be found through DMOs and the plan cache. A workload can be captured through a trace or extended events, and tuned with the Database Tuning Advisor.
Jason shows examples of each of these tools. The scripts he uses are clear and concise. He mentions the GUI tools, and shows a few examples, but focuses primarily on scripts. I like this approach (and usually use scripts myself) because, as he points out in the chapter, it is much easier to port a script from server to server than it is to fire up a GUI on each instance you want to run the task on.
When it comes to the business, Jason covers two important points. An index may not be used frequently, but can be part of a very important process. Removing it could cause a weekly or even monthly task to decrease in performance drastically. This is why it is important to know why your indexes exist. Indexing on foreign keys is also very important, because those relationships will be used frequently in queries – how often do you query data from only one table? Here, another excellent script – to find foreign keys that are not indexed – is provided.
All of these approaches are things I deal with weekly as a consultant. Indexing a database is something that can be easily overlooked, or considered a set-it-and-forget-it strategy. It should not be. A constant cycle of reviewing current index usage and adding new indexes is integral to having a well-performing database and application. This chapter gives you a set of tools to start that cycle.
This chapter is an excellent segue into the book Jason and Ted Krueger wrote about indexes – Expert Performance Indexing for SQL Server 2012. I’ve been reading this for the last couple of months and it’s proved invaluable in my day-to-day work.
Make sure you check out Pro SQL Server 2012 Practices today!
Jes Borland is a Premier Field Engineer - SQL Server for Microsoft, where she is a trusted technical adviser, deep technical support, and teacher for her customers. Her experience as a DBA, consultant, and 5-time Data Platform MVP allow her to help the business and the IT teams reach their goals. She has worked with SQL Server as a developer, report writer, DBA, and consultant. Her favorite topics are administration, automation, and performance. She frequently presents at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef.
