Узнать размер колонки

#sql #sql_server #sql_server_2008

Для того, чтобы узнать размер всей бд, или, например, одной таблицы, у нас есть команда
'sp_spaceused'.
Но если пойти дальше, то возникает вопрос: а как получить статистику по полям? Как
узнать, в каком поле (колонке) в таблице содержатся самые тяжёлые данные, а в каком
самые легкие?

Вопрос не столько теоретический, сколько практический: есть огромная база, которую
надо проанализировать (постепенно погружаюсь в Data-Mining).
    

Ответы

Ответ 1

Используйте функцию DATALENGTH() 

В следующем примере находится длина столбца ProductName в таблице MyOrderTable.

SELECT DATALENGTH(ProductName) as [SizeInBytes] FROM MyOrderTable


Для справки: DATALENGTH


Ответ 2

Как узнать, в каком поле (колонке) в таблице содержатся самые тяжёлые
  данные, а в каком самые легкие?


Вас интересует размер в байтах, занимаемый тем или иным столбцом на диске (видимо
так, раз вы упомянули sp_spaceused)?

Не уверен, что возможно определить его точно. Точно можно узнать, сколько страниц
(блоков по 8Кб, которыми SqlServer хранит данные) занимают данные всей таблицы (или
индекса).

Месту на диске, отведенному под хранение конкретного столбца, по-видимому можно дать
лишь некоторую оценку (которая, впрочем, не всегда будет адекватной). И с datalength
не совсем всё просто. Далее несколько подробнее.



Возьмём запрос

/*1*/ select sum(datalength([Column])) from [Table];


за основу оценки.

Во-первых. Кроме собственно данных столбца всегда есть дополнительная служебная информация,
которая может иметь отношение к столбцу, но её объем между столбцами логически может
делиться непропорционально их количеству в таблице (например данные заголовка строки).
Т.е. оценку размера /*1*/ следует воспринимать как "не менее чем".

Чем меньше в таблице столбцов, и чем короче запись, тем больше издержки на служебные
данные, тем, соответственно, дальше оценка /*1*/ от реальности. Так, для таблицы с
одним коротким столбцом полный размер данных (с учётом служебной информации) может
значительно превосходить "логический" размер данных самого столбца. Сравните, например,
для таблицы

create table tiny_nums (value tinyint);

insert into tiny_nums (value)
select top (1048576) 1
from master.sys.all_columns a cross join master.sys.all_columns b;


результат, возвращаемый запросом /*1*/ с тем, что покажет sp_spaceused.

Во-вторых. Значение, возвращаемое datalength не всегда соответствует действительности.
В частности, если datalength([Column]) возвращает NULL, то физически это может быть
вовсе не ноль.

Дело в том, что типы столбцов делятся на fixed-length (напр. int, char(20), datetime2(0),
uniqueidentifier, и т.п.) и variable-length (напр. varbinary(64), nvarchar(30) и т.п.).
И если для variable-length оценка /*1*/ приблизительно справедлива, то для fixed-length
столбцов резервируется место для хранения значения, даже если само значение NULL.

Т.е. для fixed-length столбцов оценку /*1*/ следует скорректировать, используя вместо
NULL (если они возможны) какое-либо непустое значение, соответствующее типу столбца
(например 0 для int):

/*2*/ select sum(datalength(isnull([IntColumn], 0))) from [Table];


Также нужно учитывать, что для столбцов типа bit возвращаемое datalength значение
равно 1. Однако если в таблице (или индексе) несколько bit столбцов, то SqlServer объединяет
их по 8 в 1 байт.

Также столбцы могут быть sparse, что означает 0 байт на хранение NULL (даже для fixed-length),
но плюс 4 дополнительных байта на хранение значения, если оно не NULL:

/*3*/ select sum(datalength([SparseColumn]) + 4) from [TableName];


В-третьих. Если столбец не просто присутствует в таблице, а ещё и участвует в индексах,
то он "утяжеляется" кратно количеству индексов, в которых он участвует. Если столбец
является ключевым в кластерном индексе, то нужно прибавить оценку кратную количеству
всех некластерных индексов (т.к. в leaf-level страницах некластерных индексов содержатся
значения ключей кластерного  индекса). Так в таблице

create table SomeTable
(
    PK_ID bigint primary key,
    FK_1_ID int,
    FK_2_ID int,
    ...
    FK_10_ID int,
    UID uniqueidentifier
);

create index IX_1 on SomeTable (FK_1_ID);
...
create index IX_10 on SomeTable (FK_10_ID);


самым "тяжёлым" скорее всего окажется вовсе не UID столбец, а PK_ID, т.к. (помимо
участия в кластерном первичном ключе) значения PK_ID будут присутствовать ещё в 10-ти
некластерных индексах.

Следует учесть также, что если некластерный индекс является фильтрованным индексом,
то соответствующую оценку (/*1*/, /*2*/ или /*3*/) нужно взять не по всей таблице,
а по строкам, соответствующим фильтру такого индекса.

В-четвертых (относится к Enterprise edition). Если применяется сжатие строк или страниц
таблицы

alter table [TableName]
    rebuild partition = all with (data_compression = row);


либо индекса

alter index [IX_Name] on [TableName]
    rebuild with (data_compression = page);


то оценки с помощью datalength перестают быть адекватными и фактор "не менее чем"
перестаёт работать.

Сравните для таблиц

create table strings (value char(2000));

insert into strings (value)
select top (10000) replicate('a', 2000)
from master.sys.all_columns a cross join master.sys.all_columns b;


и

create table strings_page (value char(2000)) with (data_compression = page);

insert into strings_page with (tablock) (value)
select top (10000) replicate('a', 2000)
from master.sys.all_columns a cross join master.sys.all_columns b;


значения оценки размера столбца с помощью datalength c тем, что покажет sp_spaceused.
Для первой таблицы "показания" datalength и sp_spaceused будут близки (т.к. строка
таблицы "широкая" и объем служебной информации сказывается мало), а для второй будут
расходиться очень сильно.

В-пятых. Всё что было сказано до этого момента справедливо для SqlServer 2008. В
более поздних версиях появились COLUMNSTORE индексы, которые, из-за особенностей своего
устройства, могут хранить данные в существенно сжатом виде. Для них оценка размера
столбца с помощью datalength также может давать неадекватный результат. Если для таблицы

create table strings_col (value char(2000));

insert into strings_col (value)
select top (10000) replicate('a', 2000)
from master.sys.all_columns a cross join master.sys.all_columns b;

create clustered columnstore index ix_clcs on strings_col;


сравнить показания sp_spaceused с datalength, то опять можно наблюдать сильное расхождение.

Полагаю, что данный список факторов, которые следует учитывать при оценке места,
занимаемого тем или иным столбцом, не исчерпывающий.