Denny Cherry's Top Tips for Indexing SQL Server Tables
A SQL Server expert gives his take on the absolute, No. 1, very worst thing IT pros can do when it comes to indexing.
Consultant, author and Microsoft MVP Denny Cherry travels the world helping companies implement complex SQL Server projects and fix their toughest issues.
He'll be speaking on several topics at the upcoming SQL Server Live! conference (part of Live! 360) in Orlando in December. We recently got a chance to ask him more about one of his sessions dealing with best practices for indexing SQL Server tables.
Your upcoming session at SQL Server Live!/Live! 360 talks about the "best and worst practices for indexing tables within your SQL Server 2000-2016 database." What is the absolute, No. 1, very worst thing any IT pro can do when it comes to indexing their tables in their database?
Not indexing the tables is the worst thing someone can do. This makes SQL Server do a table scan for every query instead of going directly to the rows that it needs. So performance is, as you'd expect, horrible.
What's a really, really bad thing that many IT pros/DBAs might not realize is so bad when it comes to indexing?
A lot of times, people will create lots of indexes assuming that this will fix the problem. SQL Server will almost always only use one index per table, and the extra indexes are getting updated but never used to look up data. And the indexes that SQL Server is using aren't handling all the user cases correctly.
What is your No. 1 best practice recommendation?
Look at the indexes that need to be added regularly. Indexes change as the application changes.
"Not indexing the tables is the worst thing someone can do. This makes SQL Server do a table scan for every query instead of going directly to the rows that it needs. So performance is, as you'd expect, horrible."
Denny Cherry, Consultant, Author and Microsoft MVP
What is your top favorite new indexing feature in SQL Server 2016 and why?
My favorite feature hands-down is the Query Store. It'll let me easily see the worst offenders on the system so I can see why they are so bad. Then I can add the indexes that are needed to make the query faster.
What is a subtlety one must consider when choosing between clustered and non-clustered indexing that IT pros may not necessarily be aware of?
Ninety-nine percent of the time, you'll be working with non-clustered indexes. Very rarely do changes to the clustered index need to be made.
What about security? What's your favorite security-related feature in SQL Server 2016?
Always Encrypted is probably my favorite security feature of SQL Server 2016. It lets people that don't have DBAs quickly and easily encrypt the data in the database without having to have any understanding of how encryption really works.
For more about Live! 360, go here.
Becky Nagel is the vice president of Web & Digital Strategy for 1105's Converge360 Group, where she oversees the front-end Web team and deals with all aspects of digital projects at the company, including launching and running the group's popular virtual summit and Coffee talk series . She an experienced tech journalist (20 years), and before her current position, was the editorial director of the group's sites. A few years ago she gave a talk at a leading technical publishers conference about how changes in Web browser technology would impact online advertising for publishers. Follow her on twitter @beckynagel.