• Adam Thurgar

Index fragmentation


A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index fragmentation. This is not a great response from a very expensive software provider. Especially when they have tables with no indexes, heap tables, do not use included columns on any indexes.

They blame the maintenance.

With the database maintenance (Ola Hallengren's stored procedures and jobs) that is run nightly we took a conservative approach. This is to ensure that we minimize the time taken.

Criteria:

Reorganize indexes with fragmentation between >20% and =<30%

Rebuild with fragmentation > 30%.

We only include an index if it has > 10 pages – so forget really small tables.

We did change this to be more aggressive, but the fragmentation was not the issue. Incorrect indexing was the problem, but the vendor has their standard response.

I still think that this is an outstanding blog on index fragmentation.

https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/


2 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t

Statistics sampling

DBA's and developers know the importance of having up to date statistics to ensure that query performance is optimal. Most of the time the statistics sampling percentage is left to the default. To und