1

I have a problem with CLUSTERED INDEX, every one of my table have extemely high total fragmentation, around 92% with having page fullness in the same time at around 99%.

So in my understanding pages are out of order but are full so access to data is random, but if I generating report Disk Usage by Top Tables then I see that for example table with 136 million records have reserved 71 GB (9GB of Data, 50KB of index, and 61GB of Unused space)

I have questions:

  1. How that table can take 61 GB of Unused space for every 9 GB of data with having page fullness of 99.84%?
  2. Why my clustered index is fragmented so high (91,77%)?
  3. Why my table using 61GB of Unused space for every 9GB of data?

Enviroment:

  • table is stored on filegroup with 12 files
  • table has clustered index which is designed as (ID_SERVER, IDENTITY) so every server while inserting data requesting other page without all servers waiting for same page with data inserts
  • data from table is removed in order of identity column then for every page data always should be removed from the left, without leaving holes in pages
  • mssql server 2016

EDIT:

CREATE TABLE [dbo].[table](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [id_data] [int] NOT NULL,
    [col1] [datetime2](7) NOT NULL,
    [col2] [datetime2](7) NOT NULL,
    [col3] [tinyint] NOT NULL,
    [col4] [tinyint] NOT NULL,
    [col5] [decimal](19, 9) NOT NULL,
    [col6] [decimal](19, 9) NOT NULL,
 CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED 
(
    [id_data] ASC,
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.