By: Brady Upton | Comments | Related: > 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.)
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:
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've set the Ignore duplicate values to "False"
If I try to INSERT a duplicate value for rowguid, I get the following error:
If I change the Ignore duplicate values to "True" and try to INSERT a duplicate value for rowguid I get the following:
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.
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.
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.
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.
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
