SQL Server 2005 table too big

Question

I have this table.

I don't know why this table is too big.

This is the table structure.

    CREATE TABLE [dbo].[ACI_HISCLI](
    [TER_CODI] [varchar](13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HIS_NUME] [int] NOT NULL,
    [HIS_FECH] [datetime] NOT NULL,
    [HIS_ESTA] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HIS_HORA] [datetime] NOT NULL,
    [MED_CODI] [decimal](13, 0) NOT NULL,
    [HIS_HOEN] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HIS_HODC] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HIS_MOTI] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_AODS] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_AODC] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_AOSS] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_AOSC] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ODQ1] [decimal](4, 2) NULL,
    [HIS_ODQ2] [int] NULL,
    [HIS_ODQ3] [decimal](4, 2) NULL,
    [HIS_ODQ4] [int] NULL,
    [HIS_OSQ1] [decimal](4, 2) NULL,
    [HIS_OSQ2] [int] NULL,
    [HIS_OSQ3] [decimal](4, 2) NULL,
    [HIS_OSQ4] [int] NULL,
    [HIS_ODR1] [decimal](4, 2) NULL,
    [HIS_ODR2] [decimal](4, 2) NULL,
    [HIS_ODR3] [int] NULL,
    [HIS_OSR1] [decimal](4, 2) NULL,
    [HIS_OSR2] [decimal](4, 2) NULL,
    [HIS_OSR3] [int] NULL,
    [HIS_ODS1] [decimal](4, 2) NULL,
    [HIS_ODS2] [decimal](4, 2) NULL,
    [HIS_ODS3] [int] NULL,
    [HIS_ODS4] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OSS1] [decimal](4, 2) NULL,
    [HIS_OSS2] [decimal](4, 2) NULL,
    [HIS_OSS3] [int] NULL,
    [HIS_OSS4] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ADI1] [decimal](3, 2) NULL,
    [HIS_ADI2] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ADI3] [int] NULL,
    [HIS_TON1] [datetime] NULL,
    [HIS_TOOD] [int] NULL,
    [HIS_TOOS] [int] NULL,
    [HIS_MOVI] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIOM] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_FOJO] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OTRO] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DIA_COD1] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DIA_COD2] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DIA_COD3] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_COND] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OBSE] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_IMA1] [image] NULL,
    [HIS_IMN1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_IMA2] [image] NULL,
    [HIS_IMN2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_CONS] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ORIL] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ORIT] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ORIP] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_INGS] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_INGA] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_INGC] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_INGE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_INGL] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_TAIN] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_PUIN] [int] NULL,
    [HIS_TEIN] [decimal](6, 2) NULL,
    [HIS_TAEG] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_PUEG] [int] NULL,
    [HIS_TEEG] [decimal](6, 2) NULL,
    [HIS_OBHE] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [USU_CREA] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HIS_ASPR] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PRO_CONS] [int] NULL,
    [MED_CODC] [decimal](13, 0) NULL,
    [ANE_CODI] [decimal](13, 0) NULL,
    [AYU_CODI] [decimal](13, 0) NULL,
    [INS_CODI] [decimal](13, 0) NULL,
    [HIS_DIPR] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIPO] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DECI] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_GODV] [int] NULL,
    [HIS_GODT] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_GOIV] [int] NULL,
    [HIS_GOIT] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIFO] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIOT] [varchar](51) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OBSD] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OBSS] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ODLA] [image] NULL,
    [HIS_ODFR] [image] NULL,
    [HIS_OILA] [image] NULL,
    [HIS_OIFR] [image] NULL,
    [HIS_ODSE] [image] NULL,
    [HIS_OISE] [image] NULL,
    [HIS_GOD1] [decimal](3, 2) NULL,
    [HIS_GOD2] [decimal](3, 2) NULL,
    [HIS_GOD3] [decimal](3, 2) NULL,
    [HIS_GOD4] [decimal](3, 2) NULL,
    [HIS_GOD5] [decimal](3, 2) NULL,
    [HIS_GOD6] [decimal](3, 2) NULL,
    [HIS_GOD7] [decimal](3, 2) NULL,
    [HIS_GOD8] [decimal](3, 2) NULL,
    [HIS_GOI1] [decimal](3, 2) NULL,
    [HIS_GOI2] [decimal](3, 2) NULL,
    [HIS_GOI3] [decimal](3, 2) NULL,
    [HIS_GOI4] [decimal](3, 2) NULL,
    [HIS_GOI5] [decimal](3, 2) NULL,
    [HIS_GOI6] [decimal](3, 2) NULL,
    [HIS_GOI7] [decimal](3, 2) NULL,
    [HIS_GOI8] [decimal](3, 2) NULL,
    [HIS_CONT] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_RESU] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_GLIC] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_HEMA] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_PAQU] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_CREA] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ELEC] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIO1] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIO2] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIO3] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIO4] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIO5] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OBEX] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OBST] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIT1] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIT2] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIT3] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_TANE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ENT_CODI] [numeric](13, 0) NULL,
    [HIS_DIDR] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIDO] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIPI] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DISA] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIO1] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIO2] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIO3] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_HOAN] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_NOAC] [varchar](120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_TACO] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_PACO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_DACO] [varchar](70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_IACO] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_NORE] [varchar](120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_TRES] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_PRES] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_DRES] [varchar](70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_IRES] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [USU_AUDI] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [FEC_AUDI] [datetime] NOT NULL,
    [TIP_AUDI] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [MOD_ORIG] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

with the command sp_spaceued ACI_HISCLI

this is the result:

name: ACI_HISCLI
rows: 270719
reserved:   64521288 KB
data:       64377992 KB
index_size:      152 Kb
unused:       143144 KB

the fields of type image, almost are null. This table have so much transactions for the users but is only text ant the size is 60 GB !! I dont know why this table is so big !. How can optimize this table or shrink this table. Im try with :

DBCC CLEANTABLE

and rebuild the index Thanks.

Solomon Rutzky · Accepted Answer

All of those TEXT and IMAGE fields take up 16 bytes each as they are pointers to where the data really resides (when there is data there). But I am almost certain that just like fixed-width datatypes (INT, BIGINT, DATETIME, etc.) they take up that space even when NULL.

Also, the table really has too many fields to be manageable. You should consider breaking it up into two (or more) tables that have the same PK, are FKed together, and have a 1-to-1 relationship. Fields that are not used that often should be moved to the secondary table.

EDIT:
The data-space used is also affected by the FILLFACTOR setting on the Clustered Index. Setting it too low, especially with such a large row-size, will cause very few rows to fit on the 8k datapage.

Also, sometimes space is not released when rows are deleted. Doing an index REBUILD on the Clustered Index would release that space (you will know if there was unreleased space after doing the REBUILD if the size drops quite a bit).

EDIT2:
Anyone using SQL Server 2005 and newer should look to convert fields using the old blob types (which are now deprecated) to the newer equivalents:

TEXT -> VARCHAR(MAX)
NTEXT -> NVARCHAR(MAX)
IMAGE -> VARBINARY(MAX)

SQL Server 2005 table too big

Answers (1)

Related Questions