Page count and fragmentation

I was working on writing a maintenance script for SQL Server that would perform a rebuild on indexes with fragmentation of 30% or above and re-organize indexes with fragmentation of below 30% and found that page count should also be taken into an account. if a page count is less than 1000 then rebuilding an index will not only have no effect of fragmentation but also rebuild procedure will be more costly than fragmentation of an index with 1000 pages or less.
As a result I changed my query slightly:

SELECT OBJECT_NAME(ps.object_id) As TableName, i.name As IndexName,ps.index_type_desc As IndexType, ps.index_depth, ps.index_level, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID('DBName'), null, null, null, 'LIMITED') as ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id 
WHERE NOT i.name IS NULL and ps.avg_fragmentation_in_percent >= 30 AND ps.page_count > 1000

Index defragmentation

Fragmentation can be defined as any condition that cause more than optimal amount of disk I/O to be performed in accessing a table or cause the longer disk I/O. For optimal performance of SQL queries, the data pages of tables are as contiguous as possible and pages are as fully packed as possible. Fragmentation breaks this rule thus reducing the performance of the queries. There are two levels of fragmentation: file system level (physical disk fragmentation) and index level fragmentation.

Physical disk fragmentation is the fragmentation of the database file in the file system, when file system cannot allocate contiguous space for it. As a result disk head has to move back and forth when reading from the datbase files. That happens when database file is stored on the same disk as other files, such as OS, application, or log files or when database grows frequently and in small chunks. The way to avoid it is to allocate enough space originally and to specify growth option to allocate larger chunks instead of small ones.

Before running windows defragmentation tool, SQL Server needs to be stopped, otherwise database file will be skipped.

Index fragmentation can be reduced by selecting an appropriate fill factor when creating the index.

There are two ways to defragment indexes: rebuild and reorganize.

When you reorganize an index, SQL Server physically reorders the leaf-level pages to match logical order of the leaf nodes. The process does not allocate new pages but only uses existing ones, compacting the indexes. Reorganization uses minimal resources and does not block queries or updates.

Reorganization should only be used on lightly fragmented indexes (no more than 30% fragmentation), heavily fragmented indexes should be rebuilt.

The sql below reorganizes the specified index:

ALTER INDEX index_name 
ON DBName.TableName
REORGANIZE

The important thing to keep in mind is that if you try to reorganize or rebuild an index with few pages, most likely nothing will change.

Rebuilding an index involves dropping the original index and building a new one, so that index starts clean, with minimal fragmentation. Index can be rebuilt offline and online. Rebuilding is done the same was as reorganizing except REBUILD keyword is used instead of REORGANIZE:

ALTER INDEX index_name 
ON DBName.TableName
REBUILD

if you want to add some options to your rebuild, use WITH clause

ALTER INDEX index_name 
ON DBName.TableName 
REBUILD WITH (
  FILLFACTOR = 60,
  ONLINE = ON
)