April 20, 2017 at 8:37 pm
I see one index which even after rebuilding with online=on every day fragmentation is 85% .
Why and what are the fixes to defrag the index?
April 21, 2017 at 1:00 am
How many pages are in the index? How many writes vs. reads does the index encounter?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 21, 2017 at 1:23 am
Is the index on a UNIQUEIDENTIFIER column?
April 21, 2017 at 1:52 am
Hi,
as Jason's suggesting it's probably because it's a small table/index. Initially SQL Server allocates storage in what are called mixed extents - i.e. the extent is shared with other tables. These won't defragment, but then again fragmentation in a dataset that small isn't going to have a noticable performance impact anyway
“In general,fragmentation on small indexes is often not controllable. The pages of smallindexes are stored on mixed extents. Mixed extents are shared by up to eightobjects, so the fragmentation in a small index might not be reduced afterreorganizing or rebuilding the index. For more information about mixed extents,see Understanding Pages and Extents.”
https://social.technet.microsoft.com/Forums/Lync/en-US/b02ed2c9-38a7-4762-9bf8-1cf79638c8f2/fragmentation-does-not-reduce-for-some-tables?forum=sqltools
if you run this query, it also has a column for the number of pages, which will help you identify if this is the case
SELECT
dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count,
indexstats.record_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN
sys.tables dbtables
on dbtables.[object_id]= indexstats.[object_id]
INNER JOIN
sys.schemas dbschemas
on dbtables.[schema_id]= dbschemas.[schema_id]
INNER JOIN
sys.indexes AS dbindexes
ON dbindexes.[object_id] =indexstats.[object_id]
AND
indexstats.index_id =dbindexes.index_id
WHERE
indexstats.database_id = DB_ID()
ORDER BY
indexstats.avg_fragmentation_in_percent DESC;
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply