Tips
Avoid Index Redundancy in SQL Server Tables
I'm trying to design indexes on a new Customer table and I have three separate query searches to satisfy. One query search is by lastname. The 2nd query search is by last name and first name. The last query search is by lastname and an active flag. How should I create my indexes?
Building SQL Server Indexes in Ascending vs Descending Order
When building indexes often the default options are used to create an index which creates the index in ascending order. This is usually the most logical way of creating an index, so the newest data or smallest value is at the top and the oldest or biggest value is at the end. Although searching an index works great by creating an index this way, but have you ever thought about the need to always return the most recent data first and ways you can create an index in descending order so the most recent data is always at the top of the index? We will take a look at how this works and the advantages of creating an index in descending vs ascending order.
Clustered Tables vs Heap Tables
One very important design aspect when creating a new table is the decision to create or not create a clustered index. A table that does not have a clustered index is referred to as a HEAP and a table that has a clustered index is referred to as a clustered table. A clustered table provides a few benefits over a heap such as physically storing the data based on the clustered index, the ability to use the index to find the rows quickly and the ability to reorganize the data by rebuilding the clustered index. Depending on the INSERT, UPDATE and DELETE activity against your tables your physical data can become very fragmented. So what can be done?
Deeper insight into unused indexes for SQL Server
One of the balancing acts of SQL Server is the use of indexes. Too few indexes can cause scans to occur which hurts performance and too many indexes causes overhead for index maintenance during data updates and also a bloated database. So what steps can be taken to determine which indexes are being used and how they are being used.
Disabling Indexes in SQL Server 2005 and SQL Server 2008
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 about using this new feature along with examples?
Finding a better candidate for your SQL Server clustered indexes
When creating tables it is difficult to determine exactly how the data will be accessed. Therefore when clustered indexes are chosen they are often just the ID column that makes the row unique. This may be a good choice, but once the application has been used and data access statistics are available you may need to go back and make some adjustments to your tables to ensure your clustered indexes are providing a benefit and not a drain on your applications. This tip shows a simple approach on how to determine a better candidate for your clustered indexes.
How can I get sysindexes information in Sql 2005?
How to create indexes on computed columns in SQL Server
In my recent article Using Computed Columns in SQL Server with Persisted Values I discussed how to create computed columns to improve performance in specific scenarios. In order to achieve maximum performance through computed columns one very important aspect that can also be implemented is creating indexes on these computed columns. There are certain requirements for creating indexes on computed columns and this tip shows you want needs to be done.
How to get index usage information in SQL Server
Databases have two primary storage needs; data pages and index pages. Understanding and viewing the actual data in your tables is pretty straightforward by running some sample queries to get an idea of what columns are being used as well as what type of data is actually being stored. On the flip side of this, it is often difficult to know exactly what indexes are being used and how they are being used. So how can you get a better understanding of how your indexes are being used and what operations are occurring (inserts, updates, deletes, selects)?
Improve SQL Server Performance with Covering Index Enhancements
The concept of index creation has always been a tricky orchestration. To maximize performance and indexes by the optimizer, queries should be covered by an index-that is, the index should include all columns requested in the query. There are limitations to creating indexes that make covering queries difficult, but a new feature in 2005 makes this easier.
Index Builds in SQL Server 2000 vs SQL Server 2005
Among many other changes between SQL Server 2000 to 2005, the index creation code has some subtle changes that are important to know and handle appropriately in scripts. It is important to be aware of these changes due to the beneficial performance gains with the proper indexes. Check out the index creation differences between SQL Server 2000 and 2005.
Index System Tables in SQL Server 2000 vs SQL Server 2005
The primary resource in SQL Server 2000 to capture index related
information was master.dbo.sysindexes. I used this system table to capture information on row and page counts or total size information for a specific table. I know with SQL Server 2005, it is not recommended to use
deprecated system tables. Unfortunately, I am having trouble figuring out
which new system objects to query in order to capture the index related
information that I am accustomed to with SQL Server 2000. As such, where
can I find this information using the new SQL Server 2005 catalog views?
Max Degree of Parallelism for Index Commands in SQL Server 2005
In SQL Server 2000 when creating an index, the max degree of parallelism or MAXDOP configuration was based on the server configuration. With SQL Server 2005, you are no longer constrained by the default settings. The MAXDOP value can be configured each time an index is created to maximize system performance.
Missing Index Feature of SQL Server 2008 Management Studio
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 identify the columns on which adding an index can help running the query faster. Can you give us a detailed explanation of how we go about using Missing Index Hints feature?
Non Unique Clustered Index and Duplicate Value limits
Retaining historical index usage statistics for SQL Server Part 1 of 3
Starting with Microsoft SQL Server 2005, DBAs were able to have a greater insight into the inner workings of their supported SQL Server instances through the use of Dynamic Management Views and Functions. The problem with some of this data is the values reset each time SQL is restarted. In this tip we take a look at how to keep some of this historical data.
Retaining historical index usage statistics for SQL Server Part 2 of 3
In part one of this series of persisting Dynamic Management View data that is stored in the cache and sourced from the Query Optimizer we examined the process for persisting data for the sys.dm_dm_index_usage_stats DMV. That DMV stores usage metrics (as the name implies) for all the indexes on a Microsoft SQL Server instance. Welcome to part two, in which we look at performing the same task for the sys.dm_db_index_operational_stats Dynamic Management Function (or DMF.) The process is identical, the code is however quite different due to the structural differences between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats. To recap why we would want to do this I suggest reviewing part one of the series. In short, we do so
Retaining historical index usage statistics for SQL Server Part 3 of 3
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 one of this series examined the process for persisting data for sys.dm_dm_index_usage_stats; the DMV pertaining to usage metrics for all the indexes on a Microsoft SQL Server instance. Part two centered on constructing a similar process for the sys.dm_db_index_operational_stats Dynamic Management Function. I suggest reviewing these precursor tips prior to tackling this one. As with the previous tips, the reason I perform these processes is to protect valuable index tuning information from
Retrieving SQL Server Index Properties with INDEXPROPERTY
SQL Server 2000 and 2005 have a built-in function called INDEXPROPERTY that allows you to return specific information about an index. This function can be called from a SELECT statement to return the results of one or more indexes.
SQL Server Filtered Indexes What They Are, How to Use and Performance Advantages
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 another scenario you have several categories of data in a particular column, but you often retrieve data only for a particular category value. In this tip, I am going to walk through what a Filtered Index is, how it differs from other indexes, its usage scenario, its benefits and limitations.
SQL Server Index Analysis Script for All Indexes on All Tables
Sometimes there is a need to review all your indexes across your entire database. The need might be for simple analysis, optimization or maybe just for documentation purposes. To simplify gathering index information for all tables across the entire database a simple tweak to the sp_helpindex stored procedure allows for the retrieval of index information for all of the tables. So instead of having to do this one table at a time, you can now do this one database at a time.
SQL Server Index Checklist
Indexing a SQL Server database in some respects is considered both an art and a science. Since this is the case, what are some considerations when designing indexes for a new database or an existing one in production? Are these the same types of steps or not? Do any best practices really exist when it comes to indexing? Where does indexing fall in the priority list from an application or production support perspective?
SQL Server Indexing Basics
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 can lead me into using some of the additional tips. Can you help me?
SQL Server Tables without a Clustered Index
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 have a clustered index. Can you provide a script or two to find out which tables do not have a clustered index? Check out this tip to learn more.
SQL Server Unique Constraints for Large Text Columns
We are storing large text and URLs that are over 900 bytes in some of our tables and have a requirement to enforce uniqueness in those columns. But SQL Server has a limitation that index size can't be over 900 bytes. How do I enforce uniqueness in these columns? In this tip we will look at various options to address this problem.
Tracking index maintanence information
Understanding and Examining the Uniquifier in SQL Server
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 mean? Does a new uniquifier column get added when you insert the first non unique row? Since the uniquifier is only four bytes, does that limit my table to 2,147,483,647 rows?
Understanding SQL Server Index Fill Factor Setting
Every database user, be it an end user, a developer or an administrator knows that indexes are one of the primary ways of making a query perform better. It is known that having indexes on large tables helps the Database Management System by using the existing indexes when queries need them by doing index seeks vs scans. When creating or rebuilding indexes you have the option of specifying a fill factor. What is an index fill factor? What is the significance of the value, when and why should it be changed? I was asked these questions quite a few times in training sessions and also found these questions in many forum posts. In this tip I will go over what fill factor is and how it impacts performance.
Understanding SQL Server Indexing
With so many aspects of SQL Server to cover and to write about, some of the basic principals are often overlooked. There have been several people that have asked questions about indexing along with a general overview of the differences of clustered and non clustered indexes. Based on the number of questions that we have received, this tip will discuss the differences of indexes and some general guidelines around indexing.
Using Hints To Test SQL Server Indexes
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?
What indexes are used most often (or least often) on my server?
Top 10
Understanding SQL Server Indexing
With so many aspects of SQL Server to cover and to write about, some of the basic principals are often overlooked. There have been several people that have asked questions about indexing along with a general overview of the differences of clustered and non clustered indexes. Based on the number of questions that we have received, this tip will discuss the differences of indexes and some general guidelines around indexing.
SQL Server Tables without a Clustered Index
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 have a clustered index. Can you provide a script or two to find out which tables do not have a clustered index? Check out this tip to learn more.
Retaining historical index usage statistics for SQL Server Part 1 of 3
Starting with Microsoft SQL Server 2005, DBAs were able to have a greater insight into the inner workings of their supported SQL Server instances through the use of Dynamic Management Views and Functions. The problem with some of this data is the values reset each time SQL is restarted. In this tip we take a look at how to keep some of this historical data.
How to get index usage information in SQL Server
Databases have two primary storage needs; data pages and index pages. Understanding and viewing the actual data in your tables is pretty straightforward by running some sample queries to get an idea of what columns are being used as well as what type of data is actually being stored. On the flip side of this, it is often difficult to know exactly what indexes are being used and how they are being used. So how can you get a better understanding of how your indexes are being used and what operations are occurring (inserts, updates, deletes, selects)?
Clustered Tables vs Heap Tables
One very important design aspect when creating a new table is the decision to create or not create a clustered index. A table that does not have a clustered index is referred to as a HEAP and a table that has a clustered index is referred to as a clustered table. A clustered table provides a few benefits over a heap such as physically storing the data based on the clustered index, the ability to use the index to find the rows quickly and the ability to reorganize the data by rebuilding the clustered index. Depending on the INSERT, UPDATE and DELETE activity against your tables your physical data can become very fragmented. So what can be done?
Improve SQL Server Performance with Covering Index Enhancements
The concept of index creation has always been a tricky orchestration. To maximize performance and indexes by the optimizer, queries should be covered by an index-that is, the index should include all columns requested in the query. There are limitations to creating indexes that make covering queries difficult, but a new feature in 2005 makes this easier.
SQL Server Indexing Basics
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 can lead me into using some of the additional tips. Can you help me?
SQL Server Index Checklist
Indexing a SQL Server database in some respects is considered both an art and a science. Since this is the case, what are some considerations when designing indexes for a new database or an existing one in production? Are these the same types of steps or not? Do any best practices really exist when it comes to indexing? Where does indexing fall in the priority list from an application or production support perspective?
Retaining historical index usage statistics for SQL Server Part 3 of 3
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 one of this series examined the process for persisting data for sys.dm_dm_index_usage_stats; the DMV pertaining to usage metrics for all the indexes on a Microsoft SQL Server instance. Part two centered on constructing a similar process for the sys.dm_db_index_operational_stats Dynamic Management Function. I suggest reviewing these precursor tips prior to tackling this one. As with the previous tips, the reason I perform these processes is to protect valuable index tuning information from
SQL Server Index Analysis Script for All Indexes on All Tables
Sometimes there is a need to review all your indexes across your entire database. The need might be for simple analysis, optimization or maybe just for documentation purposes. To simplify gathering index information for all tables across the entire database a simple tweak to the sp_helpindex stored procedure allows for the retrieval of index information for all of the tables. So instead of having to do this one table at a time, you can now do this one database at a time.
Last 10
SQL Server Tables without a Clustered Index
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 have a clustered index. Can you provide a script or two to find out which tables do not have a clustered index? Check out this tip to learn more.
Understanding and Examining the Uniquifier in SQL Server
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 mean? Does a new uniquifier column get added when you insert the first non unique row? Since the uniquifier is only four bytes, does that limit my table to 2,147,483,647 rows?
Using Hints To Test SQL Server Indexes
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?
Missing Index Feature of SQL Server 2008 Management Studio
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 identify the columns on which adding an index can help running the query faster. Can you give us a detailed explanation of how we go about using Missing Index Hints feature?
Understanding SQL Server Index Fill Factor Setting
Every database user, be it an end user, a developer or an administrator knows that indexes are one of the primary ways of making a query perform better. It is known that having indexes on large tables helps the Database Management System by using the existing indexes when queries need them by doing index seeks vs scans. When creating or rebuilding indexes you have the option of specifying a fill factor. What is an index fill factor? What is the significance of the value, when and why should it be changed? I was asked these questions quite a few times in training sessions and also found these questions in many forum posts. In this tip I will go over what fill factor is and how it impacts performance.
SQL Server Unique Constraints for Large Text Columns
We are storing large text and URLs that are over 900 bytes in some of our tables and have a requirement to enforce uniqueness in those columns. But SQL Server has a limitation that index size can't be over 900 bytes. How do I enforce uniqueness in these columns? In this tip we will look at various options to address this problem.
SQL Server Indexing Basics
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 can lead me into using some of the additional tips. Can you help me?
Retaining historical index usage statistics for SQL Server Part 3 of 3
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 one of this series examined the process for persisting data for sys.dm_dm_index_usage_stats; the DMV pertaining to usage metrics for all the indexes on a Microsoft SQL Server instance. Part two centered on constructing a similar process for the sys.dm_db_index_operational_stats Dynamic Management Function. I suggest reviewing these precursor tips prior to tackling this one. As with the previous tips, the reason I perform these processes is to protect valuable index tuning information from
Disabling Indexes in SQL Server 2005 and SQL Server 2008
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 about using this new feature along with examples?
SQL Server Filtered Indexes What They Are, How to Use and Performance Advantages
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 another scenario you have several categories of data in a particular column, but you often retrieve data only for a particular category value. In this tip, I am going to walk through what a Filtered Index is, how it differs from other indexes, its usage scenario, its benefits and limitations.
