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:
- How that table can take 61 GB of Unused space for every 9 GB of data with having page fullness of 99.84%?
- Why my clustered index is fragmented so high (91,77%)?
- Why my table using 61GB of Unused space for every 9GB of data?
- 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
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]