We have 2 SQL servers in our test ENV, both with the same database on. There are some tables that are replicated from the DB on the one server to the DB on the other server. While we’re testing we sometimes “refresh” these servers with a fresh DB from PROD.
I’ve noticed that the index fragmentation Server A, DB X differs to the indexes on Server B, DB X. Why would this be?
I’m using the below query to return fragmentation. On Server A, DB X there are 90 indexes that are 50% or more fragmented and then on Server B, DB X there are only 50 odd.
SELECT S.name as 'Schema', T.name as 'Table', I.name as 'Index', DDIPS.avg_fragmentation_in_percent, DDIPS.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS INNER JOIN sys.tables T on T.object_id = DDIPS.object_id INNER JOIN sys.schemas S on T.schema_id = S.schema_id INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id WHERE DDIPS.database_id = DB_ID() and I.name is not null AND DDIPS.avg_fragmentation_in_percent > 0 ORDER BY DDIPS.avg_fragmentation_in_percent desc