mardi 4 août 2015

SQL amount of pages bigger than it should be

I have a DB in SQL Express 2012 and there is one particular table which I am struggling to understand. Said table is has a CI and the following properties:

CREATE TABLE [dbo].[Hourly_Flows](
    [S_ID] [smallint] NOT NULL,
    [FromID] [tinyint] NOT NULL,
    [ToID] [tinyint] NOT NULL,
    [Hour8760] [smallint] NOT NULL,
    [FromTo] [smallint] NULL,
    [ToFrom] [smallint] NULL,
 CONSTRAINT [PK__oHF_Hour__60BEC9D565570293] PRIMARY KEY CLUSTERED 
(
    [S_ID] ASC,
    [FromID] ASC,
    [ToID] ASC,
    [Hour8760] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I created a new empty DB, with the exact same table and inserted all the records into it, the size of the new and old table is below:

+--------------+--------------+--------------+
|    Table     |     Old      |     New      |
+--------------+--------------+--------------+
| RowCounts    | 109,911,720  | 109,911,720  |
| TotalPages   | 436,323      | 259,137      |
| UsedPages    | 436,270      | 259,098      |
| DataPages    | 434,434      | 258,009      |
| TotalSpaceMB | 3,408        | 2,024        |
| UsedSpaceMB  | 3,408        | 2,024        |
| DataSpaceMB  | 3,394        | 2,015        |
+--------------+--------------+--------------+

As you can see the amount of pages and size is much smaller in the new table. Before inserting the data in the original table I tried truncated the table, dropped and recreated, cleantable and always get the same results.

Not sure what I am missing here, I have the same results if I re-insert the data into the original table. The only other issue could be that the DB is growing over the 10GB size limit of SQL Express DBs, but not sure why this could make a difference.

The table used to have more columns which were deleted (hence we truncated it), we do this often but this is the first time it does not reduce its size.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire