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

Откуда:
Сообщений: 45
Есть БД, которая постоянно в онлайне (сайт).

В БД есть таблица:
CREATE TABLE [dbo].[UserSessions] (
	[id] [int] IDENTITY(1,1) NOT NULL,
	[sGuid] [uniqueidentifier] NOT NULL,
	[start] [datetime] NOT NULL,
	[end] [datetime] NOT NULL,
	[ip] [varchar](32) NOT NULL,
	и т.д.
 CONSTRAINT [PK_UserSession] PRIMARY KEY CLUSTERED ([sGuid] ASC)

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


В связи с неимоверным разрастанием базы обнаружилось, что за несколько лет накопилось слишком много ненужных данных в этой таблице (в менеджере показывает, что Число строк: 78883899, Пространство данных: 9 377,594 MB).

При попытке почистить таблицу:

DELETE FROM UserSessions WHERE [start]<'20130101'

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

Вопрос: как быстро и безболезненно очистить таблицу от старых данных и сжать БД после этого (а то файл БД много места занимает)?
7 ноя 13, 12:18    [15090048]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Label
Member

Откуда:
Сообщений: 45
Вдогонку.
Думал что это из-за большого объема данных, которые предстоит удалять в одной транзакции, пробовал частями, типа:
DELETE FROM UserSessions WHERE ([start]>='20100101' and [start]<'20100201')

тоже не помогло. Задумывается надолго...
7 ноя 13, 12:23    [15090093]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Dmitry Gurianov
Member

Откуда:
Сообщений: 651
В цикле открываем транзакцию, убиваем первую тыщу строк, коммитим, едем дальше.
Надеюсь, индекс на старт и на енд стоит?
7 ноя 13, 12:27    [15090128]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Dmitry Gurianov
Member

Откуда:
Сообщений: 651
Label, поторопился -индекс только на старт. Нету ведь? :)
7 ноя 13, 12:29    [15090159]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35400
Блог
попробуйте с top

DELETE TOP (10000)
  FROM 
  WHERE


правильнее конечно создать рядом новую таблицу и перелить туда нужные данные, но тут нужно хорошо разбираться с вашей системе (например, можно забыть про права на новосозданную таблицу), так что вариант с удалением по частям для вас менее рисковый
7 ноя 13, 12:33    [15090209]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Label
пробовал частями, типа:
DELETE FROM UserSessions WHERE ([start]>='20100101' and [start]<'20100201')

тоже не помогло. Задумывается надолго...

Может, часть слишком большая. Что возвращает запрос
SELECT COUNT(*) FROM UserSessions WHERE ([start]>='20100101' and [start]<'20100201')
?

Кластерный индекс по uniqueidentifier — это круто. Интересно, какая у вас фрагментация таблицы?
7 ноя 13, 12:36    [15090246]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Label
Member

Откуда:
Сообщений: 45
Dmitry Gurianov, нету никаких индексов (база не моя) ...

Ну я в общем так и пытался сделать малыми порциями, помесячно (не знаю как в цикле по тысячу строк удалять), но это не спасло, задумывается видать на поиске нужных строк с датами.

Критик, а по какому критерию он будет отбирать эти 10000 строк?
SELECT TOP 100 * from UserSessions 

отбирает строки с совершенно разными датами (от 2010 до 2013 года)?
Наверное по PRIMARY KEY CLUSTERED?
7 ноя 13, 12:42    [15090288]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Dmitry Gurianov
Member

Откуда:
Сообщений: 651
Label
Dmitry Gurianov, нету никаких индексов (база не моя) ...

Ну я в общем так и пытался сделать малыми порциями, помесячно (не знаю как в цикле по тысячу строк удалять), но это не спасло, задумывается видать на поиске нужных строк с датами.


Он будет тормозить вне зависимости от размера порции, потому что при любом WHERE ему надо пройти по 78 млн записей и каждую проверить на попадание в условие. Индекс спасет ситуацию, но тут уже я не могу сказать, сколько времени займет его перестроение и положит ли это базу :(
7 ноя 13, 12:46    [15090311]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Label
Member

Откуда:
Сообщений: 45
Гость333, попробовал
SELECT COUNT(*) FROM UserSessions WHERE ([start]>='20100101' and [start]<'20100201')

За пять минут не выполнился, пользователи стали жаловаться что сайт еле ворочается (разгар рабочего дня), отменил запрос.
7 ноя 13, 12:49    [15090335]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
last_sGuid
Guest
create table #delteted_guid([sGuid] [uniqueidentifier] not null, primary key ([sGuid]))

declare @last_sGuid uniqueidentifier = CAST(0x0 as uniqueidentifier)
	
while 1=1
begin

truncate table #delteted_guid

delete  a
output deleted.sGuid
into #delteted_guid ([sGuid])
from (
	select top (1000) *
	from dbo.UserSessions
	where sGuid > @last_sGuid
		and [start]<'20130101'
	order by sGuid
) a


if @@ROWCOUNT = 0
	break

select @last_sGuid = MAX(sGuid)
from #delteted_guid

waitfor delay '00:10:00'

end


не проверял, но как-то так
7 ноя 13, 13:18    [15090594]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35400
Блог
Label
а по какому критерию он будет отбирать эти 10000 строк?


в общем случае - как удобнее серверу,
но никто не запрещает сделать ограничение в where
7 ноя 13, 13:26    [15090656]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
last_sGuid
create table #delteted_guid([sGuid] [uniqueidentifier] not null, primary key ([sGuid]))

declare @last_sGuid uniqueidentifier = CAST(0x0 as uniqueidentifier)
	
while 1=1
begin

truncate table #delteted_guid

delete  a
output deleted.sGuid
into #delteted_guid ([sGuid])
from (
	select top (1000) *
	from dbo.UserSessions
	where sGuid > @last_sGuid
		and [start]<'20130101'
	order by sGuid
) a


if @@ROWCOUNT = 0
	break

select @last_sGuid = MAX(sGuid)
from #delteted_guid

waitfor delay '00:10:00'

end


не проверял, но как-то так


а Вы уверены что у ТС sGuid последовательно возрастают синхронно с датой ?????
7 ноя 13, 13:28    [15090684]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
last_sGuid
Guest
LexusR
last_sGuid
create table #delteted_guid([sGuid] [uniqueidentifier] not null, primary key ([sGuid]))

declare @last_sGuid uniqueidentifier = CAST(0x0 as uniqueidentifier)
	
while 1=1
begin

truncate table #delteted_guid

delete  a
output deleted.sGuid
into #delteted_guid ([sGuid])
from (
	select top (1000) *
	from dbo.UserSessions
	where sGuid > @last_sGuid
		and [start]<'20130101'
	order by sGuid
) a


if @@ROWCOUNT = 0
	break

select @last_sGuid = MAX(sGuid)
from #delteted_guid

waitfor delay '00:10:00'

end


не проверял, но как-то так


а Вы уверены что у ТС sGuid последовательно возрастают синхронно с датой ?????

а какая разница?
мы последовательно перебираем всю таблицу, двигаясь по кластерному индексу.
7 ноя 13, 13:45    [15090778]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6204
Критик
правильнее конечно создать рядом новую таблицу и перелить туда нужные данные, но тут нужно хорошо разбираться с вашей системе (например, можно забыть про права на новосозданную таблицу), так что вариант с удалением по частям для вас менее рисковый

Можно создать новую таблицу, перелить туда нужные данные, старой сделать TRUNCATE, данные перелить обратно, и новую таблицу убить. Всё права останутся на месте.
7 ноя 13, 13:57    [15090844]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
last_sGuid
Guest
Сон Веры Павловны
Критик
правильнее конечно создать рядом новую таблицу и перелить туда нужные данные, но тут нужно хорошо разбираться с вашей системе (например, можно забыть про права на новосозданную таблицу), так что вариант с удалением по частям для вас менее рисковый

Можно создать новую таблицу, перелить туда нужные данные, старой сделать TRUNCATE, данные перелить обратно, и новую таблицу убить. Всё права останутся на месте.

угу, только все данные, что попали туда после переливки, пропадут, причем в том числе пропадет часть тех данных, что попали в таблицу после начала переливки (кластерный индекс то гуиду).
7 ноя 13, 14:05    [15090904]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Label
Member

Откуда:
Сообщений: 45
last_sGuid, Выдала ошибку на:

select @last_sGuid = MAX(sGuid) from #delteted_guid

Сообщение 8117, уровень 16, состояние 1, строка ...
Тип данных операнда uniqueidentifier недопустим для оператора max.
8 ноя 13, 07:32    [15095102]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
kain111
Member

Откуда:
Сообщений: 227
был как то случай , когда в таблице была какая то битая запись и при попытке ее считать запрос вис, а другую часть таблицы отрабатывал моментально, может случай из этих ?
в свое время решил переливом читающейся части таблицы в новую и удалением старой, но может поможет checkdb
8 ноя 13, 09:17    [15095345]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Label
Member

Откуда:
Сообщений: 45
Спасибо всем за помощь!

В общем, поскольку простого выхода не нашлось, пришлось пожертвовать актуальными данными статистики - уговорил на TRUNCATE TABLE (бэкап, ясное дело, сделал :-)

Я правильно понимаю, что для того, чтобы такой ситуации не возникло в дальнейшем, на поле типа datetime надо создать индекс (он же не сильно затормозит вставку новых данных о посетителях сайта)?
8 ноя 13, 09:40    [15095423]     Ответить | Цитировать Сообщить модератору
 Re: Как очистить таблицу от ненужных данных?  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35400
Блог
еще исправьте
CONSTRAINT [PK_UserSession] PRIMARY KEY NONCLUSTERED ([sGuid] ASC)
8 ноя 13, 09:55    [15095483]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить