Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 удаление большого кол-ва строк  [new]
удаление
Guest
Есть таблица содержащая 107 млн строк. Необходимо удалить примерно 100 млн (выборка по колонке datetime). За сутки добавляются примерно 650 тыс новых записей.
delete from sample_audit_log
where audit_timestamp < dateadd(week,-2,getdate())

запрос ставит эксклюзивную блокировку на таблицу, что соответственно лочит insert'ы.
truncate делать нельзя.

CREATE TABLE [dbo].[SAMPLE_AUDIT_LOG](
	[COUNTER] [int] NOT NULL,
	[SAMPLE_NUMBER] [int] NULL,
	[TABLE_NAME] [varchar](20) NULL,
	[TABLE_KEY] [int] NULL,
	[AUDIT_TYPE] [varchar](80) NULL,
	[ACTION] [varchar](20) NULL,
	[REASON] [varchar](254) NULL,
	[TRANS_STRING] [varchar](max) NULL,
	[TRANS_ORDER] [int] NULL,
	[USER_NAME] [varchar](10) NULL,
	[AUDIT_TIMESTAMP] [datetime] NULL,
	[RECORD_SIGNED] [varchar](1) NULL
) ON [PRIMARY]

CREATE CLUSTERED INDEX [AUDIT_TIMESTAMP] ON [dbo].[SAMPLE_AUDIT_LOG] 
(
	[AUDIT_TIMESTAMP] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
26 мар 12, 16:22    [12315444]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
удаление,а в чем вопрос то?Как быстро удалить?
Может быстрее 7 млн строк скопировать в другую таблицу,потом drop table + sp_rename.
Секционирование можно попробовать ...
26 мар 12, 16:29    [12315521]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
удаление
Guest
Да, вопрос именно в этом: как удалить быстро, при этом не блокируя таблицу на вставку новых записей.
секционирование не возможно (забыл указать версию) - SQL Server 2005 Standart Edition SP4
Дисковый массив достаточно медленный и удаление вышеописанным способом выливается в колоссальный простой.
26 мар 12, 16:32    [12315553]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Да просто удалять в цикле сравнительно небольшими порциями.
Причём в отдельных транзакциях (чтобы лог не сильно разрастался)
Например
DELETE TOP(100000) ...
Пока @@ROWCOUNT не вернёт 0.
Только сначала его надо сделать как-то больше 0
26 мар 12, 16:35    [12315609]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
AndyD
Member

Откуда:
Сообщений: 30
Операция разовая?

Удаляйте не большим куском сразу, а по несколько дес. тысяч записей в одной транзакции.
Можно еще добавить
delete from sample_audit_log with (paglock)
...

что бы меньше времени/ресурсов на блокировки уходило
26 мар 12, 16:37    [12315626]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
Самое быстрое удаление из большой таблицы - это отключение секций, для секционированной таблицы.
https://www.sql.ru/articles/mssql/2005/073102partitionedtablesandindexes.shtml#58

Может для Вас подойдет логическое удаление данных?
http://src-code.net/logicheskoe-udalenie-dannyx/
26 мар 12, 17:04    [12315947]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
AndyD
Операция разовая?

Удаляйте не большим куском сразу, а по несколько дес. тысяч записей в одной транзакции.
Можно еще добавить
delete from sample_audit_log with (paglock)
...

что бы меньше времени/ресурсов на блокировки уходило
Page-lock может привести к дедлокам, когда идет доступ к ключам одной страницы в разном порядке. Если один кластерный индекс, без некластерных, и удаленные записи никак не пересекаются со вставляемыми, то можно.
26 мар 12, 17:10    [12315981]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
Готовый пример, удаление частями (большая таблица)
для версии SQL 2005 и позже:
DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1

WHILE (@RowsDeleted > 0)
    BEGIN
        DELETE TOP (10000) FROM MyTable WHERE ..... -- условие WHERE
        SET @RowsDeleted = @@ROWCOUNT
    END


Для SQL 2000:
DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1
SET ROWCOUNT 10000 -- порция, количество строк удаления

WHILE (@RowsDeleted > 0)
    BEGIN
        DELETE FROM MyTable WHERE ..... -- условие WHERE
        SET @RowsDeleted = @@ROWCOUNT
    END
26 мар 12, 17:48    [12316299]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
удаление,

BEGIN TRAN
1. Переимновать SAMPLE_AUDIT_LOG в OLD_SAMPLE_AUDIT_LOG
2. Создать новую пустую таблицу SAMPLE_AUDIT_LOG, и пусть туда идут вставки.
COMMIT TRAN

Займет меньше секунды, главное получить эксклюзивный лок на таблицу.

Потом вытянуть нужных 7 млн из старой таблицы в новую и старую грохнуть.
27 мар 12, 00:53    [12317871]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
angel_zar
Member

Откуда: Барнаул
Сообщений: 902
trew,

Только ROWCOUNT надо обратно бы вернуть
27 мар 12, 06:42    [12318019]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
squid
Member

Откуда: LA
Сообщений: 590
Правильно сказал товарищ trew - секционирование ваше все. Все остальное -изврат с теми или иными побочными эффектами в виде тормозов и локов. Более того, рекомендация юзать код
DELETE TOP (10000) FROM MyTable
может привести опять же к локу всей таблицы. цитата из



автор
Lock Escalation Thresholds

Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:

A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.

A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.

The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.

If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.

27 мар 12, 22:42    [12324033]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
squid
Member

Откуда: LA
Сообщений: 590
почерпнуто из
27 мар 12, 22:43    [12324034]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
squid
Member

Откуда: LA
Сообщений: 590
что с форумом. не постятся ссылки.
http://msdn.microsoft.com/en-us/library/ms184286.aspx
27 мар 12, 22:44    [12324039]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
squid
Правильно сказал товарищ trew - секционирование ваше все. Все остальное -изврат с теми или иными побочными эффектами в виде тормозов и локов.

Если это разовая операция, то городить огород с секциями и ради этого покупать Enterprise лицензию (если нужно), нет никакого смысла.

В данном конкретном случае, этот совет практического смысла не имеет, ибо таблица уже есть, с данными, не секционированна. Вы предлагаете сначала её секционировать, а потом быстро удалить данные? Это изврат в квадрате, и без локов тут уж точно не обойтись.
28 мар 12, 01:01    [12324394]     Ответить | Цитировать Сообщить модератору
 Re: удаление большого кол-ва строк  [new]
удаление
Guest
Mind
удаление,

BEGIN TRAN
1. Переимновать SAMPLE_AUDIT_LOG в OLD_SAMPLE_AUDIT_LOG
2. Создать новую пустую таблицу SAMPLE_AUDIT_LOG, и пусть туда идут вставки.
COMMIT TRAN

Займет меньше секунды, главное получить эксклюзивный лок на таблицу.

Потом вытянуть нужных 7 млн из старой таблицы в новую и старую грохнуть.


Всем спасибо. Воспользовался этим советом.
3 апр 12, 19:35    [12360080]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить