Q&A

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.

About the Author

Becky Nagel is vice president of AI for 1105 Media, where she specializes in training internal and external customers on maximizing their business potential via a wide variety of generative AI technologies as well as developing cutting-edge AI content and events. She's the author of "ChatGPT Prompt 101 Guide for Business Uses," regularly leads research studies on generative AI business usage, and serves as the director of AI Boardroom, a new resource for C-level executives looking to excel in the AI era. Prior to her current position she was a technical leader for 1105 Media's Web, advertising and production teams as well as editorial director for a suite of enterprise technology publications, including serving as founding editor of PureAI.com. She has 20 years of enterprise technology journalism experience, and regularly speaks and writes about generative AI, AI, edge computing and other cutting-edge technologies. She can be reached at [email protected].

Featured

comments powered by Disqus

Subscribe on YouTube