表の列ごとの合計ストレージを計算します

列ごとに大きなテーブルに格納されているストレージの合計量を決定しようとしていました。その中には複数のnvarcharがあります。

1つの列はnvarchar（max）であり、インポート時にXMLのテキストがその列に配置されます。レコードが正しく処理された後。列は再び空になり、空の文字列になります。

SQL Serverの「トップテーブル別のディスク使用量」レポートに、次のように表示されます。

金額記録：1 808 604

予約済み（KB）：15 209 272

データ（KB）：14 466 776

インデックス（KB）：731 896

未使用（KB）：10600

そこで、この膨大な量のデータがテーブルのどこにあるかを見つける方法を探していました。nvarchar（max）列はほとんどすべて空であるためです。

そのテーブルのすべての列に対してsum（datalength（columnname））を実行しました。そして、すべての列の値の合計を作成しました。

これは私に与えました：499 344838バイト= 0.499344838ギガバイト

だから私は今疑問に思っています：

• sum（datalength（..））でコンテンツのサイズを計算することはできませんか？

• その計算は正しいですか、そしてレポートで使用されている他の+/- 14GBはどこにありますか？

編集：データを挿入していくつかのテストを行いました（ループについては以下を参照）

• 10K-> 202.768KBのデータを挿入
• update BATCH set XML = '' where xml <> ''-> 2.768KBデータ
• 40Kを挿入-> 813.800KBデータ
• update BATCH set XML = '' where xml <> ''-> 13.800KBデータ
• 50K-> 1.027.592データを挿入
• update BATCH set XML = '' where xml <> ''-> 27592KBデータ

• ALTER TABLEバッチREBUILDWITH（ONLINE = OFF）-> 22928KBデータ

• DROP＆CREATEテーブル

• nvarchar変数の代わりにXML列 ''を使用して100Kを挿入します-> 22.864KBデータ

このテストでは、データの違いはそれほど大きくなく、+-23MBと27MBです。

本番テーブルで再構築を試みますが、スケジュールする必要があります。

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