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

Есть таблица около 5 000 000 строк

Сейчас на таблице стоит первичный ключ (чтобы не было пустых строк и дубликатов)

Заливка в такую таблица занимает много времени

Можно ли сделать следующее:

убрать первичный ключ, вместо него сделать кластерный индекс
и удалять его перед заливкой а потом вешать обратно
12 сен 14, 09:52    [16566888]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
Glory
Member

Откуда:
Сообщений: 104751
локкии
Заливка в такую таблица занимает много времени

Сколько - это много ?
Много по какой причине ?

локкии
убрать первичный ключ, вместо него сделать кластерный индекс

А вы уверены, что сейчас ваш ПК не кластерный ?
12 сен 14, 09:54    [16566893]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
Glory
Member

Откуда:
Сообщений: 104751
локкии
и удалять его перед заливкой а потом вешать обратно

А что потому делать с "(чтобы не было пустых строк и дубликатов)" ?
12 сен 14, 09:54    [16566895]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
как заливаете? как выглядит таблица и первичный ключ? триггеры есть?
12 сен 14, 09:55    [16566897]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
локкии
Guest
Glory,

Без RK данные заливаются 30 секунд и RK 3 минуты
12 сен 14, 09:57    [16566900]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
локкии
Guest
churupaha,

заливаю через ETL
таблица справочник 20 тестовых полей и айдишник [bigint]
12 сен 14, 09:59    [16566909]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
Glory
Member

Откуда:
Сообщений: 104751
локкии
Без RK данные заливаются 30 секунд и RK 3 минуты

Вы знаете, что за ПК у вас и как он создан ?
Вы смотрели план выполнения ?
Анализировали fill factor ?
Какой командой происходит "заливка" ?
12 сен 14, 10:01    [16566911]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
локкии,

а как ваша etl льеть? по одному insert? а строки идут в базу в порядке возрастания ключа индекса или в разнобой (page splits)?
12 сен 14, 10:02    [16566913]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
локкии
Guest
Glory,

CREATE TABLE [x](
[Id] [bigint] NOT NULL,
CONSTRAINT [PK_x] PRIMARY KEY ([x])
) ON [PRIMARY]


ЕТЛ = datasource-datadestonation


Остальные вопросы не понял )
12 сен 14, 10:03    [16566918]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Glory

+ padindex
12 сен 14, 10:04    [16566919]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
Glory
Member

Откуда:
Сообщений: 104751
локкии
ЕТЛ = datasource-datadestonation

Не имя программы, а текст команды "Заливка в такую таблица"

локкии
CREATE TABLE [x](
[Id] [bigint] NOT NULL,
CONSTRAINT [PK_x] PRIMARY KEY ([x])
) ON [PRIMARY]


PRIMARY KEY constraints default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED.
12 сен 14, 10:07    [16566926]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
локкии
Guest
Glory,

она сама :) в етл есть пакет data flow task
12 сен 14, 10:11    [16566941]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
Glory
Member

Откуда:
Сообщений: 104751
локкии
она сама :) в етл есть пакет data flow task

Да хоть "свободу Анжеле Дэвис"
Нужен текст команды, которая _выполняется_, а нее название объекта
12 сен 14, 10:12    [16566947]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
локкии
Guest
Glory,

http://msdn.microsoft.com/ru-ru/library/ms141122.aspx
Во время выполнения задача потока данных создает план выполнения в соответствии с потоком данных, а подсистема обработки потока данных выполняет этот план. Возможно создание задачи потока данных, не содержащей потока данных, но задача будет выполнена, только если она включает по меньшей мере один поток данных.
12 сен 14, 10:42    [16567062]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
локкии,

профайлером гляньте что сыпется серверу от вашей etl
12 сен 14, 10:43    [16567066]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
Glory
Member

Откуда:
Сообщений: 104751
локкии
Во время выполнения задача потока данных создает план выполнения в соответствии с потоком данных, а подсистема обработки потока данных выполняет этот план. Возможно создание задачи потока данных, не содержащей потока данных, но задача будет выполнена, только если она включает по меньшей мере один поток данных.

Замечательное объяснение текста команды заливки данных.
12 сен 14, 10:45    [16567072]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 901
локкии
Glory,

CREATE TABLE [x](
[Id] [bigint] NOT NULL,
CONSTRAINT [PK_x] PRIMARY KEY ([x])
) ON [PRIMARY]


ЕТЛ = datasource-datadestonation


Остальные вопросы не понял )

скорее всего у вас просто latch на страницу на вставку, если ID возрастающее последовательное значение
посмотрите wait во время вставки.
12 сен 14, 10:58    [16567131]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Slava_Nik
локкии
Glory,

CREATE TABLE [x](
[Id] [bigint] NOT NULL,
CONSTRAINT [PK_x] PRIMARY KEY ([x])
) ON [PRIMARY]


ЕТЛ = datasource-datadestonation


Остальные вопросы не понял )

скорее всего у вас просто latch на страницу на вставку, если ID возрастающее последовательное значение
посмотрите wait во время вставки.


а с кем конкуренция за PAGELATH то? он вроде в один поток льет.
12 сен 14, 11:17    [16567238]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
aleks2
Guest
локкии
Добрый день!

Есть таблица около 5 000 000 строк

Сейчас на таблице стоит первичный ключ (чтобы не было пустых строк и дубликатов)

Заливка в такую таблица занимает много времени

Можно ли сделать следующее:

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


Феерический бред.
Кластерный индекс ~ таблица.
Его удаление-создание ~ очистка-заливка таблицы.

И он ишо жалуется на скорость заливки.
12 сен 14, 11:27    [16567298]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 901
churupaha
Slava_Nik
пропущено...

скорее всего у вас просто latch на страницу на вставку, если ID возрастающее последовательное значение
посмотрите wait во время вставки.


а с кем конкуренция за PAGELATH то? он вроде в один поток льет.

Чтобы зафиксировать вставить новую строку со следующим значением в эту же страницу, должна быть зафиксирована предыдущая запись. здесь ставиться pageiolatch на страницу, следующая вставка ждет этого ,отсюда идут задержки.

но это предположение, надо смотреть , как сказал, wait-ы, а не гадать
Кстати, по один поток или несколько так и не сказал автор.
12 сен 14, 11:57    [16567507]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Slava_Nik
churupaha
пропущено...


а с кем конкуренция за PAGELATH то? он вроде в один поток льет.

Чтобы зафиксировать вставить новую строку со следующим значением в эту же страницу, должна быть зафиксирована предыдущая запись. здесь ставиться pageiolatch на страницу, следующая вставка ждет этого ,отсюда идут задержки.

но это предположение, надо смотреть , как сказал, wait-ы, а не гадать
Кстати, по один поток или несколько так и не сказал автор.


не надо путать:

ожидание потоком завершения I/O (pageiolatch_**)

и

и конкуренцию потоков на захват pagelatch_** на "крайнюю правую" листовую страницу кластерного индекса.
12 сен 14, 12:07    [16567554]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3752
локкии
Glory,

CREATE TABLE [x](
[Id] [bigint] NOT NULL,
CONSTRAINT [PK_x] PRIMARY KEY ([x])
) ON [PRIMARY]


ЕТЛ = datasource-datadestonation


Остальные вопросы не понял )

так он у тебя и сейчас кластерный.
Сделай не кластерный.
Дропай перед заливкой,
после создавай.
12 сен 14, 13:29    [16568131]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
Glory
Member

Откуда:
Сообщений: 104751
Ivan Durak
Дропай перед заливкой,
после создавай.

А после ошибки создания - чеши репу, что делать с дубликатами и пустыми значениями.
12 сен 14, 13:59    [16568359]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
локкии
Остальные вопросы не понял )


про page splits

заливка в порядке возрастания кластерного ключа

место в логе занятое транзакцией заливки
database_transaction_log_used_mbdatabase_transaction_log_reserved_mbdatabase_transaction_log_record_count
164.0584.721000002


из них место в логе занятое page split'ами
page_splits_cnt_mbpage_splits_log_size_mbpage_splits_reserve_log_size_mb
78127.4975.54



заливка того же самого в рандомном порядке

место в логе занятое транзакцией заливки
database_transaction_log_used_mbdatabase_transaction_log_reserved_mbdatabase_transaction_log_record_count
216.8196.271000002


из них место в логе занятое page split'ами
page_splits_cnt_mbpage_splits_log_size_mbpage_splits_reserve_log_size_mb
1128060.25114.39



+ тест

create database page_splits_db;
go

use page_splits_db;
go

-- чтобы log truncation можно было сделать checkpoint'ом
-- а не log backup'ом
alter database page_splits_db set recovery simple;
go
-- готовим место
alter database page_splits_db modify file (name = page_splits_db, size = 500Mb, filegrowth = 50Mb);
go
alter database page_splits_db modify file (name = page_splits_db_log, size = 1Gb, filegrowth = 100Mb);
go

create table t
(
	id int,
	data char(50),

	constraint pk_t primary key clustered(id)
);
go

-- делаем log truncation
checkpoint;
go

set nocount on;

-- льем в порядке возрастания кластерного ключа
declare
	@n int = 1;

begin transaction;

while (@n <= 1000000)
begin
	insert into t(id) values(@n);

	set @n = @n + 1;
end;

select 
	database_transaction_log_bytes_used  / 1024.0 / 1024.0 as database_transaction_log_used_mb,
	database_transaction_log_bytes_reserved  / 1024.0 / 1024.0 as database_transaction_log_reserved_mb,
	database_transaction_log_record_count
from
	sys.dm_tran_current_transaction ct
		inner join
	sys.dm_tran_database_transactions dt on ct.transaction_id = dt.transaction_id

commit transaction;

go

-- смотрим pagesplits при заливке в порядке возрастания ключа
with t0
as
(
	select
		max([Transaction Name]) over (partition by [Transaction ID]) as txn,
		*
	from
		fn_dblog(null, null)

)
select
	count(distinct [Transaction ID]) as page_splits_cnt_mb,
	sum([Log Record Length]) / 1024.0 / 1024.0 as page_splits_log_size_mb,
	sum([Log Reserve]) / 1024.0 / 1024.0 as page_splits_reserve_log_size_mb
from
	t0
where
	txn = 'SplitPage';
go


-- перелопачиваем таблицу, чтобы при ее чтении в allocation order,
-- записи шли не по возрастанию колонки id, таблица будет
-- источником для следующей заливки
-- allocation order id-ов разбрасываем в источнике в порядке dummy
alter table t add dummy uniqueidentifier default newid() not null
go
alter table t drop constraint pk_t;
go
alter table t add constraint pk_t_dummy primary key clustered(dummy);
go

create table t2(id int primary key clustered, data char(50));
go

-- делаем log truncation
checkpoint;
go

-- льем НЕ в порядке возрастания кластерного ключа
set nocount on;

declare
	@n int;

declare
	src cursor for
		select
			id
		from
			t;
open src;

fetch next from src into @n;

begin transaction;

while (@@fetch_status = 0)
begin
	insert into t2(id) values(@n);

	fetch next from src into @n;
end;

select 
	database_transaction_log_bytes_used  / 1024.0 / 1024.0 as database_transaction_log_used_mb,
	database_transaction_log_bytes_reserved  / 1024.0 / 1024.0 as database_transaction_log_reserved_mb,
	database_transaction_log_record_count
from
	sys.dm_tran_current_transaction ct
		inner join
	sys.dm_tran_database_transactions dt on ct.transaction_id = dt.transaction_id

commit transaction;

close src;

deallocate src;
go

-- посмотрим сколько page splits при заливке не в порядке возрастания кластерного ключа
with t0
as
(
	select
		max([Transaction Name]) over (partition by [Transaction ID]) as txn,
		*
	from
		fn_dblog(null, null)

)
select
	count(distinct [Transaction ID]) as page_splits_cnt_mb,
	sum([Log Record Length]) / 1024.0 / 1024.0 as page_splits_log_size_mb,
	sum([Log Reserve]) / 1024.0 / 1024.0 as page_splits_reserve_log_size_mb
from
	t0
where
	txn = 'SplitPage';
go

12 сен 14, 14:28    [16568548]     Ответить | Цитировать Сообщить модератору
 Re: Кластерный идендекс и первичный ключ на большую таблицу  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
локкии

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


Вопрос, а зачем? Это даст прирост производительности? Другие индексы и ограничения есть смысл удалять, но не кластерный.

Если вы делает BULK INSERT, то ускорить вставку можно, если вы сортируете вставляете данные в порядке соответствующем первичному ключу.
12 сен 14, 15:24    [16568996]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить