Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Transaction log на разных операциях  [new]
Rem
Member

Откуда:
Сообщений: 261
Модель восстановления full. Каким образом можно прикинуть использование transaction log для операций вставки, удаления, обновления, добавления записей, усечения конкретной таблицы с известной структурой?
Например, в таблице, занимающей примерно 30Gb (без учета индексов), надо удалить примерно половину записей. Насколько распухнет лог, если операцию удаления производить одним запросом? 15Gb + резервируемое место для отката, равное примерно тем же 15 Gb?

Записей в таблице немного, но куча жирных полей с типом image.
21 сен 15, 11:56    [18174424]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31950
Rem
Насколько распухнет лог, если операцию удаления производить одним запросом? 15Gb + резервируемое место для отката, равное примерно тем же 15 Gb?
По моему, только на 15 гб. Это при изменении удваивается - пишутся старые и новые значения.
21 сен 15, 12:07    [18174495]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Rem
Member

Откуда:
Сообщений: 261
alexeyvg
Rem
Насколько распухнет лог, если операцию удаления производить одним запросом? 15Gb + резервируемое место для отката, равное примерно тем же 15 Gb?
По моему, только на 15 гб. Это при изменении удваивается - пишутся старые и новые значения.


В статье на technet написано вот так:

Кроме того, регистрируются операции отката. Каждая транзакция резервирует в журнале транзакций место, чтобы при выполнении инструкции отката или возникновения ошибки в журнале было достаточно места для регистрации отката. Объем резервируемого пространства зависит от выполняемых в транзакции операций, но обычно он равен объему, необходимому для регистрации каждой из операций. Все это пространство после завершения транзакции освобождается.

Ну, а сразу после выполнения операции можно узнать сколько фактически места в логе к этой операции (транзакции) относится?
21 сен 15, 12:15    [18174534]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
invm
Member

Откуда: Москва
Сообщений: 9826
Rem
Каждая транзакция резервирует в журнале транзакций место, чтобы при выполнении инструкции отката или возникновения ошибки в журнале было достаточно места для регистрации отката.
На самом деле физически не резервируется. Сервер просто следит, чтобы в журнале было достаточно места для отката всех активных транзакций.
Rem
Ну, а сразу после выполнения операции можно узнать сколько фактически места в логе к этой операции (транзакции) относится?
sys.dm_tran_database_transactions + sys.dm_tran_current_transaction
Смотрим сколько было до и после операции.
21 сен 15, 12:28    [18174608]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Rem
Member

Откуда:
Сообщений: 261
invm
sys.dm_tran_database_transactions + sys.dm_tran_current_transaction
Смотрим сколько было до и после операции.


Спасибо.
Только, наверное, из-за того, что база имеет уровень совместимости SQL Server 2000 все поля, касающиеся кол-ва записей и размера, нулевые.
Сам сервер 2008-ой.
21 сен 15, 12:42    [18174714]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Rem
Member

Откуда:
Сообщений: 261
Кстати, что хранится в sys.dm_tran_database_transactions ?
Я вижу только одни и те же записи, которые относятся к tempdb, хотя с двумя базами, размещенными на сервере, постоянно идет очень активная работа.
21 сен 15, 12:54    [18174794]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
o-o
Guest
Rem
invm
sys.dm_tran_database_transactions + sys.dm_tran_current_transaction
Смотрим сколько было до и после операции.


Спасибо.
Только, наверное, из-за того, что база имеет уровень совместимости SQL Server 2000 все поля, касающиеся кол-ва записей и размера, нулевые.
Сам сервер 2008-ой.

нет.
наплевать ему на совместимость при записи в лог.
не то или не в той базе смотрире
21 сен 15, 12:55    [18174801]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Rem
Member

Откуда:
Сообщений: 261
o-o
не то или не в той базе смотрире


В sys.dm_tran_database_transactions есть поле database_id. Как можно смотреть не в той базе?
21 сен 15, 13:06    [18174858]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
invm
Member

Откуда: Москва
Сообщений: 9826
Rem
Как можно смотреть не в той базе?
Можно все :)
Значит не так смотрите.
+
create database DBTest2000;
go

alter database DBTest2000 set compatibility_level = 80;
go

exec sp_helpdb 'DBTest2000';
go

use DBTest2000;

begin tran;

select
 dt.*
from
 sys.dm_tran_current_transaction ct join
 sys.dm_tran_database_transactions dt on dt.transaction_id = ct.transaction_id
where
 dt.database_id = db_id();

create table dbo.t (id int primary key);

select
 dt.*
from
 sys.dm_tran_current_transaction ct join
 sys.dm_tran_database_transactions dt on dt.transaction_id = ct.transaction_id
where
 dt.database_id = db_id();

rollback;
go

use master;
drop database DBTest2000;
go
21 сен 15, 13:15    [18174914]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Glory
Member

Откуда:
Сообщений: 104751
Rem
В sys.dm_tran_database_transactions есть поле database_id. Как можно смотреть не в той базе?

Прав наверное нет
21 сен 15, 13:17    [18174920]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Rem
Member

Откуда:
Сообщений: 261
Glory
Rem
В sys.dm_tran_database_transactions есть поле database_id. Как можно смотреть не в той базе?

Прав наверное нет


Да нет. Я просто подумал, что в этой таблице будет информация о всех, т.е. в том числе и завершенных транзакциях.
Оказывается, что нет.
Увидел в ней желаемое, запустив оба запроса (удаление из пользовательской таблички и селект из dm_tran_database_transactions) в одной транзакции.
21 сен 15, 13:51    [18175124]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Rem
Member

Откуда:
Сообщений: 261
Ну и в итоге, если судить по database_transaction_log_bytes_used и database_transaction_log_bytes_reserved, то получился объем почти на 2 порядка меньший, чем примерный объем удаляемых записей.
Косвенно это подтверждает и размер файлов бэкапа лога, который в рамках log shipping делается каждые 15 минут.
21 сен 15, 14:21    [18175399]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Glory
Member

Откуда:
Сообщений: 104751
Rem
то получился объем почти на 2 порядка меньший, чем примерный объем удаляемых записей.

А вы как размер этих записей посчитали ?
21 сен 15, 14:23    [18175421]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Rem
Member

Откуда:
Сообщений: 261
Glory
Rem
то получился объем почти на 2 порядка меньший, чем примерный объем удаляемых записей.

А вы как размер этих записей посчитали ?


Размер записей в исходной таблице, несмотря на поля image, примерно одинаков, т.к. такова логика работы приложения.
Удалению подлежало практически ровно половина записей, объем занимаемый всеми данными, получил запросом.

SELECT
	(row_number() over(order by a3.name, a2.name))%2 as l1,
	a3.name AS [schemaname],
	a2.name AS [tablename],
	a1.rows as row_count,
	(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
	a1.data * 8 AS data,
	(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
	(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
	(SELECT 
		ps.object_id,
		SUM (
			CASE
				WHEN (ps.index_id < 2) THEN row_count
				ELSE 0
			END
			) AS [rows],
		SUM (ps.reserved_page_count) AS reserved,
		SUM (
			CASE
				WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
				ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
			END
			) AS data,
		SUM (ps.used_page_count) AS used
	FROM sys.dm_db_partition_stats ps
	GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
	(SELECT 
		it.parent_id,
		SUM(ps.reserved_page_count) AS reserved,
		SUM(ps.used_page_count) AS used
	 FROM sys.dm_db_partition_stats ps
	 INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
	 WHERE it.internal_type IN (202,204)
	 GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a3.name, a2.name




Ну, а началось всё с моего вопроса о том как можно соотнести размер лога с размером удаляемых записей. Предположили, что они будут примерно одинаковы, а за фактическим значением используемого лога подсказали обратиться к данным таблицы dm_tran_database_transactions.
На, а вообще, тут может оказаться неправильным и первое предположение, и второе. А, может быть, и оба.
21 сен 15, 14:49    [18175656]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
o-o
Guest
Rem
Ну и в итоге, если судить по database_transaction_log_bytes_used и database_transaction_log_bytes_reserved, то получился объем почти на 2 порядка меньший, чем примерный объем удаляемых записей.

а у меня ровно тот объем, что и ожидался.
репро:
вставляю 20 записей, каждая размером в страницу, т.е. 20 страниц данных.
удаляю 10 из них:
--create table dbo.t (id int, c char(8000));
--insert into dbo.t values (1, 'a');
--go 10

--insert into dbo.t values (2, 'b');
--go 10

begin transaction
    delete dbo.t
    where id = 1;
    
	select  db_name(dt.database_id) as dbname,
			database_transaction_log_record_count,
			database_transaction_log_bytes_used
	from sys.dm_tran_database_transactions as dt
		inner join sys.dm_tran_session_transactions as st
			on dt.transaction_id = st.transaction_id
 	where dt.database_id = DB_ID();
commit transaction;

вот результат:
dbname database_transaction_log_record_count database_transaction_log_bytes_used
r 11 81132

во втором окне все подробно:
моя транзакция 0000:0000044b,
10 записей по 8100 и одна 132 байта (LOP_BEGIN_XACT)
LOP_COMMIT_XACT не посчитался, т.к. идет уже после замера запросом в первом окне
21 сен 15, 15:15    [18175917]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Glory
Member

Откуда:
Сообщений: 104751
Rem
Размер записей в исходной таблице, несмотря на поля image, примерно одинаков, т.к. такова логика работы приложения.

Я не понял, вы сравниваете какие свои предполагаемые/усредненные объемы с конкретными показателями из запроса и удивляетесь, что они не сходятся ?
21 сен 15, 15:18    [18175939]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
o-o
Guest
ой, у меня картинка не прицепилась.
сейчас будет

К сообщению приложен файл. Размер - 116Kb
21 сен 15, 15:27    [18176006]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Rem
Member

Откуда:
Сообщений: 261
Glory
Rem
Размер записей в исходной таблице, несмотря на поля image, примерно одинаков, т.к. такова логика работы приложения.

Я не понял, вы сравниваете какие свои предполагаемые/усредненные объемы с конкретными показателями из запроса и удивляетесь, что они не сходятся ?


Да, именно так. Меня смутила разница на 2 порядка. Предложите свой правильный подход.
21 сен 15, 15:39    [18176074]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Glory
Member

Откуда:
Сообщений: 104751
Rem
Да, именно так. Меня смутила разница на 2 порядка. Предложите свой правильный подход.

Посчитать точно объем всех удаляемых вами данных.
21 сен 15, 15:43    [18176094]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Rem
Member

Откуда:
Сообщений: 261
Вчера данные в таблице занимали 22Гб при 64000 записей.
После удаления осталось 34000 записей, под данные теперь 9,5 Гб.
Среди файлов бэкапов логов, которые создаются каждые 15 минут, больших файлов не было. Пара файлов была размером 100-150Мб вместо обычных 8-10 Мб. Логи при бэкапе по умолчании жмутся?
21 сен 15, 17:30    [18176911]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Glory
Member

Откуда:
Сообщений: 104751
Rem
Вчера данные в таблице занимали 22Гб при 64000 записей.
После удаления осталось 34000 записей, под данные теперь 9,5 Гб.

Это опять ваши рассчеты на коленке или результаты каких-то _средств измерения_ ?

Rem
Логи при бэкапе по умолчании жмутся?

Компрессия есть не во всякой версии и редакции
21 сен 15, 19:12    [18177450]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Rem
Member

Откуда:
Сообщений: 261
Glory
Rem
Вчера данные в таблице занимали 22Гб при 64000 записей.
После удаления осталось 34000 записей, под данные теперь 9,5 Гб.

Это опять ваши рассчеты на коленке или результаты каких-то _средств измерения_ ?


Это результаты выполнения запроса, который я постил выше. Годится в качестве средства измерения???
Один на базе по состоянию на предыдущий день, другой - на рабочей базе.
Поскольку база в режиме совместимости с 2000-м я не могу воспользоваться штатным отчетом SSMS. Насколько я понимаю, этот запрос - суть то же самое, что выполняется отчетом "Использование дисковой памяти таблицей".

Еще раз: если этот запрос не врет, то объем, занимаемый данными таблицы, после удаления уменьшился почти на 13Гб. Размер бэкапов transaction log-а раз в 15 меньше, что соизмеримо с результатами выборки из sys.dm_tran_database_transactions.
Это еще можно было бы как-то объяснить при наличии у сиквела собственной дедупликации, т.к. в таблице очень много записей с одинаковым содержимым жирных полей image.
22 сен 15, 08:48    [18178632]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Glory
Member

Откуда:
Сообщений: 104751
Rem
Это результаты выполнения запроса, который я постил выше. Годится в качестве средства измерения???

sp_spaceused c updateusage годится

Rem
Еще раз: если этот запрос не врет, то объем, занимаемый данными таблицы, после удаления уменьшился почти на 13Гб. Размер бэкапов transaction log-а раз в 15 меньше, что соизмеримо с результатами выборки из sys.dm_tran_database_transactions.
Это еще можно было бы как-то объяснить при наличии у сиквела собственной дедупликации, т.к. в таблице очень много записей с одинаковым содержимым жирных полей image.

Размер только данных не есть размер таблицы и ее индексов
22 сен 15, 08:56    [18178661]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Rem
Member

Откуда:
Сообщений: 261
Да, с updateusage, действительно, не смотрел. После этого результаты запроса другие и теперь совпадают с sp_spaceused. Однако, дельта осталась та же самая - около 13 Гб.

На скрине верхние два результата - по таблице до удаления, нижние два - после.

К сообщению приложен файл. Размер - 56Kb
22 сен 15, 09:24    [18178764]     Ответить | Цитировать Сообщить модератору
 Re: Transaction log на разных операциях  [new]
Glory
Member

Откуда:
Сообщений: 104751
Rem
На скрине верхние два результата - по таблице до удаления, нижние два - после.

А запрос с замерами транзакции при удалении ?
22 сен 15, 09:42    [18178851]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить