Indexing

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

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 r

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 a

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 bee

In this tip we look at system tables to get better insight into our SQL Server indexes.

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 o

In this article, we look at how to create a SQL Server index that can satisfy multiple queries versus having to create multiple indexes.

In this tip we look at how you can get information about indexes using sysindexes.

This tip will show you what indexes are being used the most and least in your databases.

Index maintenance is key and this tip will help you with tracking index maintenance information.

Learn about non unique clustered indexes and duplicate value limits in this aticle.

In this tip we look at the impact of building SQL Server indexes in ascending versus descending order and the impact.

One very important design aspect when creating a new table is the decision to create or not create a clustered index. We will take a look at a few points to help with the decision.

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

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,

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 no

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

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

Learn how to use the SQL INDEXPROPERTY function to return various meta data about SQL Server indexes in a database.

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 ti

Sometimes there is a need to review all your indexes across your entire database. Check out this modified system stored procedure to get data about all indexes for a database.