Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Увеличивающийся размер таблицы  [new]
NewBie123
Guest
День добрый,

помогите понять следующее: имеется таблица с 4мя полями

CREATE TABLE [dbo].[log]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[datum] [datetime] NOT NULL CONSTRAINT [DF__log__datum__3D5E1FD2] DEFAULT (getdate()),
[aktion] [nvarchar] (500) COLLATE Czech_CI_AS NULL,
[text] [nvarchar] (max) COLLATE Czech_CI_AS NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[log] ADD CONSTRAINT [PK__log__3C69FB99] PRIMARY KEY CLUSTERED ([id]) ON [PRIMARY]

в ней 13911318 строчек, и весит она 6.6 ГБ, индексного места 24МБ, неиспользованного 1.3 МБ

После того, как была проведена инструкция
ALTER TABLE dbo.log ALTER COLUMN aktion NVARCHAR(150) COLLATE Czech_CI_AS NOT NULL

таблица стала весить 8ГБ, индексное место осталось прежним 24МБ, неиспользованное 1.3МБ, и в ней все еще 13911318 строчек

Вопрос, почему при уменьшении размера колонки размер таблицы увеличился? о_О

@@Version - Microsoft SQL Server 2012 - 11.0.5522.0 (X64)
Jun 17 2014 17:01:31
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
10 окт 14, 16:48    [16688317]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
NewBie123
Guest
Поле Aaktion в таблице NOT NULL
10 окт 14, 16:49    [16688333]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
NewBie123,

начнем с того, что это не "размер колонки". это максимально допустимое количество символов в колонке.
а увеличился он, потому что сервер просто добавил новый вариант столбца aktion к таблице, а данные старого трогать не стал.
чтобы "почистить" их можно таблице rebuild сделать.
10 окт 14, 16:54    [16688366]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
NewBie123
Guest
daw,

ALTER TABLE log REBUILD?
я думал, что оно только для партиций или сжатия используется
10 окт 14, 16:56    [16688376]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
NewBie123
Guest
daw,

после ребилда размер уменьшился до первоначального
10 окт 14, 16:59    [16688407]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
VGalamakh
Member

Откуда: Киев (Альба)
Сообщений: 66
The storage size, in bytes, is two times the number of characters entered + 2 bytes
10 окт 14, 18:03    [16688738]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
NewBie123

Вопрос, почему при уменьшении размера колонки размер таблицы увеличился? о_О


Скорее всего она создала новую таблицу и перелила в неё данные из старой. При этом полностью перестроился кластерный индекс. У вас FillFactor на таблице был 99% , а стал около 75%.
10 окт 14, 18:51    [16688972]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
o-o
Guest
VGalamakh
The storage size, in bytes, is two times the number of characters entered + 2 bytes

это нам что, про nvarchar рассказывают?
VGalamakh, вам заняться нечем?
человек пытался "уменьшить" размер таблицы, а он вырос,
и то же самое будет и в случае varchar-а, при чем тут хранение юникода?
прежде чем менять максимальный размер колонки переменной длины, стоило подумать,
чему бы это вообще могло помочь. то ж не фиксированная длина!
потому после ребилда все и вернулось на круги своя. какой длины строки были, такой и остались.
никто никакие 500 под них не резервировал.
10 окт 14, 18:55    [16688987]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3752
Сжатие на таблицу кстати включи, тексты хорошо жмутся
10 окт 14, 18:57    [16688996]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
o-o
Guest
a_voronin
NewBie123
Вопрос, почему при уменьшении размера колонки размер таблицы увеличился? о_О


Скорее всего она создала новую таблицу и перелила в неё данные из старой. При этом полностью перестроился кластерный индекс. У вас FillFactor на таблице был 99% , а стал около 75%.


достал уже, нечитатель хренов, всякому терпению есть предел,
не опошляйте уже правильные объяснения знающих людей:

daw
NewBie123,
начнем с того, что это не "размер колонки". это максимально допустимое количество символов в колонке.
а увеличился он, потому что сервер просто добавил новый вариант столбца aktion к таблице, а данные старого трогать не стал.
чтобы "почистить" их можно таблице rebuild сделать.
10 окт 14, 18:58    [16688998]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
o-o
a_voronin
пропущено...


Скорее всего она создала новую таблицу и перелила в неё данные из старой. При этом полностью перестроился кластерный индекс. У вас FillFactor на таблице был 99% , а стал около 75%.


достал уже, нечитатель хренов, всякому терпению есть предел,
не опошляйте уже правильные объяснения знающих людей:

daw
NewBie123,
начнем с того, что это не "размер колонки". это максимально допустимое количество символов в колонке.
а увеличился он, потому что сервер просто добавил новый вариант столбца aktion к таблице, а данные старого трогать не стал.
чтобы "почистить" их можно таблице rebuild сделать.


Уважаемый господин, чей ник совпадает с обозначением места, где справляют нужду.

Специально для вас объясняю, что одно другому не противоречит.

1) сервер просто добавил новый вариант столбца aktion к таблице, а данные старого трогать не стал.[/b]
чтобы "почистить" их можно таблице rebuild сделать.
2) При этом полностью перестроился кластерный индекс. У вас FillFactor на таблице был 99% , а стал около 75%.
10 окт 14, 19:11    [16689053]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104751
a_voronin
Специально для вас объясняю, что одно другому не противоречит.

Вот только ALTER TABLE dbo.log ALTER COLUMN aktion NVARCHAR(150) COLLATE Czech_CI_AS NOT NULL никак не может "создать новую таблицу и перелить туда данные"
10 окт 14, 20:35    [16689364]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
Glory
a_voronin
Специально для вас объясняю, что одно другому не противоречит.

Вот только ALTER TABLE dbo.log ALTER COLUMN aktion NVARCHAR(150) COLLATE Czech_CI_AS NOT NULL никак не может "создать новую таблицу и перелить туда данные"


Это я имел ввиду "возможно". Что на уровне движка БД происходит тут ещё вопрос. Но то, что изменился FillFactor вы надеюсь отрицать не будете?
10 окт 14, 21:11    [16689496]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104751
a_voronin
Но то, что изменился FillFactor вы надеюсь отрицать не будете?

А силой мысли узнали, что он изменился ?
Или просто наубом цифры назвали ?
10 окт 14, 21:12    [16689500]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104751
a_voronin
Что на уровне движка БД происходит тут ещё вопрос.

Для вас - да.
Другим ответ ужк известен.
10 окт 14, 21:13    [16689509]     Ответить | Цитировать Сообщить модератору
 Re: Увеличивающийся размер таблицы  [new]
o-o
Guest
a_voronin, у вас действительно безудержный полет фантазии, обзавидоваться можно
a_voronin
ник совпадает с обозначением места, где справляют нужду

вы, простите, с какой стороны на него смотрели, что к такому выводу пришли?
до меня дошло только вчера, и то по случаю
вы намекаете на то, что "о-о" = СООБЩАЮЩИЕСЯ УНИТАЗЫ, вид сверху?

a_voronin
Специально для вас объясняю, что одно другому не противоречит.

1) сервер просто добавил новый вариант столбца aktion к таблице, а данные старого трогать не стал.[/b]
чтобы "почистить" их можно таблице rebuild сделать.
2) При этом полностью перестроился кластерный индекс. У вас FillFactor на таблице был 99% , а стал около 75%.


специально для тех кто в танке: хватит примазываться к верным ответам, сводя их "на нет" ложными высказываниями.

даже убрав гениальную фразу "она создала новую таблицу и перелила в неё данные",
вы продолжаете вводить в заблуждение своим "При этом полностью перестроился кластерный индекс"
вы в курсе, что такое перестроение индекса?
Index rebuild (whether online or offline, and at least as far back as 7.0) will create a new copy of the index before dropping the old copy. см. первый же миф вот отсюда:
Search Engine Q&A #19: Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking)

в конце поста тест, показывающий, что исходные страницы никуда не деваются,
если влезет, страница "распухнет", если не влезет, она расщепится -- split page,
т.е. будет выделена новая страница, куда часть данных будет перенесена.
но исходная страница никуда не денется, фига ли page split называть index rebuild???

дальше идем:
"У вас FillFactor на таблице был 99% , а стал около 75%"
даже если бы было перестроение, вы не в курсе, что без указания FF в явном виде,
перестроение приведет к восстановлению оного?
т.е. если изначальный FF равен 100, то и индекс перестроится с FF=100
(что у ТС и произошло после применения совета daw)

When an option is not explicitly specified, the current setting is applied. For example, if a FILLFACTOR setting is not specified in the REBUILD clause, the fill factor value stored in the system catalog will be used during the rebuild process.
ALTER INDEX (Transact-SQL)

еще раз:
сервер просто добавил новый вариант столбца aktion к таблице, а данные старого трогать не стал

вот мини-демонстрация происходящего:
CREATE TABLE [dbo].[log]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[datum] [datetime] NOT NULL CONSTRAINT [DF__log__datum__3D5E1FD2] DEFAULT (getdate()),
[aktion] [nvarchar] (500) COLLATE Czech_CI_AS NULL,
[text] [nvarchar] (max) COLLATE Czech_CI_AS NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[log] ADD CONSTRAINT [PK__log__3C69FB99] PRIMARY KEY CLUSTERED ([id]) ON [PRIMARY]
go

insert into dbo.log(aktion, text)
values (replicate(N'a_voronin', 10), replicate(N'text_', 200));
go 3

DBCC IND (base1, 'dbo.log', 1);
---
PageFID	PagePID	IAMFID	IAMPID	ObjectID	IndexID	PartitionNumber	PartitionID	iam_chain_type	PageType
1	1793	NULL	NULL	453576654	1	1	72057594040025088	In-row data	10
1	1792	1	1793	453576654	1	1	72057594040025088	In-row data	1

/* всего одна страница данных и заполнена так, чтобы при изменении типа столбца все равно все влезло */

DBCC TRACEON(3604);
go

DBCC PAGE(base1, 1, 1792, 1);
go

/* показываю, что лежит на странице до смены типа (укороченно) */

Slot 0, Offset 0x60, Length 2205, DumpStyle BYTE
Slot 1, Offset 0x8fd, Length 2205, DumpStyle BYTE
Slot 2, Offset 0x119a, Length 2205, DumpStyle BYTE

 Row - Offset                         
2 (0x2) - 4506 (0x119a)              
1 (0x1) - 2301 (0x8fd)               
0 (0x0) - 96 (0x60) 

/* меняем NVARCHAR(500) -> NVARCHAR(150) */
ALTER TABLE dbo.log ALTER COLUMN aktion NVARCHAR(150) COLLATE Czech_CI_AS NOT NULL

/* что со страницами? все то же самое, ибо ВЛЕЗЛО */
DBCC IND (base1, 'dbo.log', 1);
---
PageFID	PagePID	IAMFID	IAMPID	ObjectID	IndexID	PartitionNumber	PartitionID	iam_chain_type	PageType
1	1793	NULL	NULL	453576654	1	1	72057594040025088	In-row data	10
1	1792	1	1793	453576654	1	1	72057594040025088	In-row data	1

/* а что там на странице 1792 поменялось? */
DBCC PAGE(base1, 1, 1792, 1);
go

Slot 0, Offset 0x60, Length 2387, DumpStyle BYTE
Slot 1, Offset 0x9b3, Length 2387, DumpStyle BYTE
Slot 2, Offset 0x1306, Length 2387, DumpStyle BYTE

Row - Offset                         
2 (0x2) - 4870 (0x1306)              
1 (0x1) - 2483 (0x9b3)               
0 (0x0) - 96 (0x60)       

-- TEST 2           

/* дропаю таблицу, пересоздаю ровно как выше было, но заполню теперь так,
чтобы переполнить ее при изменении типа столбца */

insert into dbo.log(aktion, text)
values (replicate(N'a_voronin', 10), replicate(N't', 1240));
go 3

DBCC IND (base1, 'dbo.log', 1);
---
PageFID	PagePID	IAMFID	IAMPID	ObjectID	IndexID	PartitionNumber	PartitionID	iam_chain_type	PageType
1	1793	NULL	NULL	453576654	1	1	72057594040025088	In-row data	10
1	1792	1	1793	453576654	1	1	72057594040025088	In-row data	1


DBCC PAGE(base1, 1, 1792, 1);
go

Row - Offset                         
2 (0x2) - 5466 (0x155a)              
1 (0x1) - 2781 (0xadd)               
0 (0x0) - 96 (0x60) 
  
Slot 0, Offset 0x60, Length 2685, DumpStyle BYTE
Slot 1, Offset 0xadd, Length 2685, DumpStyle BYTE
Slot 2, Offset 0x155a, Length 2685, DumpStyle BYTE

ALTER TABLE dbo.log ALTER COLUMN aktion NVARCHAR(150) COLLATE Czech_CI_AS NOT NULL

/* сюрприз! data pages уже 2: 1792, 1796
1 - data page
2 - index page
10 - IAM page
 */

PageFID	PagePID	IAMFID	IAMPID	ObjectID	IndexID	PartitionNumber	PartitionID	iam_chain_type	PageType	IndexLevel	NextPageFID	NextPagePID	PrevPageFID	PrevPagePID
1	1793	NULL	NULL	885578193	1	1	72057594041204736	In-row data	10	NULL	0	0	0	0
1	1792	1	1793	885578193	1	1	72057594041204736	In-row data	1	0	1	1796	0	0
1	1794	1	1793	885578193	1	1	72057594041204736	In-row data	2	1	0	0	0	0
1	1796	1	1793	885578193	1	1	72057594041204736	In-row data	1	0	0	0	1	1792

DBCC PAGE(base1, 1, 1792, 1);

m_nextPage = (1:1796) -- это в page header, говорит, следующая страница 1796

Slot 0, Offset 0x60, Length 2867, DumpStyle BYTE

Row - Offset                         
0 (0x0) - 96 (0x60) 
                 

DBCC PAGE(base1, 1, 1796, 1);


Slot 0, Offset 0x60, Length 2867, DumpStyle BYTE
Slot 1, Offset 0xb93, Length 2867, DumpStyle BYTE

Row - Offset                         
1 (0x1) - 2963 (0xb93)               
0 (0x0) - 96 (0x60)          

/* вот они, все 3 записи, разнеслись по двум страницам, на первой одна осталась, на вторую 2 сложили */        
15 окт 14, 18:53    [16709689]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить