Webinar Follow-Up: 5 SQL Server Indexing Myths

Presenter: Jason Strate

There are many "best practices" around that help people decide how to index databases. Having these practices can help alleviate the time that it takes to design an indexing strategy for a database. These practices can be of great use, except when they are wrong. Join us in this session, as we discuss some common myths associated with indexes and then dive into the myths to demonstrate how they can be debunked. At the end of the session, you'll know a few more things about indexes and leave armed with scripts that can help you debunk these myths on your own.

Click here to view the full webinar!

Follow-Up Questions:

Q: Does indexing even matter now with Hekaton in place?

Absolutely. In-memory OLTP (hekaton) only satisfies some use cases to data needs. Its great for high throughput OLTP tables. That doesn't necessarily make it a good fit for reference tables, slowly changing OLTP, tables requiring LOB data types, or nearly all data warehouse solutions.

Q: How do you recommend finding the indexes that can use or benefit from tuning fill factor?

I look at sys.dm_db_index_operational_stats and sys.dm_db_index_physical_stats.  The first dynamic management object provides information on the number of allocations; which relates to the rate in which page splits are occurring.  Then look at the second dynamic management object to determine the indexes that are fragmented.  After identification, then look at the rate in which these indexes need to be rebuilt and adjust the fill factor on those that are rebuilt most often.

Q: Do you think it is better to have many single column non-clustered indexes or a fewer number of multi-column non-clustered indexes?

It's not so something that a single pattern can fit all tables and workloads. In a general sense, non-clustered indexes with multiple or included columns can often provide a better performance lift over a large number of single column indexes. Of course, YMMV.

Q: We have seen some indexes getting fragmented few minutes after we rebuild/reorganize them. What could be the reason?

The main reason would be that the rows in the table are variable length and volatile in those lengths. After the rebuilds and reorganizations, the rows are being modified and page splits are occurring.  A secondary reason could be an database shrink, it that is happening, just stop doing that immediately. There are other reasons, but these are where I'd start looking.

Q: Would an index rebuild, physically order the primary clustered index?

The index rebuild will resort the pages physically in the database, it will not physically sort the data on the pages. This shouldn't be considered an issue. Getting to the data is the primary concern and once the page is accessed, there it is.