Q&A
Mastering SQL Server Indexing: What Every .NET Developer Needs to Know
For developers working with SQL Server, few performance tuning techniques are as essential--or as frequently misunderstood--as indexing. Done right, indexing can drastically improve application responsiveness, streamline transaction processing, and reduce load on critical database systems. Done wrong, it can bog down performance, increase maintenance overhead, and lead to slow, unpredictable query behavior.
In his session "SQL Server Indexing" at the Visual Studio Live! Las Vegas developer conference set for March, veteran consultant and Microsoft MVP Denny Cherry will demystify the art and science of indexing across multiple versions of SQL Server, from 2000 to 2025.
"Indexing correctly in SQL Server is the core to getting good performance our of the database, and therefor the application that is using the database. Slow databases can have a critical impact as the business can't close their month end and do their invoicing."
Denny Cherry, Owner and Principal Consultant, Denny Cherry & Associates Consulting
Geared toward .NET developers who interact with SQL databases but may not have a deep background in database internals, the talk covers foundational indexing concepts, practical examples of both best and worst practices, and guidance on how to apply newer indexing features introduced in recent versions of SQL Server.
Denny's session also explores specialized techniques like filtered indexes, strategies for tuning indexes on high-transaction tables, and tips for spotting redundant or ineffective indexes in legacy environments. Whether you're struggling with slow queries, trying to scale a database-driven application, or simply looking to sharpen your T-SQL toolbox, this session is designed to give you a stronger command of how indexing can make or break database performance.
We caught up with Denny ahead of the event to learn more about the insights he'll be sharing--and what developers can do today to start indexing smarter.
VisualStudioMagazine: What inspired you to present on this topic?
Denny: When it comes to indexing in Microsoft SQL Server, it's a topic that most developers don't know enough about. Indexing correctly in SQL Server is the core to getting good performance out of the database, and therefore the application that is using the database. Slow databases can have a critical impact as, for example, the business can't close their month end and do their invoicing
What's a common mistake developers make when creating non-clustered indexes that actually hurts performance?
Oftentimes when indexes are hurting performance instead helping is when the columns are not in the correct order, or columns are missing from the index altogether. This leads to either index scans or key lookups which are both performance killers.
How do you recommend handling indexing on tables that are frequently updated--especially large transactional tables?
Tables which have a high transaction throughput should still be indexed. While, yes, every index does slow down inserts, updates and deletes, we're talking my microseconds. Unless there are a LOT of indexes, managing the indexes which are being used is worth the cost. The problem comes into play when there are indexes on the tables which aren't being used or updated and are only being maintained.
Can you explain when it makes sense to use filtered indexes, and what the performance trade-offs are?
Filtered indexes are really useful when the queries always have the same predicates in the where clause, an active flag for example. If we are always looking for rows where the active column in the table is 1 then we can build a filtered index on this to make the index smaller which means that it will perform faster when looking for rows.
Is there a quick way to identify redundant or overlapping indexes in a legacy database?
There are system catalog views which you can look at to see the columns which are used by the indexes, and in what order. Those catalog views are going to be the "easiest" way to see the information in question. It is a sadly manual process of reviewing the records and looking for duplicate indexes or indexes that are subsets of another index.
How does the query optimizer decide whether to use an index, and can developers influence that decision effectively?
The optimizer looks at all the indexes and sees which ones will satisfy the query that is being executed. If it sees an index that will work, it uses that one. If there are multiple indexes that will work, it'll select the one that it thinks will have the smallest IO cost based on the column sizes of the columns in each index. The best way to influence this is to create proper indexes on the tables. That way, the SQL Server will select the correct index. There are hints that you can give to tell SQL Server to use a specific index, but normally I just use these for tuning to see why SQL Server isn't using the index that I think it should be. Sometimes I'm right, but there's a column missing from the index, and sometimes it's making the decision based on information that I'm not aware of like the row sizes, or the number of rows being returned.
How can attendees learn more about this topic and also prepare for your session?
There are tons of books and blog posts about this topic that are available. Having an understanding of what indexes are and what they can do would be helpful, but this session is written to assume that we're starting from the group up. It won't make you an index running expert, but it will give you a good foundation to continue on from. This post can be useful for redundant indexes. This post could be useful for some background.
Note: Those wishing to attend the session can save money by registering early, according to the event's pricing page. "Save $400 when you register by the Super Early Bird Savings deadline of Jan. 16," said the organizer of the event, which is presented by the parent company of Visual Studio Magazine.