Indexing

In this tip we look at the missing index recommendations that SQL Server automatically provides and why you should pay close attention to what is recommended and how you actually implement the new index.

Learn about when to consider using SQL Server Clustered Columnstore Index for ETL processes and when other alternatives may yield better performance.

In this tip we cover various things you should check before dropping unused SQL Server indexes.

Learn how a robust on-demand incremental SQL Server statistics update process on partitioned tables will boost performance stability, reduce resource consumption (I/O and CPU) and significantly shrink the maintenance window for very large tables.

Learn how to overcome the SQL Server Filtered Index UnMatchedIndexes issue with parameterized queries, query hints, index hints, Dynamic SQL and more.

A MSSQLTips webinar was presented about the columnstore index features in the SQL Server 2016 database engine. This tip answers some of the questions asked during the webinar.

In this tip we look at a script that can help you identify all foreign keys that do not have a corresponding index.

In SQL Server 2016 the maximum size of all index key columns has been extended to provide us with more flexibility when we design an index. In this tip we will see how it works.

Learn about the SQL Server 2016 ColumnStore Index String Predicate Pushdown performance improvements for data warehouses.

I've read many SQL Server best practice documents that state every table should have a clustered index as opposed to leaving it as a heap structure with nonclustered indexes. In this tip we will look at the differences.

You want a T-SQL tool that will check your user database for non-clustered indexes that are not being used and disable these indexes automatically. Check out this tip to learn more.

We can enforce uniqueness of values in specific column(s) in a SQL Server table by creating a unique constraint or a unique index on those column(s). Read this tip to learn about the difference.

Learn about the how to index foreign key columns as a SQL Server for Performance Tuning tip to improve your database application and reduce IO.

Learn about the SQL Server indexing options for data warehouses including columnstore, B-Tree and hybrid options with the associated performance and execution plan implications.

In this tip we will look at SQL Server column store index performance compared to traditional SQL Server indexes.

Index-usage statistics get re-initialized when the SQL Server instance in question gets restarted and, starting with SQL Server 2012, after an index rebuild. This can obscure the true impact of indexes in day-to-day operations and lead to false determinat

Although not as much of a performance issue as missing indexes where they could be used, having too many indexes defined on a table can cause a lot of unnecessary IO on your database as well as use up additional disk space. In this tip we look at how to f

Over the course of my career as a DBA I've read a lot of documentation and articles on database maintenance and more specifically around index maintenance. In this tip we take a closer look at how queries improve after an index rebuild versus an index reo

Learn how covering indexes can improve query performance.

I am sure sometimes as SQL Server DBAs and Developers we would like to report on more detailed information about indexes on a table. One of the primary means to find the information is sp_helpindex which is a system stored procedure to check index summar