个人中心
gpt4 book ai didi

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

转载 作者：行者123 更新时间：2023-12-04 21:33:32 24 4
gpt4 key购买 nike

我试图确定每个列在大表中的总存储量。里面有多个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) 列几乎都是空的。

我对该表中的所有列进行了求和(数据长度(列名))。并对所有列值求和。

这给了我:499 344 838 字节 = 0.499344838 千兆字节

所以我现在想知道:

  • 是不是不能用sum(datalength(..))来计算内容的大小

  • 该计算是否正确，报告中使用的其他 +- 14GB 在哪里？

编辑:我已经完成了一些插入数据的测试(参见下面的循环)

  • 插入 10K --> 202.768KB 数据
  • 更新 BATCH set XML = '' where xml <> '' --> 2.768KB 数据
  • 插入 40K --> 813.800KB 数据
  • 更新 BATCH set XML = '' where xml <> '' --> 13.800KB 数据
  • 插入 50K --> 1.027.592 数据
  • 更新 BATCH set XML = '' where xml <> '' --> 27 592KB 数据

  • ALTER TABLE batch REBUILD WITH (ONLINE = OFF) --> 22 928KB 数据

  • 删除并创建表

  • 用 XML 列 '' 代替 nvarchar 变量插入 100K --> 22.864KB 数据

通过此测试，数据差异不大，+- 23 vs 27 MB。

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

Edit2:我用循环进行了 1000 次测试:insert 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

最佳答案

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

ALTER TABLE batch REBUILD WITH (ONLINE = OFF)

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

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

关于sql - 计算表中每列的总存储量，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/53657877/

24 4 0
文章推荐： continuous-integration - 是否可以使用 Travis CI 测试硬件相关代码？
文章推荐： apache-kafka - 增加 KafkaConsumer 单次轮询中读取的记录数
文章推荐： angularjs - 将日期时间从 angular $http.get 请求传递给 Web API 2 Controller
文章推荐： IntelliJ 中的 scala spark notebook
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com