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

Откуда:
Сообщений: 1031
Коллеги, нужен совет экспертов:

SQL: Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64) ; Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

Есть большая таблица в режиме 7х24, с 3 BLOB полями.
В неё идет интенсивная запись, 10 - 300 строк в секунду.
По ночам стартует процесс очистки, который страсывает блоб поля для запиcей старше Х дней в NULL.
Некоторые блоб поля для некоторых строк остаются несброшенными.
Проблема в том, что этом место, занятое блобами очищается не полностью.

В результате приходится периодически перезаливать всю таблицу, и таблица становится в 8-10 раз меньше!
Например сейчас таблица весит 2,6 ТБ, после перезаливки 0,26 ТБ:
TabRowsCntReservedMBDataMBPagesCntindex_sizeMBunusedMB
до перезаливки53376923026206352541191325272474728926551
после перезаливки5268295482613281968892520187963934504
(разницу в количестве строк пожалуйста игнорируйте)

Есть ли идеи, как тожно очищать блоб поля без необходитости "ручной" перезаливки?

PS:
Что приходит в голову:
Приделать к таблице поле "Cleaned" и партиционировать таблицу по нему.
При очиске блоб полей устанавливать Cleaned=1, в надежде, что это повлечёт перемещение записей в другую партицию - т.е. как бы неявную перезаливку.
Будет ли такой трюк работать?

Структура таблицы:
+

Таблица:
CREATE TABLE [dbo].BlobTable
(
	[Col_PK] [int] IDENTITY(1,1) NOT NULL,
	[Col_ParentValue] [int] NULL, 
	[Col03] [varchar](100) NULL,
	[Col04] [int] NULL,
	[Col05] [varchar](200) NULL,
	[Col06] [smallint] NULL,
	[Col07] [smallint] NULL,
	[Col08] [smallint] NULL,
	[Col09] [smallint] NULL,
	[Col10] [int] NULL,
	[Col11] [int] NULL,
	[Col12] [int] NULL,
	[Col13] [varchar](255) NULL,
	[Col14] [varbinary](max) NULL,
	[Col15] [varbinary](max) NULL,
	[Col16] [varbinary](max) NULL,
 CONSTRAINT [PK_BlobTable] PRIMARY KEY CLUSTERED ([Col_PK])
) 
-- есть ещё несколько индексов, которые я не привожу для экономии места. 



Измерения:
CREATE view [dbo].[v_TableSpaceUsage] 
as
-- from sp_spaceusage
select 
[Schema_Name],
Table_Name,
[rowCount] as RowsCnt,
cast((reservedpages * 8) / 1024.0 as decimal(18,1))  as ReservedMB,  
cast((pages * 8) / 1024.0 as decimal(18,1)) as DataMB,
pages as PagesCnt,
index_sizeMB =  cast((CASE WHEN usedpages     > pages     THEN (usedpages - pages)         ELSE 0 END * 8) / 1024.0 as decimal(18,1)),
unusedMB =      cast((CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END * 8) / 1024.0as decimal(18,1))

from
( 
 SELECT 
        s.name as [Schema_Name],
        object_name(p.object_id) Table_Name,
		reservedpages = SUM (reserved_page_count),
		usedpages = SUM (used_page_count),
		pages = SUM (
			CASE
				WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
				ELSE lob_used_page_count + row_overflow_used_page_count
			END
			),
		[rowCount] = SUM (
			CASE
				WHEN (index_id < 2) THEN row_count
				ELSE 0
			END
			)
	FROM sys.dm_db_partition_stats p
	join sys.objects o on p.object_id=o.object_id and o.type in ('U') 
	join sys.schemas s on o.schema_id=s.schema_id
	group  by  s.name, p.object_id
 )T

3 янв 19, 12:30    [21777848]     Ответить | Цитировать Сообщить модератору
 Re: Очистка BLOB полей в большой таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 8671
alter index PK_BlobTable on dbo.BlobTable reorganize with (lob_compaction = on);
3 янв 19, 13:59    [21777879]     Ответить | Цитировать Сообщить модератору
 Re: Очистка BLOB полей в большой таблице  [new]
Dzianis
Member

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

а как реагирует на шринк и реорганизация индексов?
3 янв 19, 14:03    [21777880]     Ответить | Цитировать Сообщить модератору
 Re: Очистка BLOB полей в большой таблице  [new]
Alexander Us
Member

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

спасибо, на днях попобую.
3 янв 19, 14:27    [21777890]     Ответить | Цитировать Сообщить модератору
 Re: Очистка BLOB полей в большой таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 8671
Alexander Us,

Может оказаться, что перелить в новую таблицу с минимальным журналированием будет гораздо эффективнее.
3 янв 19, 14:59    [21777906]     Ответить | Цитировать Сообщить модератору
 Re: Очистка BLOB полей в большой таблице  [new]
Alexander Us
Member

Откуда:
Сообщений: 1031
invm
Может оказаться, что перелить в новую таблицу с минимальным журналированием будет гораздо эффективнее.

Переливаю своим тулом на основе SqlBilkCopy, база в Simple Mode. (минимальне журналирование, или?)

Проблема в том, что такую переливку приходится выполнять вручную:
перелить из BlobTable в BlobTable_NEW, а затем "переключить" таблицы.

Недостатки метода:
- для переключения старой и новой таблиц приходится выбирать окно с минимальной загрузкой
- переключение приходиться выполнять вручную, на мой взгляд овтоматизировать рискованно
- старая и новая таблицы, обе лежат в базе и занимают место

Что касается reorganize with (lob_compaction = on), много лет назад я пробовал его на SQL 2005, тогда эффекта не было.
Но думаю, имеет смысл попробовать ещё раз, на SQL 2008.

Хотелось бы конечно иметь возможность освобождать старое BLOB пространство без выведения таблицы в оффлайн и ручных операций.

Как Вы думаете, трюк с партиционированием может дать желаемое?
3 янв 19, 15:26    [21777911]     Ответить | Цитировать Сообщить модератору
 Re: Очистка BLOB полей в большой таблице  [new]
Rankatan
Member

Откуда:
Сообщений: 250
Как насчет варианта вынести блобы в отдельную таблицу? Связь 1 к 1, дальше секционирование этой таблицы, очистку делать с помощью truncate partitions (в sql 2008 switch+trunctate)
4 янв 19, 02:47    [21778180]     Ответить | Цитировать Сообщить модератору
 Re: Очистка BLOB полей в большой таблице  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 6979
А можно вообще в FILESTREAM хранить.
4 янв 19, 02:50    [21778181]     Ответить | Цитировать Сообщить модератору
 Re: Очистка BLOB полей в большой таблице  [new]
Alexander Us
Member

Откуда:
Сообщений: 1031
Rankatan
Как насчет варианта вынести блобы в отдельную таблицу? Связь 1 к 1, дальше секционирование этой таблицы, очистку делать с помощью truncate partitions (в sql 2008 switch+trunctate)
Удалять надо не стороки, а данные в ячейках таблицы - выборочно.
Кроме того, в таблицу постоянно идут вставки, и для switch+trunctate наверное нужно будет определять окна обслуживания - т.е. выводить таблицу в оффлайн.
4 янв 19, 10:49    [21778279]     Ответить | Цитировать Сообщить модератору
 Re: Очистка BLOB полей в большой таблице  [new]
Alexander Us
Member

Откуда:
Сообщений: 1031
Relic Hunter
А можно вообще в FILESTREAM хранить.

Можно, но нужно ли:
Средняя длинна всех трёх блоб полей 6КБ (есть исключения, поэтому длиння полей установлена в max).
А рекомендуемая длинна поля для FILESTREAM от 1МБ?
4 янв 19, 10:58    [21778282]     Ответить | Цитировать Сообщить модератору
 Re: Очистка BLOB полей в большой таблице  [new]
Mr. X
Member

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

Для switch нужна монопольная блокировка на таблицу источник, но она длится доли секунды. Это и будет вашь downtime.
4 янв 19, 11:49    [21778304]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить