Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
Размер данных, выдаывемый отчётом SSMS (использование диска наиболее большими таблицами) отличается от контрольного подсчёта.

Вот вариант запроса, который использует SSMS:
SELECT
object_name(object_id) name,
SUM 
(
	CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
							ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
	END
)*8 AS dataKB
			
FROM sys.dm_db_partition_stats ps	
where object_name(object_id)='Currency'
GROUP BY ps.object_id
--> 8 KB


Вот контрольный подсчёт:
select object_name(p.object_id) Name, sum(a.data_pages)*8 DataKB from 
		sys.partitions p
join sys.allocation_units a on p.partition_id = a.container_id
where object_name(object_id)='Currency'
group by p.object_id
--> 16 KB

Что же правильно?
8 июн 17, 13:20    [20549935]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
ЗЫ: пример сделан на базе AdventureWorks
8 июн 17, 13:29    [20549962]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
aleks2
Guest
1. А с чего бы вдруг, неведомый "контрольный подсчет" считает точнее?
2. Справка где?
3. Разница "в одну страницу" - это погрешность округления.
8 июн 17, 14:00    [20550078]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7746
Alexander Us,

потому что разное отображают. Справку смотрите по sys.dm_db_partition_stats.
8 июн 17, 14:39    [20550260]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
aleks2,

Собственно откуда проблема:

Есть табличка с identity полем ~2 млрд. строк.
На ней 2 индекса: ПК по identity полю + индекс другому столбцу.

Я решил, что identity более не нужно, сделал такую же таблицу без identity.
Приделал 1 (кластерный) индекс по двум другим столбцам.
Перелил данные. fill factor в обоих по умолчанию.

Затем сравнил размеры таблиц в ssms.
Оказалось что место, занимаемое данными в новой таблице больше, чем в старой (177GB / 147 GB)
Ожидалось обратное.

При подсчете данных SSMS считает страницы индексов с ps.index_id < 2 (куча,группированный) // строка case else ...
Так как на первой таблице 2 индекса, страницы второго не учитываются.

На второй таблице только один индекс.

В результате размер данных новой таблицы отображается большим числом чем старой.
Но это не корректно!
Данные же те же минус поле identity.
8 июн 17, 14:41    [20550274]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Alexander Us
Приделал 1 (кластерный) индекс по двум другим столбцам.
Уникальный кластерный? Или неуникальный?
8 июн 17, 19:21    [20551318]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
aleks2
Guest
Alexander Us
aleks2,

Собственно откуда проблема:

Есть табличка с identity полем ~2 млрд. строк.
На ней 2 индекса: ПК по identity полю + индекс другому столбцу.

Я решил, что identity более не нужно, сделал такую же таблицу без identity.
Приделал 1 (кластерный) индекс по двум другим столбцам.
Перелил данные. fill factor в обоих по умолчанию.

Затем сравнил размеры таблиц в ssms.
Оказалось что место, занимаемое данными в новой таблице больше, чем в старой (177GB / 147 GB)
Ожидалось обратное.

При подсчете данных SSMS считает страницы индексов с ps.index_id < 2 (куча,группированный) // строка case else ...
Так как на первой таблице 2 индекса, страницы второго не учитываются.

На второй таблице только один индекс.

В результате размер данных новой таблицы отображается большим числом чем старой.
Но это не корректно!
Данные же те же минус поле identity.


Баги не в сортирах - баги в головах.

1. Если в таблице НЕТ PK или уникального кластерного индекса - MS SQL добавляет поле уникального идентификатора строки.
2. Не исключено, то размер такого идентификатора bigint.
8 июн 17, 19:24    [20551321]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36965
aleks2
2. Не исключено, то размер такого идентификатора bigint.
Четырехбайтовый он. Больше 4 лярдов одинаковых ключей в некластерный не помещается.
8 июн 17, 19:31    [20551327]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
msLex
Member

Откуда:
Сообщений: 8066
Гавриленко Сергей Алексеевич
aleks2
2. Не исключено, то размер такого идентификатора bigint.
Четырехбайтовый он. Больше 4 лярдов одинаковых ключей в некластерный не помещается.

Во-первых, он 8-ми байтовый
Во-вторых, он добавляется только там где нужно.
8 июн 17, 19:34    [20551329]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36965
msLex
Гавриленко Сергей Алексеевич
пропущено...
Четырехбайтовый он. Больше 4 лярдов одинаковых ключей в некластерный не помещается.

Во-первых, он 8-ми байтовый
Во-вторых, он добавляется только там где нужно.


http://aboutsqlserver.com/2011/11/24/a-few-more-words-about-uniquifiers-and-uniqueness-of-the-clustered-index/

Там все как-то сложнее. Автор утверждает, что " have to admit, that general comment about 4 bytes uniquifier overhead was not exactly correct. In some cases overhead could be 0 bytes but in most cases it would be 2, 6 or 8 bytes."
8 июн 17, 19:37    [20551335]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
msLex
Member

Откуда:
Сообщений: 8066
Гавриленко Сергей Алексеевич
msLex
пропущено...

Во-первых, он 8-ми байтовый
Во-вторых, он добавляется только там где нужно.


http://aboutsqlserver.com/2011/11/24/a-few-more-words-about-uniquifiers-and-uniqueness-of-the-clustered-index/

Там все как-то сложнее. Автор утверждает, что " have to admit, that general comment about 4 bytes uniquifier overhead was not exactly correct. In some cases overhead could be 0 bytes but in most cases it would be 2, 6 or 8 bytes."


Вот что написано в итоге
автор
... for unique values of the clustered key:

There is no overhead if row don’t have variable-width columns or all variable-width columns are null
There are 2 bytes overhead (variable-offset array) if there is at least 1 variable-width column that stores not null value

For non-unique values of the clustered key:

There are 8 extra bytes if row does not have variable-width columns
There are 6 extra bytes if row has variable-width columns
8 июн 17, 19:57    [20551361]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
alexeyvg
Alexander Us
Приделал 1 (кластерный) индекс по двум другим столбцам.
Уникальный кластерный? Или неуникальный?

приделал кластерный, неуникальный, 2 поля int not null

Что я не могу понять: почему отчёт SSMS суммирует количество страниц ps.in_row_data_page_count только для индексов с ID<2 ?

SUM 
(
	CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
				    ELSE (                            ps.lob_used_page_count + ps.row_overflow_used_page_count)
	END
)*8 AS dataKB
9 июн 17, 10:12    [20552115]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
msLex,

а как проверить:

-есть на этой таблице идентификатора строки или нет?
-какого он размера?
9 июн 17, 10:21    [20552144]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
aleks2
Guest
Alexander Us
msLex,

а как проверить:

-есть на этой таблице идентификатора строки или нет?
-какого он размера?

1. Посмотреть на кластерный индекс. Если его нет или он неуникальный - идентификатор есть.
2. Нафига тебе его размер?
9 июн 17, 10:57    [20552267]     Ответить | Цитировать Сообщить модератору
 Re: Место занимаемое данными в таблице: баг в отчёте SSMS?  [new]
msLex
Member

Откуда:
Сообщений: 8066
Alexander Us
Что я не могу понять: почему отчёт SSMS суммирует количество страниц ps.in_row_data_page_count только для индексов с ID<2 ?


Потому что для кучи и кластерного индекса фиксированные index_id, 0 и 1 соответственно.
Alexander Us
msLex,

а как проверить:

-есть на этой таблице идентификатора строки или нет?
-какого он размера?

идентификатора строки - это "уникализатор" или что?
9 июн 17, 12:25    [20552741]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить