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

Имеется таблица (структуру смотрите ниже). В ней присутствуют LOB-поля.
В таблице постоянно происходит добавление и удаление данных (в 90% случаев хронологически по полю srcExpirationDate).
Проблема сейчас в следующем: при удалении данных из этой таблицы - реально место не освобождается и затем это место sql server не может повторно использовать. При этом происходит разрастание размеров таблицы более чем в 10 раз от реально требуемого размера для хранения данных. Т.е. на начало эксплуатации у нас есть 30000 строк. Занимают они, к примеру 5 GB. Через неделю эскплуатации(постоянное добавление/удаление) - уже 50 GB. Перезаливаем эти же данные заново в чистую таблицу - опять 5 GB.

Версия sql server: Microsoft SQL Server Web Edition (64-bit) 10.0.2531.0

GO
CREATE TABLE [dbo].[T_Test](
[KeyHash] [nvarchar](50) NOT NULL,
[SiteDomainId] [int] NOT NULL,
[srcFullUrl] [nvarchar](max) NOT NULL,
[srcResponse] [ntext] NOT NULL,
[srcExpirationDate] [datetime] NOT NULL,
[srcKey] [nvarchar](max) NOT NULL,
[srcCachePeriodInMinutes] [int] NOT NULL,
[srcNumOfHits] [int] NOT NULL,
[srcVital] [bit] NOT NULL,
CONSTRAINT [PK_T_Test_1] PRIMARY KEY NONCLUSTERED
(
[KeyHash] ASC,
[SiteDomainId] ASC
))
GO
CREATE CLUSTERED INDEX [IX_T_Test_srcExpirationDate_ppa] ON [dbo].[T_Test]
(
[srcExpirationDate] ASC
)
GO

Что пробовали.
1. Пробовали просто выполнять shrink - место не освобождается
2. Пробовали реорганизовывать индексы with LOB compation - место не освобождается.
3. Перестраивали кластерный индекс - место не освобожадется
4. Пробовали DBCC CLEANTABLE - место не освобождается.
5. Пробовали параметр для таблицы sp_tableoption «large value types out of row» - место не освобождается

Вообщем пока не сделаешь truncate table - таблица будет прирастать и прирастать в объемах.

Похожая проблема обсуждались здесь
1. http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/f80ad8c7-e6c9-4538-a89b-8d6727050b9c/
2. http://www.sqlservercentral.com/Forums/Topic993883-146-6.aspx#bm1008735

Заранее благодарен за любую комментарии по проблеме, а также за различные предлагаемые способы как ее решить.
7 дек 10, 11:33    [9897374]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Glory
Member

Откуда:
Сообщений: 104751
И что показывают DBCC SHOWCONTIG и sp_spaceused ?
7 дек 10, 11:39    [9897415]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Павел-П
Guest
Glory,

Вот результаты, для данных, которые при заливке в чистую таблицу займут меньше 10GB.

1. exec sp_spaceused 'dbo.T_Test'
namerowsreserveddataindex sizeunused
Test16202769831296 KB69796520 KB17000 KB17776 KB



2.
DBCC SHOWCONTIG scanning 'T_Test' table...
Table: 'T_Test' (405576483); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 22930
- Extents Scanned..............................: 3012
- Extent Switches..............................: 5854
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 48.97% [2867:5855]
- Logical Scan Fragmentation ..................: 20.03%
- Extent Scan Fragmentation ...................: 98.61%
- Avg. Bytes Free per Page.....................: 920.3
- Avg. Page Density (full).....................: 88.63%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Сообщение было отредактировано: 7 дек 10, 12:00
7 дек 10, 11:49    [9897506]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Павел-П
Guest
Ну вот примерно это показывает.
7 дек 10, 13:51    [9898640]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Павел-П
Guest
На данный момент есть предположение, что вся эта ерунда происходит из-за наличия [ntext] колонки [srcResponse]. Предположение проходит проверку. Думаю, в течение недели буду точно знать. Такая проблема существовала в 7.0 версии, но у меня же 2008 :-). Вообщем подождем неделю.

Вчера еще раз проверил поможет ли Rebuild индексов - ничем не помог. Т.е. дело не во фрагментации, как я понимаю.
8 дек 10, 10:39    [9903240]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Павел-П
Guest
Вот нашел подтверждение, что проблема есть и в 2005.
http://www.eggheadcafe.com/software/aspnet/31693624/issue-with-unclaimed-space.aspx
Скоро будем знать точно, причина ntext или не ntext.
8 дек 10, 10:52    [9903328]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Павел-П
Guest
Причиной был не ntext.
Причину обнаружил с помощью
select * from sys.dm_db_index_physical_stats(db_id(), object_id('T_Test'), null, null, N'DETAILED')
Этот запрос показал, что у меня в строчке CLUSTERED INDEX LOB_DATA есть целых
2 690 956 - ghost records. И это кажется и есть прикол самого sql server-а. Т.е. он у меня сейчас не удаляет ghost records вообще.

А вот и подтверждающая ссылка нашлась.
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/c023e51c-92ad-471b-89a6-8c93732ee6cb/
Там совет - перезапустите sql server instance.

Прямо сейчас его проверить не могу, но скоро проверим.
13 дек 10, 13:28    [9928904]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Павел-П
А вот и подтверждающая ссылка нашлась.
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/c023e51c-92ad-471b-89a6-8c93732ee6cb/
Там совет - перезапустите sql server instance.

Прямо сейчас его проверить не могу, но скоро проверим.

А системный коннект для ghost cleaner-а есть в списке ?
13 дек 10, 13:31    [9928937]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Павел-П
Guest
Glory,

Этим я сейчас и занимаюсь, пытаюсь определить есть ли он в списке процессов. Пока не увидел.
Дело в том, что в явном виде, как я понял, его там может и не быть. Запускается каждые 5 сек или что-то типа того. Т.е. появляется периодически.

Вот один из предложенных вариантов определения.
http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost-cleanup-in-depth.aspx

Но наиболее важный прикол, что он все-таки тупо может остановиться. И об этом надо помнить.
13 дек 10, 14:55    [9929628]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Павел-П
Guest
Glory,

Ситуация стала еще интересней. Процесс то вроде живет.

DatabaseName Sesession_id request_id start_time status command
Test 16 0 2010-10-13 07:24:58.920 background GHOST CLEANUP

... но не удаляет.
13 дек 10, 15:21    [9929855]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Павел-П
Guest
Оказывается, при большом количестве вставок/удалении данных ghost cleanup task может просто не успевать очищать данные и эти удаленные данные могут накапливаться.

http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Ghost-cleanup-in-depth.aspx


When the ghost cleanup task starts up it checks to see if its been told to cleanup a page - if so it goes and does it. If not, it picks the next database that is marked as having some ghost records and looks through the PFS allocation map pages to see if there are any ghost records to cleanup. It will check through or cleanup a limited number of pages each time it wakes up - I remember the limit is 10 pages - to ensure it doesn't swamp the system. So - the ghost records will eventually be removed - either by the ghost cleanup task processing a database for ghost records or by it specifically being told to remove them from a page. If it processes a database and doesn't find any ghost records, it marks the database as not having any ghost records so it will be skipped next time.

Ключевые слова - I remember the limit is 10 pages - to ensure it doesn't swamp the system.

Т.е. при большом количестве вставок/удалений (а LOB-ы еще больше усугубляют дело) - дело будет плохо. Интересно, а как это обходить.

Может кто-нибудь знает.
13 дек 10, 16:57    [9930577]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Павел-П
Guest
Вообщем проблема решилась.
По непонятных причинам переклинило ghost cleanup process. Самое интересное, что он работал и очищал ghost rows на моей базе данных, но делал это только для IN_ROW_DATA.
А решилось все обычным рестартом sql server instance.
После этого ghost cleanup process завис на всю ночь, очищая накопленные 3 000 000 строк записей призраков. А к утру - все стало хорошо.
Интересно, повторится ли такая ситуация еще раз.
14 дек 10, 11:39    [9933886]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Oleg Dok
Member

Откуда:
Сообщений: 2
Проблема 100% повторится.

Под 2008 сервером за один цикл чистится 200 страниц.

У меня есть та же проблема:

Таблица ок. 500 млн. записей, BLOB-поле типа varbinary(max) в отдельной файлгруппе на 6 файлов
'large value types out of row' - опция включена



sys.dm_db_index_physical_stats показывает десятки миллионов ghosts в LOB_DATA кластерного индекса.

Реиндекс, раорганайз, скан по индексу - не помогают.

DBCC cleantable
DBCC UPDATEUSAGE
DBCC FORCEGHOSTCLEANUP (недокументированная)
EXEC dbo.sp_clean_db_file_free_space

- не помогают!

Помогают две вещи - перегруз всех записей в новую табл или рестарт сервера - после рестарта GHOST_CLEANUP отрабатывает как положено и всё чистит, после чего ghost records count для LOB_DATA снова начинает расти.

Обе эти вещи - не опция на продакшне.

Может кто знает решение или писать багрепорт в МС?
29 апр 11, 19:22    [10589187]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Oleg Dok
Member

Откуда:
Сообщений: 2
Да - sql server 2008 SP2 x64
29 апр 11, 19:24    [10589190]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Павел-П
Guest
Oleg Dok,

В моем случае ситуация не повторилась и больше и не воспроизводится.
Все хорошо и прекрасно сейчас, хотя нагрузки даже возрасли в разы.
5 май 11, 02:10    [10607536]     Ответить | Цитировать Сообщить модератору
 Re: LOB-объекты. Не очищается место после удаления. Помогите советом, пжл.  [new]
Maks Bragar
Member

Откуда: UA->AT
Сообщений: 165
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

один из вариантов когда у меня не работал шринк.

CREATE TABLE [dbo].[Ist_1](
	[a] [varchar](32) NOT NULL,
	[b] [varchar](100) NOT NULL,
	[c] [varchar](100) NULL,
	[d] [varchar](10) NULL,
	[e] [varchar](100) NULL,
	[f] [varchar](32) NULL
) ON [PRIMARY]

в ней sys.dm_db_index_physical_stats показывает много ghost_record_count.

НИКАКИМИ средствами шринкнуть файл не мог (кроме докидывания файла в ФГ и шринка мдф с EMPTY_FILE)

Добавил Id , сделал кластерный, ребилд, вуаля, шринк заработал.
На будущее, тем кто борется со шринком, первым делом банально глянуть:
select * from sys.dm_db_index_physical_stats (DB_ID(), NULL, null, null, N'DETAILED') sts
WHERE sts.ghost_record_count != 0
30 ноя 11, 11:15    [11681873]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить