I've got a table in my SQL Server 2005 database with two indices that are heavily fragmented (33.3% and 85.7%). The table looks like the following:
CREATE TABLE [dbo].[Seasons](
[SeasonID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[TeamID] [bigint] NOT NULL,
[Year] [smallint] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Seasons] PRIMARY KEY CLUSTERED
( [SeasonID] ASC )
The other index is on the TeamID column. When I attempt to rebuild the indices in management studio it tells me 'success' on each one. But, when I view the fragmentation again nothing has changed. The table only has 2300 rows in it. Not sure if this is relevant, but I also noticed that it takes a LONG time (on the order of 30 seconds) to pull up the fragmentation info when I right-click an index, choose properties, and then select the Fragmentation page.
Other indices in the DB rebuild without a problem.
Any idea why I can't actually rebuild these indices? Any idea why it takes sooo long to pull up the fragmentation information? Thanks!
Jon,
2300 rows and based on your schema, each row occupies about 118 bytes and in total comes around about 65 pages. In SQL Server a page is about 8 KB and you might already know this. In these small tables, fragmentation is NOT a big deal and you don't have to worry about them.
Initially space is allocated to tables as a single page allocation and once it has upto 3 extents then it will a uniform extent. The first few single page allocations spike up the fragmentation info. Hope this helps.
I've noticed the same thing on the fragmentation option, I find it easier to use DBCC SHOWCONTIG WITH ALL_INDEXES, TABLERESULTS to get the stats for all tables at once, or just do one table.
And I wouldn't worry about fragmentation levels on smaller tables, it seems they don't defragment sometimes, not sure if it's a fault in the defrag process, or the calculation of the fragmentation level, but with a couple of thousand rows it's not going to affect performance.