时间戳

首页 > 解决方案 > 计算表中每列的总存储量

sql - 计算表中每列的总存储量

问题描述

我试图确定大表中每列的总存储量。其中有多个 nvarchar。

一列是 nvarchar(max) 并且在导入时，将 XML 的文本放入其中。正确处理后的记录。该列再次被清空为空字符串。

在 SQL Server“顶级表的磁盘使用情况”报告中，我看到以下内容。

金额记录：1 808 604

保留 (KB)：15 209 272

数据 (KB)：14 466 776

索引 (KB)：731 896

未使用 (KB)：10 600

所以我一直在寻找如何在表中找到这么多数据的位置。因为 nvarchar(max) 列几乎都是空的。

我对该表中的所有列进行了 sum(datalength(columnname)) 。并对所有列值求和。

这给了我：499 344 838 字节 = 0.499344838 GB

所以我现在想知道：

编辑：我已经做了一些插入数据的测试（见下面的循环）

通过这个测试，它的数据差异不大，+- 23 vs 27 MB。

我将尝试在生产表上进行重建，但我需要安排它。

Edit2：我用循环进行了 1000 次测试：插入 100 + set xml='' where xml<>'' 之后的结果是 264.008KB 数据。如果我进行重建，它会下降到 22.944KB。

所以看起来重建可以提供一个解决方案。但是任何关于这在生产环境中的密集程度的想法？如果我需要执行它，是否可以在我的应用程序中检测到它？

带索引的表定义

CREATE TABLE [dbo].[BATCH](
    [BATCH_PID] [bigint] IDENTITY(1,1) NOT NULL,
    [XML_CREATION_DATE] [datetime] NULL,
    [BATCH_REFERENCE] [nvarchar](50) NULL,
    [SOURCE] [nvarchar](50) NULL,
    [DOCUMENT_CLASS_FID] [int] NULL,
    [XML_NAME] [nvarchar](150) NULL,
    [XML_TYPE] [int] NULL,
    [XML] [nvarchar](max) NULL,
    [NUM_OF_DOCUMENTS] [int] NULL,
    [NUM_OF_IMAGES] [int] NULL,
    [PRIORITY] [int] NULL,
    [STATUS] [int] NULL,
    [USER_FID] [int] NULL,
    [EXTENAL_USER] [nvarchar](50) NULL,
    [REMARKS] [nvarchar](max) NULL,
    [XML_PATH] [nvarchar](max) NULL,
    [BATCH_CREATION_DATE] [datetime] NULL,
    [BATCH_PROCESS_DATE] [datetime] NULL,
    [Action] [int] NULL,
    [IMPORT_LOCATION_FID] [bigint] NULL,
    [QUARANTINE_LOCATION_FID] [bigint] NULL,
    [QUARANTINE_DATE] [datetime] NULL,
    [QUARANTINE] [bit] NULL,
    [DOCS_ON_ERROR] [varchar](255) NULL,
    [CAPTURE_XML] [nvarchar](max) NULL,
    [IGNORE_PAC] [bit] NULL,
    [APPLICATION] [int] NULL,
    [EXTRA_INFO] [nvarchar](max) NULL,
    [INPUT_TEXT] [nvarchar](max) NULL,
    [PROCESS_TIME_BATCH] [int] NULL,
    [PROCESS_TIME_DOCUMENT] [int] NULL,
    [PROCESS_TIME_IMAGE] [int] NULL,
    [BATCH_SIZE] [int] NULL,
    [RULES] [nvarchar](1000) NULL,
    [KEEP_XML] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
    [BATCH_PID] 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]


CREATE NONCLUSTERED INDEX [IDX_BATCH_Action] ON [dbo].[BATCH]
(
    [Action] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_BATCH_CREATION_DATE] ON [dbo].[BATCH]
(
    [BATCH_CREATION_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_BATCH_SIZE] ON [dbo].[BATCH]
(
    [BATCH_SIZE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_DOCUMENT_CLASS_FID] ON [dbo].[BATCH]
(
    [DOCUMENT_CLASS_FID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_IMPORT_LOCATION_FID] ON [dbo].[BATCH]
(
    [IMPORT_LOCATION_FID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_PRIORITY] ON [dbo].[BATCH]
(
    [PRIORITY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_PROCESS_TIME_BATCH] ON [dbo].[BATCH]
(
    [PROCESS_TIME_BATCH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_PROCESS_TIME_DOCUMENT] ON [dbo].[BATCH]
(
    [PROCESS_TIME_DOCUMENT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_PROCESS_TIME_IMAGE] ON [dbo].[BATCH]
(
    [PROCESS_TIME_IMAGE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_QUARANTINE] ON [dbo].[BATCH]
(
    [QUARANTINE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_QUARANTINE_LOCATION_FID] ON [dbo].[BATCH]
(
    [QUARANTINE_LOCATION_FID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_SOURCE] ON [dbo].[BATCH]
(
    [SOURCE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_STATUS] ON [dbo].[BATCH]
(
    [STATUS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_USER_FID] ON [dbo].[BATCH]
(
    [USER_FID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IDX_BATCH_XML_NAME] ON [dbo].[BATCH]
(
    [XML_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

我还创建了一个简单的循环来插入数据

declare @numInserts int = 10000
declare @buildvarchar int = 10000

declare @maxvarchar nvarchar(max) = N'X'

begin

 while @buildvarchar > 0
 begin
  set @maxvarchar = @maxvarchar + N'X'
  set @buildvarchar = @buildvarchar - 1
 end


 while @numInserts > 0
 begin
INSERT [dbo].[BATCH] ([XML_CREATION_DATE], [BATCH_REFERENCE], [SOURCE], [DOCUMENT_CLASS_FID], [XML_NAME], [XML_TYPE], [XML], [NUM_OF_DOCUMENTS], [NUM_OF_IMAGES], [PRIORITY], [STATUS], [USER_FID], [EXTENAL_USER], [REMARKS], [XML_PATH], [BATCH_CREATION_DATE], [BATCH_PROCESS_DATE], [Action], [IMPORT_LOCATION_FID], [QUARANTINE_LOCATION_FID], [QUARANTINE_DATE], [QUARANTINE], [DOCS_ON_ERROR], [CAPTURE_XML], [IGNORE_PAC], [APPLICATION], [EXTRA_INFO], [INPUT_TEXT], [PROCESS_TIME_BATCH], [PROCESS_TIME_DOCUMENT], [PROCESS_TIME_IMAGE], [BATCH_SIZE], [RULES], [KEEP_XML])
VALUES (CAST(N'2017-09-21T14:56:46.000' AS DateTime), N'', N'iDesk', 1, N'21-09-2017-14-44-58-501574', 2, 
@maxvarchar, 0, 0, 1, 9, 1, N'', N'', N'D:\BaseDir\', CAST(N'2017-09-21T14:56:46.000' AS DateTime), CAST(N'2017-09-21T14:56:46.000' AS DateTime), 3, 1, 0, CAST(N'1900-01-01T00:00:00.000' AS DateTime), 0, N'1', NULL, NULL, 4, NULL, N'', 412, 0, 0, 0, N'', 0)

        set @numInserts = @numInserts - 1
    end
end

标签： sqlsql-server

解决方案

通过以下语句，我设法将表大小减少了 27GB。

ALTER TABLE Batch REBUILD WITH (ONLINE = OFF)

看起来 SQL 没有自动重新使用空间。

我没有找到关于如何检测它是否会提前清理某些东西的解决方案。总数据长度查询差异就像 400MB 差异。

推荐阅读