1

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!

AgileJon
  • 425
  • 1
  • 5
  • 9

2 Answers2

2

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.

Sankar Reddy
  • 1,374
  • 8
  • 8
1

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.

SqlACID
  • 2,166
  • 18
  • 18