SQL Indexes in order to speed up queries to the database. It is important that any index created is analyzed for its usage pattern; for example, will the query only be made once per day, or thousands of times in a loop? It might not be worth it to create an index for a query which is only executed once per day. The reason for this is that anytime an INSERT, UPDATE or DELETE statement is executed, any affected indexes must also be updated.

A side effect from a lot of INSERT, UDPATE or DELETE statements is that over time that index can become fragmented. Fragmentation occurs when SQL can no longer fit data into the free space allocated for an index, or space is removed within the middle of a page of data. It handles this by creating another page for the index to store its data in. Excessive fragmentation can lead to poor performance if a lot of records are being selected as the data is not kept contiguous. This can lead to increased IO and CPU. One workload that is particularly prone to cause heavy fragmentation is the upgrade process. After upgrade, a one-time full reindex should be considered.

SELECT s.DATABASE_ID,

s.OBJECT_ID,

s.INDEX_ID,

b.NAME,

s.AVG_FRAGMENTATION_IN_PERCENT

FROM sys.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) AS s

INNER JOIN sys.INDEXES AS b

ON s.OBJECT_ID = b.OBJECT_ID

AND s.INDEX_ID = b.INDEX_ID

WHERE s.DATABASE_ID = DB_ID()

ORDER BY s.AVG_FRAGMENTATION_IN_PERCENT DESC