Indexing

Sometimes as a DBA we need to generate a T-SQL script for dropping and creating indexes in our SQL Server databases. However, SQL Server Management Studio does not have an easy way via the wizard to complete these tasks all at the same time. In this tip,

I'm creating an index in SQL Server using SSMS and I'm a little overwhelmed with all the index properties. In this tip we explain what each property is meant for and the options you should take.

SQL Server Clustered indexes can have enormous implications for performance of operations on a table. But are there times when a SQL Server non-clustered index would perform better than a clustered index for the same operation? Are there any trade-offs t

My database has many foreign key constraints to enforce referential integrity. Many of them are composite indexes and often used as covered index to optimize application performance. We review indexes periodically, add or remove columns from existing inde

Indexes are a great way to improve the performance of queries. However, they require regular monitoring and maintenance to ensure that the system continues to function smoothly. If indexes are not monitored regularly and appropriate action not taken then

I have been trying to tune my SQL Server and I noticed a few database settings for statistics. I also read the previous tip on automatic statistics updates. Could you please describe the Auto Create Statistics Async option? How is this configured and whe

As databases grow, it often becomes necessary to add new I/O paths to accommodate the growing space. Even without growth that requires this scale, it can still be useful to utilize multiple I/O devices to spread out the load. One way that you can make opt

Today, a developer reported that he is not able to create an index in our development environment and is getting this error: Msg 8606, Level 17, State 1, Line 1 This index operation requires 1024 KB of memory per DOP. The total requirement of 2048 KB for

The amount of data in data warehouses is growing rapidly. At the same time, the query performance against these same data warehouses is degrading. I heard SQL Server 2012 introduces a new way of creating/managing/storing indexes which improves the perfor

I am new to SQL Server statistics. Could you please describe what are the benefits of using the Auto Create Statistics and Auto Update Statistics options? I am also curious about how these statistics are automatically created and updated. Check out this

I was checking the statistics objects in my Microsoft SQL Server database and found some statistics with strange names. Could you please explain what the _WA_Sys... statistics are? Why are they created? Can I safely delete them? Do they differ from the ot

In the previous tip we learned that outdated statistics can cause SQL Server performance problems. My question is: How can I find out which statistics are outdated in my SQL Server database?

This tip will look at the order of the columns in your SQL Server indexes and how this order impacts query plans and performance.

A new feature in SQL Server 2012 is the Columnstore Index which can be used to significantly improve query performance. In this tip we will take a look of how it works and how we can use it.

I know it is a general best practice to have a clustered index on all of your SQL Server tables. Unfortunately, everyone that works with SQL Server in our company does not know that. I think I have uncovered a pattern where a number of tables do not hav

When you create a non unique clustered index, SQL Server creates a hidden 4 byte uniquifier column that ensures that all rows in the index are distinctly identifiable. However, SQL Server will only use the uniquifier when necessary. What exactly does this

When tuning indexes, it is somewhat difficult to tell how much impact the changes you have made will have on a query. Other than just looking at the different execution plans, is there an easy way to compare the queries using the old and new indexes?

While looking through the new features and improvements in SQL Server 2008 Management Studio (SSMS) we found a potentially interesting one called Missing Index Hints. Database Developers and Administrators can use Missing Index Hints feature to quickly id

In this tip we look at how to use and understanding the SQL Server index fill factor setting.

Learn how to store data in SQL Server where the data is over 900 bytes and how you can index this data.

I have seen your tips on indexing and I think they are great. I am relatively new to SQL Server and need to understand the basics. I need some additional background information to understand what sorts of indexing options are available. Hopefully that

In this, the third of the four part series on persisting the rowset results from the indexing Dynamic Management Views cached in temporary internal SQL Server structures we will explore what is required to store the missing index-related metadata. Part o

While looking through the new features in SQL Server 2005 and SQL Server 2008 we found a potentially interesting one called Disabling Indexes, which can be used to disable indexes on a table or a view. Can you give us a detailed explanation of how we go a

SQL Server 2008 introduces Filtered Indexes which is an index with a WHERE clause. For example, you have a lot of NULL values in a column and you want to retrieve records with only non-NULL values (in SQL Server 2008, this is called Sparse Column). Or in