• Article Topics

  • Become a member of the SQLDBASupport community and receive notifications of new posts by email

    Join 82 other followers

  • Are you writer? Become an Author! Mail to

    amitvaid81 @ gmail dot com

  • Founder

    This is journey of a SQL DBA. I now work as a Sr. Database Architect and Solution Consultant, generally in performance tuning, clustering or high availability. The opinions expressed herein are my own personal opinions through real time experience.

    Amit Vaid

  • Advertisements

Tips on Optimizing SQL Server Non-Clustered Indexes

Non-clustered indexes are best for queries:

– That return few rows (including just one row) and where the index has good selectivity (generally above 95%).
– That retrieve small ranges of data (not large ranges). Clustered indexes perform better for large range queries.

When you think of page splits, you normally only think of clustered indexes. This is because clustered indexes enforce the physical order of the index, and page splitting can be a problem if the clustered index is based on a non-incrementing column. But what has this to do with non-clustered indexes? While non-clustered indexes use a clustered index (assuming the table is not a heap) as their key, most people don’t realize that non-clustered indexes can suffer from page splitting, and because of this, need to have an appropriate fillfactor and pad_index set for them.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: