0

I am running SQL Server 2012 and I am wondering if I create a clustered index if, as time goes on and users add records, if the RDBMS will maintain the clustered index on table change or have to do something in say for example a daily SSIS task to maintain the index.

1
  • What exactly are you asking? Are you asking "does the server insert/update/delete new rows"? (Yes it does) Or are you asking "is there anything I need to do to the index on a regular basis to keep it optimum"? (Perhaps, see answer below) Jun 20 at 20:38

1 Answer 1

4

There's no difference between a clustered and a non-clustered index from an index maintenance perspective.

If you add rows and they aren't steadily increasing (or decreasing) for the index key, you end up with page splits. Such a page split result in two things:

External fragmentation: When SQL Server follow the linked list do to a scan or partial scan, it will jump back and forth on the disk. This is pretty insignificant with modern disk subsystem (as compared to when we have single spinning disks under our SQL Servers).

Internal fragmentation: After a page split happened, you get two pages which are each about 50% full. A lot of page splits means higher number of of pages. OTOH, when the next row is to be inserted on such a page, there's already space for that row and you don't get a page split at the point in time. Letting the pages average on a certain fill factor can be a good thing, instead of rebuilding the index over and over again, hopelessly trying to reduce internal fragmentation (or even deliberately leaving free space on the pages - end up in the same situation as before the rebuild).

I did some measurement to determine what performance differences I could see between a ridiculously fragmented index (>99%) and an index with 0 fragmentation. I measured them from low to insignificant. The code I was using is also available if you want to run the test in your environment: https://sqlblog.karaszi.com/index-fragmentation-revisited/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.