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

Откуда:
Сообщений: 1427
Подскажите пожалуйста, можно ли получить текст запроса для sys.indexes.

Например, в гуиде MSSMS можно сгенерировать запрос на создание индекса, значит где-то в БД хранятся поля по которым строиться индекс. Вопрос: где?
4 июн 13, 18:09    [14390671]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
nigerman
Member

Откуда: Колыбель контрреволюции
Сообщений: 17787
_ч_,

sys.index_columns, кажется.
4 июн 13, 18:24    [14390723]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Гость333
Member

Откуда:
Сообщений: 3683
_ч_
можно ли получить текст запроса для sys.indexes

Если отвечать буквально, то можно так:
exec sp_helptext 'sys.indexes'

Но, судя по дальнейшему описанию, вам нужно сформировать DDL-запрос на создание индекса, основываясь на существующем индексе. Тогда сразу вопрос: в каком сценарии вы хотите это использовать? Почему недостаточно функционала SSMS?
4 июн 13, 19:05    [14390876]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
_ч_,

Все в системных представлениях есть, в относительно простом варианте генерации достаточно будет следующих представлений:
sys.tables
sys.indexes
sys.index_columns
sys.columns

а вот тут есть пара готовых решений:
http://stackoverflow.com/questions/9089700/generate-script-of-all-the-indexes-in-a-database
4 июн 13, 20:54    [14391173]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
_ч_,

Повесьте профайлер на SSMS и посмотрите, что она вызывает, когда вы в ней индекс скриптуете. Там же дофига всяких тонкостей.
5 июн 13, 02:56    [14392013]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
_ч_
Member

Откуда:
Сообщений: 1427
Гость333,

в связи с предыдущей моей темой, есть задача сделать так, чтобы таблицы для секционирования имели одинаковые column_id в sys.columns. Для этого я уже сделал скрипт на пересоздание одной из таблиц.

Но еще надо сделать так, чтобы в таблице sys.indexes index_id у двух таблиц имел одинаковые значения. Соответственно, хотелось бы сгенерировать эти индексы в нужной мне последовательности, вытаскивая информацию из системных таблиц.

Владимир Затуливетер, спасибо

Ennor Tiegael, действительно, спасибо
5 июн 13, 10:01    [14392633]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
_ч_,

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

declare @tbl sysname = 'dbo.MYTABLE'

select
	@tbl as tbl,
	i.name as idx_name,
	i.type_desc,
	i.is_unique,
	i.is_primary_key,
	i.filter_definition,
	case when ds.type = 'FG'
		then quotename(ds.name)
		else quotename(ds.name)+'('+quotename(pt.ps_id)+')'
	end data_space,
	reverse(stuff(reverse(icl.list),1,1,'')) idx_list,
	reverse(stuff(reverse(inc.list),1,1,'')) inc_list
from
	sys.indexes i
join
	sys.data_spaces ds
		on i.object_id = object_id(@tbl)
		and i.data_space_id = ds.data_space_id
outer apply
(
	select
		quotename(c.name)+
		case when ic.is_descending_key=1
			then ' desc'
			else ''
		end+',' as 'data()'
	from
		sys.index_columns ic
	join
		sys.columns c
			on ic.object_id = i.object_id
			and ic.index_id = i.index_id
			and ic.is_included_column = 0
			and c.object_id = ic.object_id
			and c.column_id = ic.column_id
	order by ic.key_ordinal
	for xml path('')
) icl(list)
outer apply
(
	select
		quotename(c.name)+',' as 'data()'
	from
		sys.index_columns ic
	join
		sys.columns c
			on ic.object_id = i.object_id
			and ic.index_id = i.index_id
			and ic.is_included_column = 1
			and c.object_id = ic.object_id
			and c.column_id = ic.column_id
	order by ic.key_ordinal
	for xml path('')
) inc(list)
outer apply
(
select c.name
from
	sys.index_columns ic
join
	sys.columns c
		on ic.object_id = i.object_id
		and ic.index_id = i.index_id
		and ic.partition_ordinal = 1
		and c.object_id = ic.object_id
		and c.column_id = ic.column_id
) pt (ps_id)
5 июн 13, 10:17    [14392711]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Mnior
В одно время извращался - прикручивал SourceSafe к SQL-ю. Вот некоторый, не последний, вариант генерации скрипта объектов ...
5924911
5 июн 13, 10:22    [14392744]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
_ч_
Member

Откуда:
Сообщений: 1427
Алексей Куренков, Mnior, ребят, спасибо за помощь
5 июн 13, 11:22    [14393163]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Гость333
Member

Откуда:
Сообщений: 3683
_ч_
Но еще надо сделать так, чтобы в таблице sys.indexes index_id у двух таблиц имел одинаковые значения. Соответственно, хотелось бы сгенерировать эти индексы в нужной мне последовательности

Откуда дровишки? Вот я создаю индексы в разном порядке и затем спокойно переключаю секции:
use tempdb;

create partition function test_pf(int) as range left for values(10);
create partition scheme test_ps as partition test_pf all to ([primary]);

create table dbo.first_table(id int not null, x varchar(20)) on test_ps(id);
create index i1_first_table on dbo.first_table(id) on test_ps(id);
create index i2_first_table on dbo.first_table(x) on test_ps(id);

insert dbo.first_table values(1, 'one');
insert dbo.first_table values(100, 'one hundred');

create table dbo.second_table(id int not null, x varchar(20)) on test_ps(id);
create index i1_second_table on dbo.second_table(x) on test_ps(id);
create index i2_second_table on dbo.second_table(id) on test_ps(id);

alter table dbo.first_table switch partition 1 to dbo.second_table partition 1;
alter table dbo.second_table switch partition 1 to dbo.first_table partition 1;
5 июн 13, 12:40    [14393789]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
_ч_
Member

Откуда:
Сообщений: 1427
Гость333,

дровишки от коллеги. тоже проверил, всё ок. Видимо он перепутал с несовпадением column_id в sys.columns. т.к. если они не будут совпадать там, то и в sys.index_columns будет таже петрушка.

Спасибо.
5 июн 13, 15:20    [14395209]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Гость333
спокойно переключаю секции
И нарезаете их тоже спокойно?
6 июн 13, 00:44    [14397498]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Mnior
Гость333
спокойно переключаю секции
И нарезаете их тоже спокойно?
Да.
6 июн 13, 06:32    [14397643]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Гость333
Mnior
И нарезаете их тоже спокойно?
Да.
На не пустых секциях?
Как и merge на не пустых?
6 июн 13, 23:12    [14402784]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Mnior
Гость333
пропущено...
Да.
На не пустых секциях?
Как и merge на не пустых?

Ну да :-)
Если это наводящие вопросы, и вы знаете какую-то особенность, так лучше сразу её покажите :-)

select * from sys.messages where text like 'ALTER TABLE SWITCH statement failed%' and language_id = 1033
Выдаётся несколько десятков возможных ошибок, ищем среди них что-нибудь про индексы, из относящегося к теме я обнаружил только "ALTER TABLE SWITCH statement failed. There is no identical index in source table '%.*ls' for the index '%.*ls' in target table '%.*ls' .".
7 июн 13, 09:35    [14403913]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
mike909
Member

Откуда:
Сообщений: 662
Гость333
Mnior
пропущено...
На не пустых секциях?
Как и merge на не пустых?

Ну да :-)
Если это наводящие вопросы, и вы знаете какую-то особенность, так лучше сразу её покажите :-)

Ну про особенности можно посмотреть здесь Создание секционированной таблицы
К указанному способу провести merge, могу добавить более удачный, imho, алгоритм:
Переключать секции целевой таблицы, которые необходимо слить в одну, в секционированную таблицу, где и функция и схема секционирования создается на время проведения операции merge и охватывает только сливаемые секции.
После переключения - drop всех индексов, кроме кластерного; merge в обоих таблицах; создание всех индексов и switch обратно.
И не забыть удалить stage таблицу ее схему секционирования и функцию.
7 июн 13, 10:37    [14404370]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Гость333
Member

Откуда:
Сообщений: 3683
mike909
Гость333
пропущено...

Ну да :-)
Если это наводящие вопросы, и вы знаете какую-то особенность, так лучше сразу её покажите :-)

Ну про особенности можно посмотреть здесь Создание секционированной таблицы

Понятно, что вопросы слияния-разделения секций, содержащих "100500 миллионов" записей, нужно тщательно планировать заранее и продумывать на несколько шагов вперёд. Но тут вроде другой вопрос был: влияет ли последовательность создания индексов (или их index_id) на возможность переключения секций? :)
7 июн 13, 12:25    [14405480]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Гость333
Но тут вроде другой вопрос был: влияет ли последовательность создания индексов (или их index_id) на возможность переключения секций? :)
Согласено, что хоть это работает, НО:
Гость333
Понятно, что вопросы слияния-разделения секций, содержащих "100500 миллионов" записей, нужно тщательно планировать заранее и продумывать на несколько шагов вперёд.
Смысл секционирования сильно падает.
Ведь одно из применений секционирования это именно "мгновенные" операции с кусками данных.
Секционирование без Split/Merge не так уж распространён, хотя на новых версиях можно и прохалявить - лимит то увеличен.
Соответственно, если индексы "выровнены" (официальное название), то это делается "мгновенно", а если нет то надо практически пересоздать эти индексы, для указанного диапазона.
Секционирование ради уменьшения SEEK мало-практично.

Согласен, что по идее часто делается концевой Merge для архивных данных, и далее чуть ли не в другую файловую группу, тогда уже монописуально - делается ли переиндексация или нет. Всё равно доступ к этой части таблицы "защищено" условиями запроса.
Эти тонкости организации процесса уже к mike909 и другим гуру.
У меня же чаще удавалось делать секционирование по ключу.
7 июн 13, 13:57    [14406203]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Mnior
Соответственно, если индексы "выровнены" (официальное название), то это делается "мгновенно", а если нет то надо практически пересоздать эти индексы, для указанного диапазона.

Что "это"? Split/Merge? Для заполненных секций — отнюдь не мгновенно, ибо нет быстрых способов разделения/слияния Б-деревьев (не говоря о том, что данные в процессе могут перемещаться в другую ФГ). Операция split при рассмотрении в профайлере — не что иное, как delete из старой секции по диапазону значений + insert в новую секцию. Операция merge — это insert в одну из секций. Невыровненные индексы добавляют операции index delete и table spool.

Если же "мгновенное это" — это переключение секций, то при наличии невыровненных индексов переключить секции вообще нельзя. Нужно эти индексы удалять и потом перестраивать целиком, а не "для указанного диапазона".
7 июн 13, 18:47    [14407971]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Ок. Возможно я перепутал что есть "выровненные индексы". Я имел ввиду лишь те, у которых первая колонка - ключ секционирования.
Гость333
ибо нет быстрых способов разделения/слияния Б-деревьев
Вы издеваетесь?
Ок, не рассматриваем разные ФГ. Всё что нужно так это:
Split - разбить N страниц на всю высоту дерева на две. Прописать номер секции.
Merge - соединить две верхние страницы (возможен каскад). Прописать номер секции.
Сами данные сортировать не нужно ибо итак сортировано по ключу секции.
+
Допустим ключ секционирования не первый в колонке. Тогда данные всё равно не перемешиваются, уже сортированны. Нужно просто их разделить на два куска, т.е. выпиливая меньшие данные в освобождающиеся страницы, уплотнять оставшиеся.
Или наоборот - потоковый MERGE.

В случае наличия в кластерном ключе. Аналогично - ибо содержится в каждом индексе.

В случае отсутствия где либо - не судьба. Сканируй снова таблу и сортируй. Но это уже итак изврат.

Гость333
Операция split при рассмотрении в профайлере — не что иное, как delete из старой секции по диапазону значений + insert в новую секцию.
Пруф в студию.
Неужели опять писать очевидный баг недоработку?!

Т.е. вы хотите сказать, что считается, что вариант (как у меня) что все индексы таблицы содержат первой колонкой ключ секционирования - это огромная редкость. И поэтому не стоит парится и всегда используется принцип чайничка?
+
Да, беследная пропажа Джима Грея подтвердила, что дальше в истории IT (и в БД в частности) наступает темное бессмысленное средневековье, где правит алчность и тупость.
Не зря в CREATE PARTITION FUNCTION стоит LEFT по умолчанию - чтобы предупредить - MS старается что бы все зае$@%ись.

Это же элементарно, что система должна быть спроектирована для максимальной эффективности. И было всё подготовлено. Ан нет, пришли Орки индусы, и "восстановили баланс сил".

Но, лять, почему при наличии ONLINE индексировании и мгновенного добавления DEFAULT, типа нельзя сделать ONLINE SPLIT/MERGE?! Данные же не куда не деваются и не меняются, этой операцией.
Голосуйте.
#спасибомикрософтзаэто
8 июн 13, 00:09    [14408854]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Mnior
Гость333
ибо нет быстрых способов разделения/слияния Б-деревьев
Вы издеваетесь?
Ок, не рассматриваем разные ФГ. Всё что нужно так это:
Split - разбить N страниц на всю высоту дерева на две. Прописать номер секции.
Merge - соединить две верхние страницы (возможен каскад). Прописать номер секции.
Сами данные сортировать не нужно ибо итак сортировано по ключу секции.

Дерево индекса при таком кавалеристском наскоке, в общем случае, разбалансируется. То есть перестанет быть Б+ деревом ("просто" Б-дерево — это несколько другое, да). Поэтому нет, не годится такой способ.

Mnior
Сами данные сортировать не нужно ибо итак сортировано по ключу секции.

Это очень существенный плюс для split/merge, согласен.
10 июн 13, 08:26    [14412886]     Ответить | Цитировать Сообщить модератору
 Re: текст запроса из sys.indexes  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Mnior
Гость333
Операция split при рассмотрении в профайлере — не что иное, как delete из старой секции по диапазону значений + insert в новую секцию.
Пруф в студию.

Я там упоминал table spool, выяснилось, что он добавляется в план выполнения для случая секционированной таблицы-кучи.
Подготовка данных:
+
use tempdb;

/*
drop table dbo.test_1, dbo.test_2, dbo.test_3, dbo.test_4;
drop partition scheme test_ps;
drop partition function test_pf;
*/

create partition function test_pf(int) as range left for values(0);
create partition scheme test_ps as partition test_pf all to ([primary]);

create table dbo.test_1(id int primary key) on test_ps(id);

with numbers(n) as
(  select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all
   select 5 union all select 6 union all select 7 union all select 8 union all select 9
)
insert dbo.test_1(id)
select n1.n + 10 * (n2.n + 10 * (n3.n + 10 * n4.n)) + 1
from numbers n1 cross join numbers n2 cross join numbers n3 cross join numbers n4;

create table dbo.test_2(id int identity primary key, a int, b varchar(30), c varchar(30)) on test_ps(id);
create index i_test_2_b on dbo.test_2(b) on test_ps(id);
create index i_test_2_c on dbo.test_2(c) on [primary];

with numbers(n) as
(  select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all
   select 5 union all select 6 union all select 7 union all select 8 union all select 9
)
insert dbo.test_2(a, b, c)
select n1.n + 10 * (n2.n + 10 * (n3.n + 10 * n4.n)) + 1,
       'String ' + cast(n1.n + 10 * (n2.n + 10 * (n3.n + 10 * n4.n)) + 1 as varchar(30)),
       'String ' + cast(n1.n + 10 * (n2.n + 10 * (n3.n + 10 * n4.n)) + 1 as varchar(30))
from numbers n1 cross join numbers n2 cross join numbers n3 cross join numbers n4;

create table dbo.test_3(id int, a varchar(30) default 'test_3') on test_ps(id);

with numbers(n) as
(  select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all
   select 5 union all select 6 union all select 7 union all select 8 union all select 9
)
insert dbo.test_3(id)
select n1.n + 10 * (n2.n + 10 * (n3.n + 10 * n4.n)) + 1
from numbers n1 cross join numbers n2 cross join numbers n3 cross join numbers n4;

create table dbo.test_4(id int identity, a int, b varchar(30), c varchar(30)) on test_ps(id);
create index i_test_4_b on dbo.test_4(b) on test_ps(id);
create index i_test_4_c on dbo.test_4(c) on [primary];

with numbers(n) as
(  select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all
   select 5 union all select 6 union all select 7 union all select 8 union all select 9
)
insert dbo.test_4(a, b, c)
select n1.n + 10 * (n2.n + 10 * (n3.n + 10 * n4.n)) + 1,
       'String ' + cast(n1.n + 10 * (n2.n + 10 * (n3.n + 10 * n4.n)) + 1 as varchar(30)),
       'String ' + cast(n1.n + 10 * (n2.n + 10 * (n3.n + 10 * n4.n)) + 1 as varchar(30))
from numbers n1 cross join numbers n2 cross join numbers n3 cross join numbers n4;

В итоге получили:
  • test_1 — секционированная таблица с кластерным индексом и одним некластерным выровненным индексом;
  • test_2 — секционированная таблица с кластерным индексом, одним некластерным выровненным индексом и одним некластерным невыровненным индексом;
  • test_3 — секционированная таблица-куча без индексов;
  • test_4 — секционированная таблица-куча с одним некластерным выровненным индексом и одним некластерным невыровненным индексом

    Дальше открываем новое окно SSMS, в нём будем делать split и merge. Также открываем профайлер, в нём отбираем события: Performance -> Showplan XML Statistics Profile, TSQL -> SQL:StmtStarting и TSQL -> SQL:StmtCompleted. Также ставим фильтр по SPID, равный SPID'у окна SSMS.

    Выполняем команду
    alter partition function test_pf() split range (5000)
    

    Появилось 4 плана выполнения, я собрал их в одну картинку:

    К сообщению приложен файл. Размер - 96Kb
  • 10 июн 13, 09:37    [14413126]     Ответить | Цитировать Сообщить модератору
     Re: текст запроса из sys.indexes  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Теперь смерджим разделённые секции обратно:
    alter partition function test_pf() merge range (5000)
    

    Планы выполнения:

    К сообщению приложен файл. Размер - 82Kb
    10 июн 13, 09:42    [14413165]     Ответить | Цитировать Сообщить модератору
     Re: текст запроса из sys.indexes  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Гость333
    В итоге получили:
  • test_1 — секционированная таблица с кластерным индексом и одним некластерным выровненным индексом;

  • fixed
    10 июн 13, 09:46    [14413198]     Ответить | Цитировать Сообщить модератору
     Re: текст запроса из sys.indexes  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6723
    Гость333
    Дерево индекса при таком кавалеристском наскоке, в общем случае, разбалансируется.
    1. C чего бы вы так уверены? Вероятность есть, но не 100%. Дерево (как и всё) на основано на страницах, и их заполненность вариативна (50-100 %%). Балансировка же средняя, а не абсолютная.
    2. И что? Это же не перелопачивать данные. При любой вставке/удалении строки может быть разбалансировка. Это нормальный постоянный фоновый процесс.
    На локировку это может никак и не влиять (тут познания деталей не хватает). А вот в реалии данная фигня локирует, будь она не ладна, но смысла я в этом не вижу.
    Скуль слабоват на механизмы (да, да) и слаб на повторности их использования. Индусы.
    Mnior
    Прописать номер секции.
    Тут конечно тоже загвоздка. Но как я понимаю это прописано не на самих страницах, а в мапах страниц.
    Гость333
    я собрал их в одну картинку:

    +100500 в карму. Спасибо.
    Только вот чтоб добить надо показать, что в тех логических операторах "index delete/insert" не стоит приписка аля "fast partition switch", что может подразумевать совершенно другие физические действия.
    Если вам не трудно конечно.
    10 июн 13, 15:48    [14415940]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить