Question

我有一个由一些申请创建的数据库。整个数据库超过50克布,一些后备问题正在出现,我的任务是尽可能少地利用这一数据库。

尤其是一个表(22克b),其中16.5克比按指数计算,其余5.5克比为数据。它的浏览量略多于12 000。

否你告诉我,是否有可能缩小指数? 我已经尝试重建、重组、重新调整集群指数、清理。我还知道,Nvarchar型的面积比alchar高出两倍,因此,我将一栏改为alchar,但由于我只节省了约2克b(1克b,1克b)。

这里是本表的缩略语(面粉0 和fld1 始终是联合国利比里亚部队):

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

Answer 1

审视您的表格定义

主要关键因素:

You have a char(32) clustered index. This 32 bytes appears in every non-clustered index

专栏:

Why char(32)? -> varchar(32)
Why nvarchar()? -> varchar
Why datetime? -> smalldatetime
Why ntext? -> varchar(max)

指数:

Can you use INCLUDE rather than having key columns

Your main issue is bad choice of clustered index. If you can t fix that, pretty much everything else is pointless: unless you delete data...

当你改为var时,你节省了22GB中的2GB。这9%是相当合理的,没有其他选择。这还表明,你不需要 n。

If you change to an int surrogate key, you d save 28 bytes per row per non-clustered index. That s 3.7 GB minimum (12 x 12,000,000 x 28) but will be more because of more rows per page. And it s transparent to the client code.

然后,你开始检查指数使用情况......

然而,你应研究能力规划和数据增长计划。我在此建议的变化将减少增长rate<>em>以及目前的规模,但如果需要另外5 000万增长,则需要为此作出规划。例如,你能否表达你们的支持?

Answer 2

Find indexes not in use and remove them. This will also cut down on the amount of writes the drive has to make for dui s (deletes, updates, and inserts). See Brent Ozar s blog on how to do this: http://www.toadworld.com/platforms/sql-server/w/wiki/10062.find-indexes-not-in-use

基本来说,如果你的读写能力低(和带;0.1),那么该指数就伤害了你,超出了帮助程度,甚至需要走任何路。在放弃指数之前,你需要仔细考虑。

http://sqlfool.com/2009/04/filtered-indexes-what-you-need-to-know/“rel=“nofollow” http://sqlfool.com/2009/04/filtered-indexes-what-you-need-to-know/。

Answer 3

如果所有指数都得到实际使用,并且放弃了你不需要的指数,但总的来说,you正在以错误的方式处理这一问题:——即使你今天设法使数据库更小,它将在几个月内保持增长和。

除非该数据库载有你可以删除的大量不必要数据和(或)指数,否则,you必须找到一种途径来管理这种规模的数据库。

在你开始减少一栏和索引、改变一栏数据类型之前,你必须绝对确定这将如何影响应用程序的运作和业绩。然而,这是第3个政党软件,其开发商将在今后几天内不回答你的问题(y do not t work until end of the year),因此there绝对不能预测这种盲人数据库结构变化的所有负面影响。这一制度可能立即失败,但你的变动加上未来的软件更新(人们不了解你所作的修改)可能会产生灾难性的结果。

Answer 4

Definately review the index, do you really need all those indexes of the pattern [POOLID],[FLD]? If their existance can t be justified, then they shouldn t exist.

I m assuming the DOCID etc are GUIDS without hyphens. If you could start again, I would chose to use the inbuilt guid type rather than char32, which would halve the size of the indecies you have, but that s not a simple change because microsoft were silly and made it so you had to put hyphens in their guid types and then the application has to have extra space in structures or translate the hyphens out.

Answer 5

After playing around with this problem I came to the final stage where: - data reduced from 5 451 477 MB to 4 088 609 MB - indexes reduced from 15 361 391 MB to 6 003 094 MB

我在此采取的步骤(也许对一些人有用):

Changed 9 column types from nvarchar to varchar - data: 4 460 305 MB, indexes - 14 456 383 MB
Removed reduntant columns which occured in more than one index - data: 4 460 305 MB, indexes - 9 592 320 MB
Changed two columns from char(32) to varchar(32) and one column from ntext to varchar(max) - data: 4 088 609 MB, indexes: 9 294 117 MB
Created new integer indentity column, deleted clustered index which was made from DOCID char(32) column, created new non-clustered index with DOCID column, created new clustered index with newly added identity column - data: 4 088 609 MB, indexes: 6 003 094 MB

Thank You for helping me to solve this problem :)

友情链接