I have a database created by some application. The entire database is more than 50 GB, some problems with backups occur, and my task is to get this database as little as possible.
Especially one table is very large (22 gb), of which 16.5 gb are taken by indices, the remaining 5.5 gb is data. It contains a little over 12 million rows.
Could you tell me if it is possible to reduce indexes? I have already tried rebuilding, reorganizing, recreating a clustered index, dbcc cleantable. I also know that the nvarchar type takes twice as much as the varchar, so I changed the column type to varchar, but thanks to this I only saved about 2 gb (1 gb for data and 1 gb for indexes).
Here is the sql for this table (fld0 and fld1 are always NULL):
CREATE TABLE [dbo].[DOC8](
[ASSOCIATION] [nvarchar](64) NULL DEFAULT (NULL),
[DOCID] [char](32) NOT NULL,
[FLD0] [nvarchar](255) NULL DEFAULT (NULL),
[FLD1] [nvarchar](2048) NULL DEFAULT (NULL),
[FLD10] [nvarchar](255) NULL DEFAULT (NULL),
[FLD2] [nvarchar](32) NULL DEFAULT (NULL),
[FLD3] [nvarchar](255) NULL DEFAULT (NULL),
[FLD4] [nvarchar](255) NULL DEFAULT (NULL),
[FLD5] [datetime] NULL DEFAULT (NULL),
[FLD6] [nvarchar](255) NULL DEFAULT (NULL),
[FLD7] [nvarchar](255) NULL DEFAULT (NULL),
[FLD8] [nvarchar](255) NULL DEFAULT (NULL),
[FLD9] [datetime] NULL DEFAULT (NULL),
[PARENTID] [char](32) NULL DEFAULT (NULL),
[POOLID] [char](32) NULL DEFAULT (NULL),
[PROPERTIES] [ntext] NULL DEFAULT (NULL),
[FLD11] [nvarchar](255) NULL DEFAULT (NULL),
[FLD12] [nvarchar](255) NULL DEFAULT (NULL),
PRIMARY KEY CLUSTERED
(
[DOCID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [DOC8_IDX_0] ON [dbo].[DOC8]
(
[ASSOCIATION] ASC,
[PARENTID] ASC,
[POOLID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [DOC8_IDX_1] ON [dbo].[DOC8]
(
[POOLID] ASC,
[FLD0] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [DOC8_IDX_10] ON [dbo].[DOC8]
(
[POOLID] ASC,
[FLD11] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [DOC8_IDX_11] ON [dbo].[DOC8]
(
[POOLID] ASC,
[FLD12] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [DOC8_IDX_2] ON [dbo].[DOC8]
(
[POOLID] ASC,
[FLD2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [DOC8_IDX_3] ON [dbo].[DOC8]
(
[POOLID] ASC,
[FLD3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [DOC8_IDX_4] ON [dbo].[DOC8]
(
[POOLID] ASC,
[FLD4] ASC,
[FLD5] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [DOC8_IDX_5] ON [dbo].[DOC8]
(
[POOLID] ASC,
[FLD6] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [DOC8_IDX_6] ON [dbo].[DOC8]
(
[POOLID] ASC,
[FLD7] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [DOC8_IDX_7] ON [dbo].[DOC8]
(
[POOLID] ASC,
[FLD8] ASC,
[FLD9] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [DOC8_IDX_8] ON [dbo].[DOC8]
(
[POOLID] ASC,
[FLD10] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [DOC8_IDX_9] ON [dbo].[DOC8]
(
[PARENTID] ASC,
[POOLID] ASC,
[DOCID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO