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

Откуда:
Сообщений: 9
Здравствуйте, установлен Sql Server 2916 sp 2. При вставке в heap таблицу table (id int), данные логируются полностью, несмотря на хинт tablock и включенный режим bulk logged. Можно ли это исправить?
20 сен 18, 22:51    [21681465]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1369
Человеческий,

ознакомьтесь
https://docs.microsoft.com/ru-ru/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import?view=sql-server-2017

https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in-bulk-load-operations/
20 сен 18, 23:36    [21681486]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Человеческий
Здравствуйте, установлен Sql Server 2916 sp 2. При вставке в heap таблицу table (id int), данные логируются полностью, несмотря на хинт tablock и включенный режим bulk logged. Можно ли это исправить?

репро давайте.
вот мой 2016 SP2 и мое репро,
прескрасно минимально логируется
вставка int в кучу с tablock в модели bulk_logged:

create database db_test;

alter database db_test
set recovery bulk_logged;

use db_test;
create table dbo.test(n int);

backup database db_test
to disk = 'C:\backups\sql_2016_dev\db_test.bak';

insert into dbo.test with(tablock) (n)
select top 10000 row_number() over(order by getdate())
from sys.all_columns a1 cross join sys.all_columns a2;

select @@version;

select operation, context, count(*)
from sys.fn_dblog(null, null)
group by operation, context
order by count(*) desc;

+
operation context (No column name)
LOP_COUNT_DELTA LCX_CLUSTERED 33
LOP_MODIFY_ROW LCX_PFS 26
LOP_SET_FREE_SPACE LCX_PFS 17
LOP_SET_BITS LCX_GAM 9
LOP_SET_BITS LCX_IAM 9
LOP_ROOT_CHANGE LCX_CLUSTERED 5
LOP_COMMIT_XACT LCX_NULL 5
LOP_LOCK_XACT LCX_NULL 5
LOP_BEGIN_XACT LCX_NULL 5
LOP_MODIFY_ROW LCX_BOOT_PAGE 4
LOP_SET_BITS LCX_DIFF_MAP 4
LOP_HOBT_DELTA LCX_NULL 4
LOP_SET_BITS LCX_ML_MAP 3
LOP_END_CKPT LCX_NULL 2
LOP_XACT_CKPT LCX_BOOT_PAGE_CKPT 2
LOP_FORMAT_PAGE LCX_HEAP 1
LOP_SHRINK_NOOP LCX_DIAGNOSTICS 1
LOP_CREATE_ALLOCCHAIN LCX_NULL 1
LOP_FORMAT_PAGE LCX_IAM 1
LOP_BEGIN_CKPT LCX_NULL 1

как видите, в лог вообще не ушло никаких операций в контексте кучи.
все, что меняется, это PFS, GAM, IAM и маленько системных таблиц

К сообщению приложен файл. Размер - 39Kb
21 сен 18, 10:06    [21681690]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
Androgen1985
Member

Откуда:
Сообщений: 55
Данные в heap есть?

Вот ознакомьтесь со статьей здесь приведены различные варианты и указана возможность минимального протоколирования http://www.sqlservercentral.com/articles/Administration/100856/
21 сен 18, 10:41    [21681747]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Androgen1985
Данные в heap есть?

Вот ознакомьтесь со статьей здесь приведены различные варианты и указана возможность минимального протоколирования

вы сами-то ознакомились с содержимым своей же ссылки?
хоть есть, хоть нет данных, это же КУЧА.
все новые данные пойдут на новые же стрницы.
ну сделайте в моем репро предварительную вставку в кучу,
чтобы не была пустой.
ничего не поменяется.
21 сен 18, 10:49    [21681758]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
Человеческий
Member

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

Если в вашем примере закомментировать подстроку "-- cross join sys.all_columns a2" у меня логируется полностью.
21 сен 18, 20:00    [21682350]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Человеческий
Yasha123,

Если в вашем примере закомментировать подстроку "-- cross join sys.all_columns a2" у меня логируется полностью.

конкретно сколько строк(страниц) вы вставляете? 100 строк/1 страницу?
23 сен 18, 21:19    [21683365]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
Человеческий
Member

Откуда:
Сообщений: 9
Yasha123,
9542 строки

create database db_test;
go
alter database db_test
set recovery bulk_logged;

use db_test;
create table dbo.test(n int);

backup database db_test
to disk = 'C:\backups\sql_2016_dev\db_test.bak';

insert into dbo.test with(tablock) (n)
select top 10000 row_number() over(order by getdate())
from sys.all_columns a1 --cross join sys.all_columns a2;

select @@version;

select operation, context, count(*)
from sys.fn_dblog(null, null)
group by operation, context
order by count(*) desc;


Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)   Mar 18 2018 09:11:49
Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 16299: ) 


+
LOP_INSERT_ROWS LCX_HEAP 9542
LOP_SET_FREE_SPACE LCX_PFS 61
LOP_BEGIN_XACT LCX_NULL 24
LOP_COMMIT_XACT LCX_NULL 24
LOP_LOCK_XACT LCX_NULL 23
LOP_MODIFY_ROW LCX_PFS 22
LOP_HOBT_DELTA LCX_NULL 19
LOP_FORMAT_PAGE LCX_HEAP 16
LOP_INSYSXACT LCX_CLUSTERED 12
LOP_INSERT_ROWS LCX_CLUSTERED 11
LOP_SET_BITS LCX_DIFF_MAP 11
LOP_INSERT_ROWS LCX_INDEX_LEAF 6
LOP_MODIFY_HEADER LCX_HEAP 4
LOP_INSYSXACT LCX_INDEX_INTERIOR 4
LOP_SHRINK_NOOP LCX_NULL 3
LOP_MODIFY_ROW LCX_CLUSTERED 3
LOP_SET_BITS LCX_IAM 3
LOP_ROOT_CHANGE LCX_CLUSTERED 3
LOP_SET_BITS LCX_GAM 3
LOP_FORMAT_PAGE LCX_CLUSTERED 2
LOP_MODIFY_ROW LCX_BOOT_PAGE_CKPT 2
LOP_DELETE_SPLIT LCX_CLUSTERED 2
LOP_INSERT_ROWS LCX_INDEX_INTERIOR 2
LOP_PREP_XACT LCX_NULL 1
LOP_END_CKPT LCX_NULL 1
LOP_CREATE_ALLOCCHAIN LCX_NULL 1
LOP_FORMAT_PAGE LCX_IAM 1
LOP_FILE_HDR_MODIFY LCX_FILE_HEADER 1
LOP_XACT_CKPT LCX_BOOT_PAGE_CKPT 1
LOP_MODIFY_COLUMNS LCX_CLUSTERED 1
LOP_BEGIN_CKPT LCX_NULL 1
24 сен 18, 15:10    [21684163]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Человеческий,
значит, 16 страниц это тоже пшик, а не балк
24 сен 18, 17:43    [21684431]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
Человеческий
Member

Откуда:
Сообщений: 9
create database db_test;
go
alter database db_test
set recovery bulk_logged;

use db_test;
create table dbo.test(n int, name varchar(100));

backup database db_test
to disk = 'C:\backups\sql_2016_dev\db_test.bak' with init

insert into dbo.test with(tablock) (n)
select top 10000 row_number() over(order by getdate())
from sys.all_columns a1 --cross join sys.all_columns a2;

select @@version;

select operation, context, count(*)
from sys.fn_dblog(null, null)
group by operation, context
order by count(*) desc;


+
LOP_MODIFY_ROW LCX_PFS 30
LOP_SET_FREE_SPACE LCX_PFS 16
LOP_SET_BITS LCX_DIFF_MAP 13
LOP_INSYSXACT LCX_CLUSTERED 12
LOP_INSERT_ROWS LCX_CLUSTERED 11
LOP_SET_BITS LCX_GAM 10
LOP_SET_BITS LCX_IAM 10
LOP_COMMIT_XACT LCX_NULL 9
LOP_BEGIN_XACT LCX_NULL 9
LOP_LOCK_XACT LCX_NULL 8
LOP_HOBT_DELTA LCX_NULL 6
LOP_INSERT_ROWS LCX_INDEX_LEAF 6
LOP_ROOT_CHANGE LCX_CLUSTERED 5
LOP_MODIFY_HEADER LCX_HEAP 4
LOP_INSYSXACT LCX_INDEX_INTERIOR 4
LOP_SHRINK_NOOP LCX_NULL 3
LOP_MODIFY_ROW LCX_CLUSTERED 3
LOP_SET_BITS LCX_ML_MAP 3
LOP_FORMAT_PAGE LCX_CLUSTERED 2
LOP_MODIFY_ROW LCX_BOOT_PAGE_CKPT 2
LOP_DELETE_SPLIT LCX_CLUSTERED 2
LOP_INSERT_ROWS LCX_INDEX_INTERIOR 2
LOP_PREP_XACT LCX_NULL 1
LOP_END_CKPT LCX_NULL 1
LOP_CREATE_ALLOCCHAIN LCX_NULL 1
LOP_FORMAT_PAGE LCX_IAM 1
LOP_FILE_HDR_MODIFY LCX_FILE_HEADER 1
LOP_BEGIN_CKPT LCX_NULL 1
LOP_FORMAT_PAGE LCX_HEAP 1
LOP_MODIFY_COLUMNS LCX_CLUSTERED 1
LOP_XACT_CKPT LCX_BOOT_PAGE_CKPT 1
24 сен 18, 17:54    [21684442]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Человеческий
create database db_test;
go
alter database db_test
set recovery bulk_logged;

use db_test;
create table dbo.test(n int, name varchar(100));

backup database db_test
to disk = 'C:\backups\sql_2016_dev\db_test.bak' with init

insert into dbo.test with(tablock) (n)
select top 10000 row_number() over(order by getdate())
from sys.all_columns a1 --cross join sys.all_columns a2;

select @@version;

select operation, context, count(*)
from sys.fn_dblog(null, null)
group by operation, context
order by count(*) desc;


+
LOP_MODIFY_ROW LCX_PFS 30
LOP_SET_FREE_SPACE LCX_PFS 16
LOP_SET_BITS LCX_DIFF_MAP 13
LOP_INSYSXACT LCX_CLUSTERED 12
LOP_INSERT_ROWS LCX_CLUSTERED 11
LOP_SET_BITS LCX_GAM 10
LOP_SET_BITS LCX_IAM 10
LOP_COMMIT_XACT LCX_NULL 9
LOP_BEGIN_XACT LCX_NULL 9
LOP_LOCK_XACT LCX_NULL 8
LOP_HOBT_DELTA LCX_NULL 6
LOP_INSERT_ROWS LCX_INDEX_LEAF 6
LOP_ROOT_CHANGE LCX_CLUSTERED 5
LOP_MODIFY_HEADER LCX_HEAP 4
LOP_INSYSXACT LCX_INDEX_INTERIOR 4
LOP_SHRINK_NOOP LCX_NULL 3
LOP_MODIFY_ROW LCX_CLUSTERED 3
LOP_SET_BITS LCX_ML_MAP 3
LOP_FORMAT_PAGE LCX_CLUSTERED 2
LOP_MODIFY_ROW LCX_BOOT_PAGE_CKPT 2
LOP_DELETE_SPLIT LCX_CLUSTERED 2
LOP_INSERT_ROWS LCX_INDEX_INTERIOR 2
LOP_PREP_XACT LCX_NULL 1
LOP_END_CKPT LCX_NULL 1
LOP_CREATE_ALLOCCHAIN LCX_NULL 1
LOP_FORMAT_PAGE LCX_IAM 1
LOP_FILE_HDR_MODIFY LCX_FILE_HEADER 1
LOP_BEGIN_CKPT LCX_NULL 1
LOP_FORMAT_PAGE LCX_HEAP 1
LOP_MODIFY_COLUMNS LCX_CLUSTERED 1
LOP_XACT_CKPT LCX_BOOT_PAGE_CKPT 1

и что? вы не строки считайте, а страницы
24 сен 18, 18:16    [21684474]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
пардон, все те же 16 страниц.
Наверное, товарищи оценили как и я, читая по диагонали, т. е. видимо код сервера не оценивает реально вставляемое, а делает оценку максимальной возможной заполненности, исходя из метаданных таблицы
24 сен 18, 18:30    [21684487]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Человеческий
Yasha123,

Если в вашем примере закомментировать подстроку "-- cross join sys.all_columns a2" у меня логируется полностью.

Ну что Вы с Анной спорите. Ее не переубедить.
Делаете проще - файл базы на диск C, файл журнала на диск D. Создаете таблицу с кластерным индексом, вставляете из другой плоской таблицы строки, замеряете "fsutil fsinfo statistics c:" и "fsutil fsinfo statistics d:" до операции и после операции. Проверяете UserFileWriteBytes. Далее создаете таблицу-heap и из той же таблицы вставляете такое же количество строк. И аналогично замеряете до и после операции.
По количеству байт, записанных на диск D, смотрите, сколько реально windows записала в файл журнала транзакций. И думаете дальше.
P.S. Таки логично при этом отключить индексацию, антивирус и прочее.
24 сен 18, 20:17    [21684598]     Ответить | Цитировать Сообщить модератору
 Re: Условие для минимального логирования?  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Andy_OLAP
...

дарагуля,
ты за мной разве что в туалет не сбегал, угомонись уже,
неси свой бред там, где меня нет.
заранее спасибо
---
Человеческий, подытожим.
для минимального логирования есть еще и некий "объемный" порог,
в документации вроде нигде не упоминается,
но это логично, что он есть.
и перед вставкой ее предполагаемый объем высчитывают как число строк * макс. длину строки для данной таблицы
24 сен 18, 21:07    [21684656]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить