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

Откуда: Железнодорожный
Сообщений: 1842
Блог
Доброго дня.
Ситуация следующая. Есть 3 таблицы. SrcTable - основная OLTP таблица. tmpSrcTable временная таблица для подготовки данных для архивной таблицы. ArchTable архивная таблица с кластерным колоночным индексом. Действия следующие:
1. перекидываем порцию данных из SrcTable в tmpSrcTable.
2. создаем секционный кластерный колоночный индекс на tmpSrcTable.
3. переключаем секции в ArchTable.
4. удаляем кластерный колоночный индекс на tmpSrcTable.
В итоге наблюдаем следующую картину (sp_spaceused и ssms свойство таблицы) кол-во строк 0, data и index_size больше 0, т.е. такое поведение как при удалении из кучи, данных фактически нет (они в архиве), но страницы не помечены как освобожденные (в PFS). освобождение страниц происходит только при truncate. Вот думаю это бага или фитча?

+ скрипт распределения страниц

SELECT au.allocation_unit_id
,au.type_desc
,au.total_pages
,au.used_pages
,au.data_pages
,p.index_id
,i.name AS [indexname]
,i.type_desc AS [index_type]
,fg.name AS [filegroup]
,s.name AS [schema]
,o.name AS [Object]
,o.type_desc 
,p.data_compression_desc
FROM sys.system_internals_allocation_units au
JOIN sys.partitions [p] ON [p].[partition_id] = au.[container_id]
JOIN sys.filegroups fg ON au.filegroup_id = fg.data_space_id
JOIN sys.objects o ON o.object_id = p.object_id
JOIN sys.schemas s ON s.schema_id = o.schema_id
JOIN sys.indexes i ON i.object_id = o.object_id AND i.index_id = p.index_id
WHERE o.name = 'tmpSrcTable'
ORDER BY total_pages DESC;

EXEC sp_spaceused 'dbo.tmpSrcTable';



Подготовка данных.
CREATE PARTITION FUNCTION fn_Dt2 (DATETIME2) AS RANGE RIGHT FOR VALUES('20160811','20160812','20160813')
GO
CREATE PARTITION SCHEME sch_Dt2 AS PARTITION fn_Dt2 ALL TO ([PRIMARY]);
GO
CREATE PARTITION FUNCTION fn_tiny (TINYINT) AS RANGE RIGHT FOR VALUES(0,1,2,3,4,5,6,7,8,9)
GO
CREATE PARTITION SCHEME sch_tiny AS PARTITION fn_tiny ALL TO ([PRIMARY]);
GO

CREATE TABLE dbo.SrcTable (Id INT NOT NULL,FName VARCHAR(20) NOT NULL,InsDate DATETIME2,PartNo TINYINT NOT NULL) ON sch_tiny(PartNo)
GO
CREATE CLUSTERED INDEX ixSrcTable ON dbo.SrcTable(InsDate,PartNo) ON sch_tiny(PartNo)
GO

CREATE TABLE dbo.tmpSrcTable (Id INT NOT NULL,FName VARCHAR(20) NOT NULL,InsDate DATETIME2,PartNo TINYINT NOT null) ON sch_Dt2(InsDate) 
GO

CREATE TABLE dbo.ArchTable (Id INT NOT NULL,FName VARCHAR(20) NOT NULL,InsDate DATETIME2,PartNo TINYINT NOT NULL) ON sch_Dt2(InsDate)
GO

CREATE CLUSTERED COLUMNSTORE INDEX ixArchTable ON dbo.ArchTable
GO

INSERT INTO dbo.SrcTable ( Id, FName, InsDate, PartNo ) VALUES 
(1,'AAAAAA','20160811 13:10',1%10),
(2,'AAAAAA','20160811 15:15',2%10),
(3,'AAAAAA','20160812 09:17',3%10),
(4,'AAAAAA','20160812 13:10',4%10),
(5,'AAAAAA','20160813 13:20',5%10),
(6,'AAAAAA','20160813 15:10',6%10)
GO


Проверяем tmpSrcTable, сейчас все по 0-м.
allocation_unit_id	type_desc	total_pages	used_pages	data_pages	index_id	indexname	index_type	filegroup	schema	Object	type_desc	data_compression_desc
72057594047234048 IN_ROW_DATA 0 0 0 0 NULL HEAP PRIMARY dbo tmpSrcTable USER_TABLE NONE
72057594047299584 IN_ROW_DATA 0 0 0 0 NULL HEAP PRIMARY dbo tmpSrcTable USER_TABLE NONE
72057594047365120 IN_ROW_DATA 0 0 0 0 NULL HEAP PRIMARY dbo tmpSrcTable USER_TABLE NONE
72057594047430656 IN_ROW_DATA 0 0 0 0 NULL HEAP PRIMARY dbo tmpSrcTable USER_TABLE NONE

name rows reserved data index_size unused
tmpSrcTable 0 0 KB 0 KB 0 KB 0 KB

далее переносим данные из таблицы.
DELETE d
OUTPUT Deleted.* INTO dbo.tmpSrcTable
FROM dbo.SrcTable d
WHERE d.InsDate < '20160812'


при этом в логе видно, что в PFS страницах объекта SrcTable идет обновление, что логично, т.к. есть кластерный индекс.

Operation Context AllocUnitName
LOP_INSERT_ROWS LCX_HEAP dbo.tmpSrcTable
LOP_SET_FREE_SPACE LCX_PFS Unknown Alloc Unit
LOP_DELETE_ROWS LCX_MARK_AS_GHOST dbo.SrcTable.ixSrcTable
LOP_SET_BITS LCX_PFS dbo.SrcTable.ixSrcTable
LOP_INSERT_ROWS LCX_HEAP dbo.tmpSrcTable
LOP_DELETE_ROWS LCX_MARK_AS_GHOST dbo.SrcTable.ixSrcTable
LOP_SET_BITS LCX_PFS dbo.SrcTable.ixSrcTable

проверяем распределение страниц tmpSrcTable данные есть.
allocation_unit_id	type_desc	total_pages	used_pages	data_pages	index_id	indexname	index_type	filegroup	schema	Object	type_desc	data_compression_desc
72057594047299584 IN_ROW_DATA 2 2 1 0 NULL HEAP PRIMARY dbo tmpSrcTable USER_TABLE NONE
72057594047365120 IN_ROW_DATA 0 0 0 0 NULL HEAP PRIMARY dbo tmpSrcTable USER_TABLE NONE
72057594047430656 IN_ROW_DATA 0 0 0 0 NULL HEAP PRIMARY dbo tmpSrcTable USER_TABLE NONE
72057594047234048 IN_ROW_DATA 0 0 0 0 NULL HEAP PRIMARY dbo tmpSrcTable USER_TABLE NONE

name rows reserved data index_size unused
tmpSrcTable 2 16 KB 8 KB 8 KB 0 KB

Строим кластерный колоночный индекс
CREATE CLUSTERED COLUMNSTORE INDEX ixtmpSrcTable ON dbo.tmpSrcTable ON sch_Dt2(InsDate)
GO


проверяем распределение страниц, добавились LOB_DATA в них же данные.
allocation_unit_id	type_desc	total_pages	used_pages	data_pages	index_id	indexname	index_type	filegroup	schema	Object	type_desc	data_compression_desc
72057594048806912 LOB_DATA 10 3 0 1 ixtmpSrcTable CLUSTERED COLUMNSTORE PRIMARY dbo tmpSrcTable USER_TABLE COLUMNSTORE
72057594048610304 LOB_DATA 2 2 0 1 ixtmpSrcTable CLUSTERED COLUMNSTORE PRIMARY dbo tmpSrcTable USER_TABLE COLUMNSTORE
72057594049003520 LOB_DATA 2 2 0 1 ixtmpSrcTable CLUSTERED COLUMNSTORE PRIMARY dbo tmpSrcTable USER_TABLE COLUMNSTORE
72057594049200128 LOB_DATA 2 2 0 1 ixtmpSrcTable CLUSTERED COLUMNSTORE PRIMARY dbo tmpSrcTable USER_TABLE COLUMNSTORE
72057594048544768 IN_ROW_DATA 0 0 0 1 ixtmpSrcTable CLUSTERED COLUMNSTORE PRIMARY dbo tmpSrcTable USER_TABLE COLUMNSTORE
72057594049134592 IN_ROW_DATA 0 0 0 1 ixtmpSrcTable CLUSTERED COLUMNSTORE PRIMARY dbo tmpSrcTable USER_TABLE COLUMNSTORE
72057594048741376 IN_ROW_DATA 0 0 0 1 ixtmpSrcTable CLUSTERED COLUMNSTORE PRIMARY dbo tmpSrcTable USER_TABLE COLUMNSTORE
72057594048937984 IN_ROW_DATA 0 0 0 1 ixtmpSrcTable CLUSTERED COLUMNSTORE PRIMARY dbo tmpSrcTable USER_TABLE COLUMNSTORE

name rows reserved data index_size unused
tmpSrcTable 2 128 KB 72 KB 0 KB 56 KB

переключаем партицию и удаляем индекс
ALTER TABLE dbo.tmpSrcTable SWITCH PARTITION 2 TO dbo.ArchTable PARTITION 2
GO
drop INDEX ixtmpSrcTable ON dbo.tmpSrcTable 
GO

Проверяем tmpSrcTable, остались только IN_ROW_DATA, и страниц стало больше (16 против 36). С чего бы?
allocation_unit_id	type_desc	total_pages	used_pages	data_pages	index_id	indexname	index_type	filegroup	schema	Object	type_desc	data_compression_desc
72057594049331200 IN_ROW_DATA 9 2 1 0 NULL HEAP PRIMARY dbo tmpSrcTable USER_TABLE NONE
72057594049396736 IN_ROW_DATA 9 2 1 0 NULL HEAP PRIMARY dbo tmpSrcTable USER_TABLE NONE
72057594049462272 IN_ROW_DATA 9 2 1 0 NULL HEAP PRIMARY dbo tmpSrcTable USER_TABLE NONE
72057594049527808 IN_ROW_DATA 9 2 1 0 NULL HEAP PRIMARY dbo tmpSrcTable USER_TABLE NONE
name rows reserved data index_size unused
tmpSrcTable 0 288 KB 32 KB 32 KB 224 KB

при удалении кластерного колоночного индекса в логе только такие записи.
Operation	Context	AllocUnitName
LOP_EXPUNGE_ROWS LCX_CLUSTERED sys.sysrscols.clst
LOP_SET_BITS LCX_PFS sys.sysrscols.clst

далее, что бы освободить страницы от tmpSrcTable можно только через truncate.
12 авг 16, 11:10    [19533058]     Ответить | Цитировать Сообщить модератору
 Re: Не освобождаются страницы при переключении секции.  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
@@version ?
12 авг 16, 11:12    [19533073]     Ответить | Цитировать Сообщить модератору
 Re: Не освобождаются страницы при переключении секции.  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
AlanDenton,

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
12 авг 16, 11:13    [19533083]     Ответить | Цитировать Сообщить модератору
 Re: Не освобождаются страницы при переключении секции.  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
gds
AlanDenton,

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
12 авг 16, 11:14    [19533091]     Ответить | Цитировать Сообщить модератору
 Re: Не освобождаются страницы при переключении секции.  [new]
daw
Member

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

можно и проще:

create table dbo.t (c int);
go
create clustered columnstore index cci_t on dbo.t;
go
drop index cci_t on dbo.t;
go
drop table dbo.t;

после удаления columnstore индекса оказываются выделены 10 страниц. если таблица партицирована - под каждую партицию.
12 авг 16, 14:28    [19534469]     Ответить | Цитировать Сообщить модератору
 Re: Не освобождаются страницы при переключении секции.  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
daw,

10 страниц это при тесте. Попробуйте залить хотя бы 100 Мб. эти же 100 Мб данных и останутся.
12 авг 16, 15:44    [19534888]     Ответить | Цитировать Сообщить модератору
 Re: Не освобождаются страницы при переключении секции.  [new]
daw
Member

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

10 страниц это при тесте. Попробуйте залить хотя бы 100 Мб. эти же 100 Мб данных и останутся.


на немного более старшей версии у меня очистилось:
+

dbo.tmpSrcTable пустая

72057594067943424	IN_ROW_DATA	0	0	0	0	NULL	HEAP	PRIMARY	dbo	tmpSrcTable	USER_TABLE	NONE
72057594068205568	IN_ROW_DATA	0	0	0	0	NULL	HEAP	PRIMARY	dbo	tmpSrcTable	USER_TABLE	NONE
72057594068074496	IN_ROW_DATA	0	0	0	0	NULL	HEAP	PRIMARY	dbo	tmpSrcTable	USER_TABLE	NONE
72057594068140032	IN_ROW_DATA	0	0	0	0	NULL	HEAP	PRIMARY	dbo	tmpSrcTable	USER_TABLE	none

DELETE d OUTPUT Deleted.* INTO dbo.tmpSrcTable FROM dbo.SrcTable d
WHERE d.InsDate < '20160812'

72057594068205568	IN_ROW_DATA	46842	46832	46830	0	NULL	HEAP	PRIMARY	dbo	tmpSrcTable	USER_TABLE	NONE
72057594068074496	IN_ROW_DATA	0	0	0	0	NULL	HEAP	PRIMARY	dbo	tmpSrcTable	USER_TABLE	NONE
72057594068140032	IN_ROW_DATA	0	0	0	0	NULL	HEAP	PRIMARY	dbo	tmpSrcTable	USER_TABLE	NONE
72057594067943424	IN_ROW_DATA	0	0	0	0	NULL	HEAP	PRIMARY	dbo	tmpSrcTable	USER_TABLE	none

CREATE CLUSTERED COLUMNSTORE INDEX ixtmpSrcTable ON dbo.tmpSrcTable ON sch_Dt2(InsDate)

72057594068533248	LOB_DATA	9203	9164	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068336640	LOB_DATA	2	2	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068729856	LOB_DATA	2	2	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068926464	LOB_DATA	2	2	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068271104	IN_ROW_DATA	0	0	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068860928	IN_ROW_DATA	0	0	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068467712	IN_ROW_DATA	0	0	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068664320	IN_ROW_DATA	0	0	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE

ALTER TABLE dbo.tmpSrcTable SWITCH PARTITION 2 TO dbo.ArchTable PARTITION 2

72057594067419136	LOB_DATA	219	205	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068336640	LOB_DATA	2	2	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068729856	LOB_DATA	2	2	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068926464	LOB_DATA	2	2	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068271104	IN_ROW_DATA	0	0	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068860928	IN_ROW_DATA	0	0	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594067353600	IN_ROW_DATA	0	0	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE
72057594068664320	IN_ROW_DATA	0	0	0	1	ixtmpSrcTable	CLUSTERED COLUMNSTORE	PRIMARY	dbo	tmpSrcTable	USER_TABLE	COLUMNSTORE

drop INDEX ixtmpSrcTable ON dbo.tmpSrcTable

72057594069057536	IN_ROW_DATA	10	3	1	0	NULL	HEAP	PRIMARY	dbo	tmpSrcTable	USER_TABLE	NONE
72057594069123072	IN_ROW_DATA	10	3	1	0	NULL	HEAP	PRIMARY	dbo	tmpSrcTable	USER_TABLE	NONE
72057594069188608	IN_ROW_DATA	10	3	1	0	NULL	HEAP	PRIMARY	dbo	tmpSrcTable	USER_TABLE	NONE
72057594069254144	IN_ROW_DATA	10	3	1	0	NULL	HEAP	PRIMARY	dbo	tmpSrcTable	USER_TABLE	NONE

Microsoft SQL Server 2014 (SP1-CU6) (KB3144524) - 12.0.4449.0 (X64) 
Apr 13 2016 12:41:07 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

12 авг 16, 16:30    [19535090]     Ответить | Цитировать Сообщить модератору
 Re: Не освобождаются страницы при переключении секции.  [new]
daw
Member

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

*более старшей

более старой
12 авг 16, 16:32    [19535093]     Ответить | Цитировать Сообщить модератору
 Re: Не освобождаются страницы при переключении секции.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35396
Блог
gds,

https://msdn.microsoft.com/ru-ru/library/gg492088.aspx
Строка columnstore: SQL Server помечает строку как логически удаленную, но не возвращает физическое хранилище для строки до тех пор, пока индекс не будет перестроен.


думаю, просто забыли актуализировать выделенное под таблицу место
12 авг 16, 19:09    [19535720]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить