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 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 strategy. She also serves as executive editor of the group's media Web sites, and you'll even find her byline on PureAI.com, the group's newest site for enterprise developers working with AI. She recently gave a talk at a leading technical publishers conference about how changes in Web technology may impact publishers' bottom lines. Follow her on twitter @beckynagel.

Featured

  • Azure Cost Management Now Commercially Available for Some Tenancies

    Microsoft on Monday announced that its Azure Cost Management feature had reached the "general availability" release stage for both Azure "pay-as-you-go" customers and Azure Government tenancies.

  • Microsoft Bringing Files Restore Capability to SharePoint Online and Teams

    Microsoft on Monday announced that it's delivering its Files Restore feature for SharePoint Online and Microsoft Teams to Office 365 tenancies as early as this month.

  • Microsoft Nabs IoT Platform Provider Express Logic

    As part of its plan to invest $5 billion in IoT technologies, Microsoft this week acquired Express Logic, which provides real-time operating systems for industrial embedded and IoT devices.

  • Dealing with Broken Dependencies in SCVMM

    Brien shows you how to resolve some broken, template-related dependencies in Microsoft's System Center Virtual Machine Manager.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.