Is the % of an index fragmentation in Log shipped / Mirror database equal to the Primary/Source database?
Suppose there is Log shipping or Mirror between database X and database Y.
The mdf/ldf file(s) of the X DB and Y DB located in different storages.
I take some index in the primary db X and estimate its fragmentation by
using “dm_db_index_physical_stats” and do the estimation of that index
also in database Y.
SELECT dm.object_id AS objectid,
dm.index_id AS indexid,
QUOTENAME(i.name) AS name,
dm.partition_number AS partitionnum,
dm.avg_fragmentation_in_percent AS Fragmentation,
i.fill_factor AS [Fillfactor],
dm.page_count,
CASE WHEN dm.alloc_unit_type_desc <> 'IN_ROW_DATA'
OR (t.lob_data_space_id = 1 AND dm.index_type_desc <> 'NONCLUSTERED INDEX')
THEN 'OFF' ELSE 'ON'
END as [Online]
FROM [sys].[dm_db_index_physical_stats] (DB_ID(), NULL, NULL , NULL, 'LIMITED') dm
INNER JOIN [sys].[tables] t ON dm.object_id = t.object_id
INNER JOIN [sys].[indexes] i ON dm.object_id = i.object_id AND dm.index_id = i.index_id
WHERE dm.avg_fragmentation_in_percent > 0
AND dm.page_count > 1000
AND dm.index_id > 0
Let`s assume that the db X and Y are fully synchronized,
meaning all transactions from db X were transferred to db Y.
The question is: Is the % of the index fragmentation equal in db X and Y?
The answer is Yes.
Because every log record gets replayed on the secondary.
Every page split and every allocation is fully logged and then replayed
exactly the same on the secondary.
You may ask, what for you need to know the % of fragmentation
on secondary server. Well, first of all, estimation of the % of fragmentation
by using the “dm_db_index_physical_stats” in Production site may cause to
IO load. So we can estimate the fragmentation on the secondary server.
Secondary, it is always good to know and understand how it is working.
Here more info:
http://www.sqlsoldier.com/wp/sqlserver/whatdoesredonemean
The mdf/ldf file(s) of the X DB and Y DB located in different storages.
I take some index in the primary db X and estimate its fragmentation by
using “dm_db_index_physical_stats” and do the estimation of that index
also in database Y.
SELECT dm.object_id AS objectid,
dm.index_id AS indexid,
QUOTENAME(i.name) AS name,
dm.partition_number AS partitionnum,
dm.avg_fragmentation_in_percent AS Fragmentation,
i.fill_factor AS [Fillfactor],
dm.page_count,
CASE WHEN dm.alloc_unit_type_desc <> 'IN_ROW_DATA'
OR (t.lob_data_space_id = 1 AND dm.index_type_desc <> 'NONCLUSTERED INDEX')
THEN 'OFF' ELSE 'ON'
END as [Online]
FROM [sys].[dm_db_index_physical_stats] (DB_ID(), NULL, NULL , NULL, 'LIMITED') dm
INNER JOIN [sys].[tables] t ON dm.object_id = t.object_id
INNER JOIN [sys].[indexes] i ON dm.object_id = i.object_id AND dm.index_id = i.index_id
WHERE dm.avg_fragmentation_in_percent > 0
AND dm.page_count > 1000
AND dm.index_id > 0
Let`s assume that the db X and Y are fully synchronized,
meaning all transactions from db X were transferred to db Y.
The question is: Is the % of the index fragmentation equal in db X and Y?
The answer is Yes.
Because every log record gets replayed on the secondary.
Every page split and every allocation is fully logged and then replayed
exactly the same on the secondary.
You may ask, what for you need to know the % of fragmentation
on secondary server. Well, first of all, estimation of the % of fragmentation
by using the “dm_db_index_physical_stats” in Production site may cause to
IO load. So we can estimate the fragmentation on the secondary server.
Secondary, it is always good to know and understand how it is working.
Here more info:
http://www.sqlsoldier.com/wp/sqlserver/whatdoesredonemean