November 16, 2009 at 12:45 pm
Wow, and you rebuilt that with no change happening to anything? Sure the rebuild succeeded?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2009 at 12:57 pm
Interesting. When I ran a REBUILD from the prompt, it worked. Before, I had done it from the properties tab, and it hadn't. Strange. Well, I suppose that answers my question. Thanks for the help!
November 16, 2009 at 3:57 pm
Not exactly sure what is being referred to by 'properties tab' - but I always rebuild or reorganize indexes from T-SQL script, never the SSMS UI. Right-clicking on an index in Object Explorer and hitting 'rebuild' runs the following query first:
SELECT
fi.avg_fragmentation_in_percent AS [AverageFragmentation]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
INNER JOIN sys.dm_db_index_physical_stats(@database_id, NULL, NULL, NULL, 'LIMITED') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int)
WHERE
(i.name=@_msparam_2)and((tbl.name=@_msparam_3 and SCHEMA_NAME(tbl.schema_id)=@_msparam_4))
This is a dumb implementation. In an attempt to display the current average fragmentation figure for the selected index on the selected table, this query runs sys.dm_db_index_physical_stats in limited mode over the whole database.
Right-clicking on the index, choosing Properties, and moving to the Fragmentation node runs this gem:
SELECT
i.name AS [Index_Name],
CAST(i.index_id AS int) AS [Index_ID],
fi.index_depth AS [Depth],
fi.page_count AS [Pages],
fi.record_count AS [Rows],
fi.min_record_size_in_bytes AS [MinimumRecordSize],
fi.max_record_size_in_bytes AS [MaximumRecordSize],
fi.avg_record_size_in_bytes AS [AverageRecordSize],
fi.forwarded_record_count AS [ForwardedRecords],
fi.avg_page_space_used_in_percent AS [AveragePageDensity],
fi.index_type_desc AS [IndexType],
fi.partition_number AS [PartitionNumber],
fi.ghost_record_count AS [GhostRows],
fi.version_ghost_record_count AS [VersionGhostRows],
fi.avg_fragmentation_in_percent AS [AverageFragmentation]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
INNER JOIN sys.dm_db_index_physical_stats(@database_id, NULL, NULL, NULL, 'SAMPLED') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int)
WHERE
(i.name=@_msparam_2)and((tbl.name=@_msparam_3 and SCHEMA_NAME(tbl.schema_id)=@_msparam_4))
ORDER BY
[Index_Name] ASC
Again, this runs over the whole database - but in SAMPLED mode just for extra giggles.
If anyone has any idea how this misfeature has made it through so many versions of SSMS, please let me know. The above were generated from 2008 SSMS 10.0.2531.0
I love the 1,000 pages thing: some people are only happy if they have an arbitrary number to work with. I like to consolidate the initial mixed extents allocated to an object once it grows, so I routinely rebuild on tables with maybe only 50 pages. As for indexes of less than 1,000 pages being in memory...why? They're a bit more likely to be retained in bpool than data pages, but that's about it - there's no discrimination based on overall object size as far as I know.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply