April 24, 2012 at 6:57 am
Hi ...
I have a Script for rebuliding or reorganizing index based on Fragmentation level.
My code is capturing fragmentation information (before and after complting the job)and keeping the result in a table .
when i check the result in (reindex_history table)Pre and Post fragmentations are showing same.
i have a doubt in procedure whether it was writtening correct result or what might be the issue.
if i do manully rebuilt or reorganize the index in (management studio),Fragmentaion level getting change
April 24, 2012 at 7:10 am
How big are those indexes. If they're less than 8 pages in size, defragmenting might not work at all. I would suggest setting a low limit on defragmenting indexes. Somewhere between 100 & 1000 pages.. Microsoft recommends greater than 1000.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 24, 2012 at 8:42 am
Ex :
SELECT i.name AS IndexName,SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.[object_id] = object_id('workflowplugin_action') GROUP BY i.name ORDER BY i.name
as a result of above Qurey , each index size is showing 16 KB.
April 24, 2012 at 8:57 am
Simha24 (4/24/2012)
Ex :SELECT i.name AS IndexName,SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.[object_id] = object_id('workflowplugin_action') GROUP BY i.name ORDER BY i.name
as a result of above Qurey , each index size is showing 16 KB.
So 2 pages? That's not going to defrag well at all, if it defrags. It's likely to be stored on a mixed extent (an extent is a collection of 8 pages). Don't bother defragging these indexes. As I said, at least 100 pages, Microsoft says at least 1000.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply