mssqltips logo

SQL Server Index Properties in Management Studio

By:   |   Updated: 2013-11-07   |   Comments   |   Related: More > Indexing

Problem

I'm creating an index in SQL Server using SSMS and I'm a little overwhelmed with all the index properties. Can you explain to me what each property is meant for and the options I should take?

Solution

Understanding indexes and how they work can be complicated enough for a Jr. DBA, but throw in all the different options and properties and an index can soon be overwhelming. In this tip, I'll discuss the different options available when creating a basic index.

To create an index using SSMS, expand the tree for a table and right click on Indexes and select New Index: (This tip will not discuss the anatomy of an index, but will focus on the properties.)

I'm creating an index in SQL Server using SSMS and I'm a little overwhelmed with all the index properties

After choosing the index type, you will notice that there a few tabs on the left side that will define how your index will act. These tabs will differ between SSMS versions but basically have the same information. In this tip, we'll be using SQL Server 2012 SSMS.

The first tab, General, is where you can set the index name, the key columns, and the included columns (if any). This tab also shows the table name and index type you selected:

In this tip, we'll be using SQL Server 2012 SSMS

The next tab, Options, is where you can view or modify the properties for the index:

The next tab, Options, is where you can view or modify the properties for the index

Let's go over each property.

Index Properties Options Page

We will begin with the options page.

Auto recompute statistics

This property defines whether or not you want SQL Server to automatically update the index statistics. Best practice is to leave this option set to True, otherwise you will have to manually update the statistics.

According to Microsoft, statistics are considered outdated when the following happens:

  • The table size has gone from 0 to >0 rows.
  • The number of rows in the table when the statistics were gathered was 500 or less, and the column modification counters (colmodctr) of the leading column of the statistics object has changed by more than 500 since then.
  • The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered.

Outdated statistics can lead to performance problems.

As the link above states, the statistics auto update is triggered by query optimization or by execution of a complied plan, and it involves only a subset of the columns referred to in the query.

Ignore duplicate values

This property specifies where a duplicate key value can be inserted into the column that is part the index. If set to "True", SQL Server will issue a warning when an INSERT statement is about to create a duplicate key and will ignore the duplicate row. If this option is set to "False", SQL Server will issue an error message and rolls back the INSERT statement.

Example:

In this example (AdventureWorks2012.Person.Person), I have a Non-clustered, unique index. My key column is rowguid.

I have a Non-clustered, unique index. My key column is rowguid

I've set the Ignore duplicate values to "False"

I've set the Ignore duplicate values to

If I try to INSERT a duplicate value for rowguid, I get the following error:

Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'Person.Person' with unique index 'AK_Person_rowguid'. The duplicate key value is (92c4279f-1207-48a3-8448-4636514eb7e2). The statement has been terminated.

If I change the Ignore duplicate values to "True" and try to INSERT a duplicate value for rowguid I get the following:

(0 row(s) affected)

As you can see, neither of these inserted the duplicate value because it was a UNIQUE index but one returned an error message and ended the statement while the other didn't return an error. If I was inserting multiple records the first message would have rolled back the transaction while the second message would have inserted all the unique values and skipped over the unique record.

The default value for this option is "False" and can only be used on UNIQUE indexes.

Allow Row Locks

The Allow Row Lock property specifies whether or not to allow row level locking. By default, this property is set to "True" and in most cases should be left this way. Setting the option to "False" can speed up index maintenance, but is more likely to block other user's.

Note: It's usually more beneficial to let SQL Server manage the locking behaviors

Allow Page Locks

Like the Row Lock property, this property specifies whether or not to allow page level locking. It should be left at its default state of "True" as well. Setting this option to "False" may reduce temporarily blocking of other users, but it can slow down index maintenance.

Note: It's usually more beneficial to let SQL Server manage the locking behaviors.

Allow online DML processing

This property allows concurrent user access to the underlying table or clustered index data and any associated non-clustered indexes during the index operation.

In more simple terms, if this property is set to "True" it will allow index maintenance to be performed online. This is essential if you run in a 24x7 uptime environment. Also, this property is only configurable in Enterprise or Developer Editions of SQL Server. If this property is set to "False" it may speed up index maintenance, but will probably block other users when it needs to take the index offline.

Maximum degree of parallelism

This property limits the number of processors to use during parallel plan execution. The default value, 0, uses the actual number of available processors. Max degree of parallelism can be set at the server level or at the index level starting in SQL Server 2005. Find out more about Max Degree of Parallelism for Indexes from Jeremy Kadlec's tip.

Sort in Tempdb

This property will store the sort results used to build an index in TempDB. If this property is set to "True", SQL Server uses temporary storage to sort and perform other intermediate tasks while creating or rebuilding an index. This storage can be used from the user database, or the TempDB.

Note: If setting this option to "True" make sure the disk subsystem that TempDB resides on has configured and tuned accordingly and that it is large enough to accommodate the temporary space that is required to create or rebuild an index.

Fill Factor

The fill factor property is probably the most popular of all the index properties. This specifies how full SQL Server should make the leaf level of each index page when creating the index. For example, if you set the fill factor to 80%, SQL Server will try to leave 20% of each leaf page empty. Bru Medishetty wrote and excellent tip on understanding this option here.

Pad Index

This property defines the space to leave open on each page in the intermediate (or non-leaf pages) levels of the index. This option is only applicable when Fill Factor is set as it uses the same percentage as Fill Factor.

That finishes up the Options tab of the index properties. This is where you will do most of your index configuration. Note that different types of indexes may or may not have these options.

Index Properties Storage Page

The next tab, Storage, is where you can specify where to store the index.

The next tab, Storage, is where you can specify where to store the index

By default, SQL Server will store indexes in the PRIMARY filegroup, however, if you have multiple filegroups setup, for example, one for indexes, this is where you would change this. Depending on how many and how large your indexes are, it may be a good idea to create a filegroup on a separate set of disks and store your indexes there. This could help with performance and storage space.

The other option is to store the index on a partition scheme. More about partitioning can be found here.

Index Properties Filter Page

The fourth tab down, Filter, is where you can add a WHERE clause to be used in the index definition.

The fourth tab down, Filter, is where you can add a WHERE clause to be used in the index definition

Filtered indexes were introduced in SQL Server 2008 and can offer a huge gain in performance and reduce maintenance and storage costs.

For example, if we need to find the employees who are Database Administrators you could create an index on the LoginID where the Job Title equals Database Administrator and when this type of query is performed it will run more efficient than a full table index.

Arshad Ali wrote an excellent tip that explains filtered indexes in greater detail here.

Index Properties Fragmentation Page

The last tab, Fragmentation, is basically a summary page of how fragmented the index may or may not be. There are no configurable options on this tab.

The last tab, Fragmentation, is basically a summary page of how fragmented the index may or may not be

Let's briefly go over each property.

Page fullness

This property defines how full the index page is. 100% means that the pages are completely full, for example. If you created an index with a fill factor of 60%, the page would be around 60% full, etc.

Total fragmentation

This property displays the total fragmentation percentage. This indicates the numbers of pages that are not stored in order. If this percentage is above 30%, I usually will rebuild the index to reorder the pages.

Average row size

This displays the average size of a leaf level row.

Depth

The number of levels in the index, including the leaf level.

Forwarded records

A forwarded record is a record that outgrows the page and the database engine uses pointers to reference the data. This property displays the number of forwarded records. You can find more about forwarded records from a tip I wrote earlier here.

Ghost rows

Ghost rows are the number of rows that are marked as deleted but not yet removed. When the server is idle, a clean-up thread will run and remove these rows.

Index type

This property displays the type of index. This is also displayed on the General tab.

Leaf-level rows

This property displays the number of leaf level rows.

Maximum row size

This property displays the maximum leaf-level row size.

Minimum row size

This property displays the minimum leaf-level row size.

Pages

This property displays the number of data pages the index is using.

Partition ID

This property defines the partition ID of the b-tree containing the index.

Version ghost rows

Version ghost rows specify the number of ghost records that are being retained due to snapshot isolation transactions

Next Steps
  • This tip focuses on configuring/viewing options using SSMS. You can also configure these properties using the CREATE or ALTER INDEX statement.
  • The sys.indexes view will display a lot of information regarding indexes as well. Using this view you can display information such as name, type, uniqueness and other properties listed under the Options tab in SSMS.


Last Updated: 2013-11-07


get scripts

next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools