Ms sql кластерный индекс: 14 вопросов об индексах в SQL Server, которые вы стеснялись задать / Хабр

Оптимизация SQL запросов для MS SQL Server с помощью индексов

Я тут пообещал поделиться опытом оптимизации SQL запросов. Мне достаточно много приходилось работать над оптимизацией и на работе не раз просили рассказать, как я оптимизирую запросы. Тут нет какого-то секрета и, хотя я пытался поделиться, это все же такой процесс, в котором мастерство приходит с опытом.

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

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

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

Когда я узнаю о том, что на сервере есть медленно выполняющийся запрос, первое на что я смотрю – статистику io. Это такое слабое звено, позволяет быстро определить легкие проблемные места SQL запросов.

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

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

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

select * 
from Member
where WSEmail = '[email protected]'

У меня в тестовой базе данных почти 200 тысяч записей и поиск по ней происходит мгновенно. Если посмотреть на время выполнения, то будет ноль секунд, что очень хорошо. Но давайте включим отображение статистики, и посмотрим на нее. Я всегда включаю статистику io и время time:

set statistics io on
set statistics time on 

Теперь после выполнения SQL запроса в SQL Management Studio внизу окна будет появляться не только результат, но и на закладке Messages будет показана статистика выполнения:

В моем случае статистика выглядела так:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'Member'. Scan count 1, logical reads 174261, 
   physical reads 0, read-ahead reads 0, lob logical reads 0, 
   lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 187 ms.

Первые четыре строчки – это время компиляции запроса и для такого простого особо проблем не должно возникнуть, а я уже выполнял запрос, поэтому и 0 секунд.

После этого идет статистика выполнения и тут нужно смотреть на количество сканирований и количество чтений (logical reads, physical reads и др). У нас сейчас запрос простой, который решается одним сканированием. Судя по количеству чтений, сканировалась абсолютно вся таблица.

Казалось бы, да и черт с ним, что сканируется вся таблица, если запрос выполняется так быстро – ноль секунд. И если запрос выполняется только один раз, то можно и забыть и забить. Но если сразу тысяча человек будет выполнять этот запрос и искать по разной фамилии? Даже при такой статистике нагрузка на базу данных будет серьезная, а если в базе будет миллион записей, то сервер может серьезно затормозить.

Для оптимизации нужно создать индекс, который ускоряет поиск:

create index IX_Member_WSEmail on Member (
  WSEmail
)

Более подробно о создании индексов можно почитать здесь: Индексы в MS SQL Server и еще немного интересной информации о индексах здесь: опции индексов.

Здесь я только скажу, что при создании индексов на таблицу, где много данных и с большим количеством выполняемых запросов может стать проблемой. Создание индекса по умолчанию потребует блокировки, что может стать препятствием. Индекс может не создаваться, потому что данные заблокированы или сайт может лечь, если индекс будет долго создаваться. Чтобы этого не произошло, нужно добавлять опцию: (online = on)

create index IX_Member_WSEmail on Member (
  WSEmail
) with (online = on)

Снова выполняем запрос и смотрим на статистику:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'Member'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

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

Попробуем взглянуть на план выполнения – графическое представление того, как сервер реально выполнял запрос. Для этого включаем опцию отображения плана – в меню Query выбираем Include Actual Execution Plan (или нажимаем Ctrl+M). Если теперь выполнить запрос, то появится еще одна вкладка – Execution Plan:

Здесь у нас две ветки и читать их нужно справа на лево. Самый правый блок – это то, с чего началось выполнение – Index Seak по индексу IX_Member_WSEmail. Наш простой запрос ищет данные по колонке WSEmail и эта колонка есть в индексе IX_Member_WSEmail, поэтому имеет смысл использовать его. И как мы уже увидели после создания индекса, результат как говориться на io. В индексе находятся индексируемые колонки и первичный ключ. Это все, что узнает сервер, когда находит строку по индексу. Но наш запрос выводит совершенно все колонки и чтобы найти оставшиеся данные, серверу приходится по первичному ключу находить их. Этот процесс быстрый – Key Lookup, потому что это первичный ключ, но он все же отнимает немного времени. И скоро мы увидим сколько. Получается, что серверу необходимо выполнить как бы две операции – поиск по индексу основного ключа, а потом по этому ключу найти данные колонок. А если выводить на экран только колонки WSEmail и первичный ключ MemberID? Эти данные уже есть индексе и второй Key Lookup не понадобиться. Посмотрим, как это будет выглядеть в статистике:

select WSEmail, MemberID
from Member
where WSEmail = '[email protected]'

Статистика падает с 7 чтений до 3:

(1 row(s) affected)
Table 'Member'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

А план выполнения начинает выглядеть идеально просто:

Допустим, нам необходимо вывести на экран имя человека, в моем случае это колонка WSFirstName:

select WSEmail, MemberID, WSFirstName
from Member
where WSEmail = '[email protected]'

Теперь запросу нужно будет снова делать два поиска – чтобы найти первичный ключи, а потом по нему найти реальную строку, чтобы выцепить имя. И это в принципе не страшно, потому что поиск по первичному ключу занял всего 4 операции чтения, но что, если выводиться 1000 строк? Тогда уже будет 4000 операций. А если запрос у нас не такой простой и в нем потом еще есть left join на какую-то другую таблицу с именами, где, по имени храниться судьба человека (такой гороскоп по имени).

Можно создать индекс, который будет индексировать по email и по имени:

create index IX_Member_WSEmail on Member (
  WSEmail, WSFirstName
)

И хотя запрос фильтрует данные только по e-mail, этот индекс все же будет работать и позволит нам быстро найти данные, и в индексе будет уже имя и поэтому второй поиск уже не нужен будет. Круто, но не совсем эффективно. Имя меняется не часто и если мы по нему реально не ищем, то по нему индексировать смысла нет, но есть возможность сказать серверу, чтобы он хранил вместе с индексом еще и колонку WSFirstName, для этого есть такая фишка, как include:

create index IX_Member_WSEmail on Member (
  WSEmail
) include(WSFirstName)

Теперь данные индексируются только по колонке WSEmail, что нам и нужно, но вместе с индексом храниться не только первичный ключ, но и имя.

Конечно же, теперь при обновлении данных в колонке WSFirstName придется обновлять и индекс, но так как мы не индексируем по этой колонке, то к перестроению данных это не приведет. Такая операция будет очень быстрой.

Еще один пример, который может выиграть от такого индекса:

select *
from Member
where WSEmail like 'noreply%'
	and WSFirstName = 'Михаил'

Мы выводим все колонки и от второго поиска по первичному ключу всех данных не убежать. Все колонки включать в индекс смысла нет, потому что это превратить его практически в первичный, просто не кластерный. Но.. Когда сервер отфильтрует данные по WSEmail по первому индексу и найдет допустим 1000 строк, он может тут же сократить эти данные и проверить имя и результат сократиться до (допустим) 100 строк. То есть Key Lookup может выполняться только 100 раз. Если колонка WSFirstName не включена в индекс, то эту операцию придется уже делать 1000 раз.

Чтобы индексы работали наиболее эффективно, тип данных значения и колонки должно совпадать. Как видите, в моем случае я просто передаю строку в одинарной кавычки, как varchar, потому что колонка имеет тип именно varchar. Если попробовать передать nvarchar:

select *
from Member
where WSEmail = N'[email protected]'

Теперь строка e-mail адреса передается в качестве nvarchar и это серьезно ударит по производительности. Изменился только тип строки, которую мы сравниваем, а посмотрите как обрушилась статистика:

(1 row(s) affected)
Table 'Member'. Scan count 1, logical reads 684, physical reads 0, read-ahead reads 408, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Или вот такой пример, так обычно делают многие движки, когда выполняют запросы и примерно так будет выполнять запросы популярный Dapper или даже .NET фреймворк:

declare @email nvarchar(200) = N'[email protected]'

select WSEmail, MemberID
from Member
where WSEmail = @email

Когда вы в .NET выполняете запрос, то создается переменная и она передается запросу.

Когда я работал над сайтом регистрации продуктов для Sony, то допустил такую ошибку. На главной странице сайте есть автокомплитер, где пользователь может ввести код товара. Пока пользователь вводит, на заднем плане происходит поиск и когда я запустил этот сайт, то он нереально затормозил, хотя этот сайт не такой уж и популярный и по посещаемости самый слабый из всех, что я делал для Sony. Начали исследовать, а оказалось, что для этого проекта я перешел на Dapper, который по умолчанию все переменные делал nvarchar, а в базе данных у нас все было просто varchar (сайт только для США). В результате, даже небольшой нагрузки на сайт хватало для серьезного падения производительности. Пришлось хакать Dapper, чтобы он не создавал переменные Unicode, а делал их простыми varchar

Если тип колонки и искомого значения совпадают, то будет использоваться Index Seek. Если не совпадают, то Index Scan – сканирование по индексу. Что используется для поиска можно увидеть в Execution Plan

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

Если ваш фреймворк косячит и передает неверно параметры, а у вас нет доступа к исходникам, чтобы исправить, то можно использовать вот такой финт ушами:

declare @email varchar(200) = '[email protected]'

select WSEmail, MemberID
from Member
where WSEmail = cast(@email as varchar)

Здесь я с помощью cast привожу email переменную к правильному типу, чтобы он совпадал с типом данных колонки.

На этом наверно остановимся, для первого вводного курса этого должно быть достаточно. Если эта статья оказались интересна, рекомендую все же посмотреть еще и видео. Лайкни его, мне приятно видеть, если моя работа полезна и интересна другим. Подпишись на канал. Если будет востребованность, будет смысл продолжать эту тему.

Если будет продолжение, то со статистикой мы еще столкнемся не раз.

Поделитесь с друзьями

Внимание!!! Если ты копируешь эту статью себе на сайт, то оставляй ссылку непосредственно на эту страницу. Спасибо за понимание

SQL. Оптимизация запросов SQL. MS SQL Медленно работают запросы

В данном руководстве мы изложили свой опыт по оптимизации запросов SQL. 

Оптимизация SQL Server

Рекомендации при разработке приложения:

• Для обработки данных используйте хранимые SQL процедуры. Избегайте действий над данными в презентационной и бизнес — логике.
• Не храните данные (из базы данных) в бизнес — логике. Храните данные в базе данных
• Не создавайте сложные и/или глубокие объектные иерархии. Создание и использование сложных классов или большого количества объектов, используемых для моделирования сложных правил или ситуаций поглощает много ресурсов и снижает производительность и гибкость приложения (прим переводчика: по-моему, объектные иерархии как раз предназначены для повышения гибкости приложения). Так происходит из-за того, что выделение и освобождение памяти при работе с объектами является ресурсоемким процессом.
• Пересмотрите проект так, чтобы использовать кеширование соединения с БД и кеширование объектов с помощью Microsoft Transaction Server (MTS). MTS кеширует и соединение с БД, и объекты, что сильно повышает общую производительность и масштабируемость приложения.
• Если ваше приложение использует SQL запросы, которые по своей природе выполняются долго, постарайтесь сделать так, чтобы ваше приложение выполняло такие запросы в асинхронном режиме. Запросы не должны дожидаться окончания окончания предыдущих запросов, чтобы начать выполняться самим. Один из способов обеспечить это в вашем n — звенном приложении — использование Microsoft Message Queue Server (MSMQ)
• разбивайте сложные таблицы на несколько, помните, чем больше в вашей таблице столбцов и тяжелых типов (nvarchar(max)), тем тяжелее по ней проход. Если некоторые данные не всегда используются в select с ней, выносите их отдельно в таблицу и связывайте через FK

Как оптимизировать SQL запросы

Используйте OLE DB, для обращений к SQL серверу.

Инкапсулируйте ваш DML (Data Manipulation Language) в хранимых процедурах (с компиляцией).

Как оптимизировать ваш код Transact-SQL.

Выберите правильные типы данных.

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

Если текстовые данные в столбце имеют разную длину, используйте тип данных VARCHAR вместо CHAR.

Не используйте NVARCHAR или NCHAR типы данных, если Вы не должны сохранить 16-разрядные символьные данные (UNICODE). Они требуют в два раза больше места, чем CHAR и VARCHAR, что повышает расходы времени на ввод-вывод.

ВНИМАНИЕ! Обычно мы используем везде NVARCHAR. Использование других строковых типов обязательно согласовывайте с техлидом на проекте. 

Если Вы должны хранить большие строки данных и их длина меньше чем 8,000 символов, используют тип данных VARCHAR вместо TEXT. Текстовые поля требуют больше ресурсов для обработки и снижают производительность.

Любое поле, в котором должны быть только отличные от нуля значения, нужно без тени сомнения объявлять как NOT NULL

Для любого поля, которое должно содержать уникальные значения, стоит указать модификатор UNIQUE

Используйте триггеры c осторожностью.

Не читайте больше данных, чем надо.

Если ваше приложение позволяет пользователям выполнять запросы, но вы не можете отсечь лишние сотни и тысячи возвращаемых строк, используйте оператор TOP внутри инструкции SELECT. 

Не возвращайте клиенту большее количество столбцов или строк, чем действительно необходимо (Не используй * в Select). 

Избегайте использования курсоров.

По возможности выбирайте быстрый forward-only курсор

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

Когда Вы закончили использовать курсор не только ЗАКРОЙТЕ (CLOSE) его, но и ОСВОБОДИТЕ (DEALLOCATE).

Корректно используйте JOIN.

Если Вы имеете две или более таблиц, которые часто объединяются вместе, тогда столбцы, используемые для объединений должны иметь соответствующий индекс (Важно!).

Для лучшей производительности, столбцы, используемые в объединениях должны иметь одинаковые типы данных. И если возможно, это должны быть числовые типы данных, вместо символьных типов.

Избегайте объединять таблицы по столбцам с малым числом уникальных значений. Если столбцы, используемые при объединениях, имеют мало уникальных значений, то SQL сервер будет просматривать всю таблицу, даже если по данному столбцу существует индекс. Для наилучшей производительности объединение таблиц должно производится по столбцам с уникальными индексами.

Если Вы должны регулярно объединять четыре или более таблиц, для получения recordset’а, попробуйте денормализовать таблицы так, чтобы число таблиц, участвующих в объединении уменьшилось. Часто, при добавлении одного или двух столбцов из одной таблицы в другую, объединения могут быть уменьшены.

Тип JOIN используйте только тот, который вернет вам НЕОБХОДИМЫЕ данные без каких-либо дублей или лишней информации.

Инкапсулируйте ваш код в хранимых процедурах

Когда хранимая процедура выполняется в первый раз (и у нее не определена опция WITH RECOMPILE), она оптимизируется, для нее создается план выполнения запроса, который кешируется SQL сервером. Если та же самая хранимая процедура вызывается снова, она будет использовать кешированный план выполнения запроса, что экономит время и увеличивает производительность. 

Всегда включайте в ваши хранимые процедуры инструкцию «SET NOCOUNT ON». Если Вы не включите эту инструкцию, тогда каждый раз при выполнении запроса SQL сервер пошлет ответ клиенту, указывающему число строк, на которые воздействует запрос.

Чтобы избежать deadlocks, пытайтесь разрабатывать ваше приложение с учетом следующих рекомендаций:

• Всегда получайте доступ к объектам в одном и том же порядке.
• Не допускайте пользовательского ввода во время транзакций. Получите все необходимые данные перед началом транзакции.
• Старайтесь делать транзакции короткими и заключайте их в один пакет (batch)
• Старайтесь использовать максимально низкий уровень изоляции для пользовательского соединения, которое работает с транзакцией.

Другие правила оптимизации SQL

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

1. Ситуация с данным оператором прямо противоположна ситуации с AND. Условия должны располагаться в порядке убывания вероятности истинности.

NOT. Эту операцию всегда следует приводить к более «читабельному» виду (в разумных пределах, конечно). Так, запрос»…WHERE NOT (column1 > 5)» преобразуется в «…WHERE column1 <= 5». 

1. Запрос «… WHERE column1 = 5 OR column1 = 6» медленнее чем «…WHERE column1 IN (5, 6)». Операция IN работает гораздо быстрее, чем серия OR. 

LIKE. Эту операцию следует использовать только при крайней необходимости, потому что лучше и быстрее использовать поиск, основанный на full-text индексах. 

Сортировка

Самой ресурсоемкой сортировкой является сортировка строк.

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

Группирование

Первое, что следует помнить, — нужно использовать как можно меньше колонок для группировки.

По возможности лучше использовать Where вместо Having

Если требуется группирование, но без использования агрегатных функций (COUNT(), MIN(), MAX и т.д.), разумно использовать DISTINCT.

Ограничить использование DISTINCT.  Эта команда исключает повторяющиеся строки в результате. Команда требует повышенного времени обработки. Лучше всего комбинировать с LIMIT.

Ограничить использование SELECT для постоянно изменяющихся таблиц.

Хранение изображений в БД нежелательно. 

Как выбирать индексы для оптимальной производительности БД.

Бывает ли слишком много индексов?
Да. Проблема с лишними индексами состоит в том, что SQL сервер должен изменять их при любых изменениях таблицы (INSERT, UPDATE, DELETE).

За основу возьмите правило не добавлять индексы автоматически, просто потому, что кажется, что они должны быть. Иногда сложно понять, как часто будет редактироваться таблица и нужен тут индекс или нет, лучшим решением будет подождать и собрать статистику и уже потом решать, стоит ли добавлять индекс или нет.

Советы по созданию кластерных индексов

• Первичный ключ не всегда должен быть кластерным индексом. Если Вы создаете первичный ключ, тогда SQL сервер автоматически делает первичный ключ кластерным индексом. Первичный ключ должен быть кластерным индексом, только если он отвечает одной из нижеследующих рекомендаций.
• Кластерные индексы идеальны для запросов, где есть выбор по диапазону или вы нуждаетесь в сортированных результатах. Так происходит потому, что данные в кластерном индексе физически отсортированы по какому-то столбцу. Запросы, получающие выгоду от кластерных индексов, обычно включают в себя операторы BETWEEN, <, >, GROUP BY, ORDER BY, и агрегативные операторы типа MAX, MIN, и COUNT.
• Кластерные индексы хороши для запросов, которые ищут запись с уникальным значением (типа номера служащего) и когда Вы должны вернуть большую часть данных из записи или всю запись. Так происходит потому, что запрос покрывается индексом.
• Кластерные индексы хороши для запросов, которые обращаются к столбцам с ограниченным числом значений, например столбцы, содержащие данные о странах или штатах. Но если данные столбца мало отличаются, например, значения типа «да/нет», «мужчина/женщина», то такие столбцы вообще не должны индексироваться.
• Кластерные индексы хороши для запросов, которые используют операторы GROUP BY или JOIN.
• Кластерные индексы хороши для запросов, которые возвращают много записей, потому что данные находятся в индексе, и нет необходимости искать их где-то еще.
• Избегайте помещать кластерный индекс в столбцы, в которых содержатся постоянно возрастающие величины, например, даты, подверженные частым вставкам в таблицу (INSERT). Так как данные в кластерном индексе должны быть отсортированы, кластерный индекс на инкрементирующемся столбце вынуждает новые данные быть вставленным в ту же самую страницу в таблице, что создает «горячую зону в таблице» и приводит к большому объему дискового ввода-вывода. Постарайтесь найти другой столбец, который мог бы стать кластерным индексом.

Советы по выбору некластерных индексов

• Некластерные индексы лучше подходят для запросов, которые возвращают немного записей (включая только одну запись) и где индекс имеет хорошую селективность (более чем 95 %).
• Если столбец в таблице не содержит по крайней мере 95% уникальных значений, тогда очень вероятно, что Оптимизатор Запроса SQL сервера не будет использовать некластерный индекс, основанный на этом столбце. Поэтому не добавляйте некластерные индексы к столбцам, которые не имеют хотя бы 95% уникальных записей. Например, столбец с «Да» или «Нет» не имеет 95% уникальных записей.
• Постарайтесь сделать ваши индексы как можно меньшего размера (особенно для многостолбцовых индексов). Это уменьшает размер индекса и уменьшает число чтений, необходимых, чтобы прочитать индекс, что увеличивает производительность.
• Если возможно, создавайте индексы на столбцах, которые имеют целочисленные значения вместо символов. Целочисленные значения имеют меньше потерь производительности, чем символьные значения.
• Если Вы знаете, что ваше приложение будет выполнять один и тот же запрос много раз на той же самой таблице, рассмотрите создание покрывающего индекса на таблице. Покрывающий индекс включает все столбцы, упомянутые в запросе. Из-за этого индекс содержит все данные, которые Вы ищете, и SQL сервер не должен искать фактические данные в таблице, что сокращает логический и/или физический ввод — вывод. С другой стороны, если индекс становится слишком большим (слишком много столбцов), это может увеличить объем ввода — вывода и ухудшить производительность.
• Индекс полезен для запроса только в том случае, если оператор WHERE запроса соответствует столбцу (столбцам), которые являются крайними левыми в индексе. Так, если Вы создаете составной индекс, типа «City, State», тогда запрос » WHERE City = ‘Хьюстон’ » будет использовать индекс, но запрос » WHERE State = ‘TX’ » не будет использовать индекс.
• Любая операция над полем в предикате поиска, которое лежит под индексом, сводит на нет его использование. where isnull(field,’’) = ‘’ здесь индекс не используется, where field = ‘’ and field is not null — здесь используется.

Советы по использованию временных таблиц и табличных переменных

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

• Временная таблица храниться физически в tempdb, табличная переменная хранится в памяти SQL
• SQL может сам решить сохранить табличную переменную физически, если там будет много данных, это потеря ресурсов, учтите это
• Временная таблица для большого объема данных (полноценная выборка), табличная переменная для малого (справочники или набор ID для чего-то)
• Временная таблица доступна из любой процедуры SQL, табличная переменная только в рамках запроса. Не забывайте очищать временные таблицы после их использования

ВЛИЯНИЕ ИНДЕКСОВ НА ПРОИЗВОДИТЕЛЬНОСТЬ 1С:ПРЕДПРИЯТИЕ 8 | Gilev.ru

— Ну у вас и запросы! — сказала база данных и повисла…

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

Что такое индекс?

Подобно содержанию в книге, индекс в базе данных позволяет быстро искать конкретные сведения в таблице.

Сначала поговорим про индексы в MS SQL Server.
Индексы представляют собой структуру, позволяющую выполнять ускоренный доступ к строкам таблицы на основе значений одного или более ее столбцов.
Индекс содержит ключи, построенные из одного или нескольких столбцов таблицы или представления, и указатели, которые сопоставляются с местом хранения заданных данных.
Индексы сокращают объем данных, которые необходимо считать, чтобы возвратить результирующий набор.

Хотя индекс и связан с конкретным столбцом (или столбцами) таблицы, все же он является самостоятельным объектом базы данных.

Просто объекта «Индекс» в платформе 1С:Предприятие 8 нет.

Индексы таблиц в базе данных 1С:Предприятие создаются неявным образом при создании объектов конфигурации, а также при тех или иных настройках объектов конфигурации.

• Неявным образом индексы создаются с учетом типов полей ключа данных — набора полей, однозначно определяющих данные. Для объектных типов данных (Справочник, Документ, ПланСчетов и др.) — это «Ссылка»; для регистров, подчиненных регистратору (РегистрНакопления, РегистрБухгалтерии, РегистрСведений, подчиненный регистратору и др.) — «Регистратор»; для регистров сведений, неподчиненных регистратору — поля, соответствующие изменениям, входящим в основной отбор регистра; для констант — идентификатор объекта метаданных Константы.
• индексируются данные в «соответствии»

Явным способом включением свойства «Индексировать» реквизитов и измерений с значение «Индексировать» и «Индексировать с доп. Упорядочиванием». Вариант ««Индексировать с доп. Упорядочиванием»» включает обычно колонку «код» или «наименование» в индекс.

Еще одним явным способом можно считать добавление объекта метаданных в объект метаданных «критерий отбора».

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

ВЫБРАТЬ
Код,
Наименование
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ Справочник.Номенклатура
ИНДЕКСИРОВАТЬ ПО Код

В любом случае, надо понимать, что говоря об индексах, мы фактически подразумеваем индексы СУБД, которая используется для 1С:Предприятие. Исключению составляют объекты типа Таблица значений, когда индексы находятся в RAM (оперативной памяти).

Физическая сущность индексов в MS SQL Server.

Физически данные хранятся на 8Кб страницах. Сразу после создания, пока таблица не имеет индексов, таблица выглядит как куча (heap) данных. Записи не имеют определенного порядка хранения.
Когда вы хотите получить доступ к данным, SQL Server будет производить сканирование таблицы (table scan). SQL Server сканирует всю таблицу, что бы найти искомые записи.
Отсюда становятся понятными базовые функции индексов:
— увеличение скорости доступа к данным,
— поддержка уникальности данных.

Несмотря на достоинства, индексы так же имеют и ряд недостатков. Первый из них – индексы занимают дополнительное место на диске и в оперативной памяти. Каждый раз когда вы создаете индекс, вы сохраняете ключи в порядке убывания или возрастания, которые могут иметь многоуровневую структуру. И чем больше/длиннее ключ, тем больше размер индекса. Второй недостаток – замедляются операции вставки, обновления и удаления записей.
В среде MS SQL Server реализовано несколько типов индексов:

• некластерные индексы;
• кластерные (или кластеризованные) индексы;
• уникальные индексы;
• индексы с включенными столбцами
• индексированные представления
• полнотекстовый
• XML

Некластерный индекс

Некластерные индексы – не перестраивают физическую структуру таблицы, а лишь организуют ссылки на соответствующие строки.
Для идентификации нужной строки в таблице некластерный индекс организует специальные указатели, включающие в себя:

• информацию об идентификационном номере файла, в котором хранится строка;
• идентификационный номер страницы соответствующих данных;
• номер искомой строки на соответствующей странице;
• содержимое столбца.

Некластерных индексов может быть несколько для одной таблицы.

Некластеризованный индекс по таблице, не имеющей кластеризованного индекса

Некластеризованный индекс по таблице, имеющей кластеризованный индекс

Кластерный (кластеризованный) индекс

Принципиальным отличием кластерного индекса от индексов других типов является то, что при его определении в таблице физическое расположение данных перестраивается в соответствии со структурой индекса. Логическая структура таблицы в этом случае представляет собой скорее словарь, чем индекс. Данные в словаре физически упорядочены, например по алфавиту.
Кластерные индексы могут дать существенное увеличение производительности поиска данных даже по сравнению с обычными индексами. Увеличение производительности особенно заметно при работе с последовательными данными. Если в таблице определен некластерный индекс, то сервер должен сначала обратиться к индексу, а затем найти нужную строку в таблице. При использовании кластерных индексов следующая порция данных располагается сразу после найденных ранее данных. Благодаря этому отпадают лишние операции, связанные с обращением к индексу и новым поиском нужной строки в таблице.
Естественно, в таблице может быть определен только один кластерный индекс. Кластерный индекс может включать несколько столбцов.
Необходимо избегать создания кластерного индекса для часто изменяемых столбцов, поскольку сервер должен будет выполнять физическое перемещение всех данных в таблице, чтобы они находились в упорядоченном состоянии, как того требует кластерный индекс. Для интенсивно изменяемых столбцов лучше подходит некластерный индекс.
При создании в таблице первичного ключа (PRIMARY KEY) сервер автоматически создает для него кластерный индекс, если его не существовало ранее или если при определении ключа не был явно указан другой тип индекса.
Когда же в таблице определен еще и некластерный индекс, то его указатель ссылается не на физическое положение строки в базе данных, а на соответствующий элемент кластерного индекса, описывающего эту строку, что позволяет не перестраивать структуру некластерных индексов всякий раз, когда кластерный индекс меняет физический порядок строк в таблице.

Уникальный индекс

Уникальность значений в индексируемом столбце гарантируют уникальные индексы. При их наличии сервер не разрешит вставить новое или изменить существующее значение таким образом, чтобы в результате этой операции в столбце появились два одинаковых значения.
Уникальный индекс является своеобразной надстройкой и может быть реализован как для кластерного, так и для некластерного индекса. В одной таблице может существовать один уникальный кластерный и множество уникальных некластерных индексов.
Уникальные индексы следует определять только тогда, когда это действительно необходимо. Для обеспечения целостности данных в столбце можно определить ограничение целостности UNIQUE или PRIMARY KEY, а не прибегать к уникальным индексам. Их использование только для обеспечения целостности данных является неоправданной тратой пространства в базе данных. Кроме того, на их поддержание тратится и процессорное время.

1С:Предприятие 8 активно использует кластерные уникальные индексы. Это означает, что можно получить ошибку не уникального индекса.

Если не уникальность заключается в датах с нулевыми значениями, то проблема решается созданием базы с параметром смещения равным 2000.
«Рыба» скрипта для определения не уникальных записей:
SELECT COUNT(*) Counter, <перечисление всех полей соответствующего индекса> from <имя таблицы>
GROUP BY <перечисление всех полей соответствующего индекса>
HAVING Counter > 1

Понятие первичного и внешнего ключа

Первичный ключ (primary key) – это набор столбцов таблицы, значения которых уникально определяют строку.

Внешний ключ (foreign key) . Внешним ключом называется поле таблицы, предназначенное для хранения значения первичного ключа другой таблицы с целью организации связи между этими таблицами. Внешний ключ в таблице может ссылаться и на саму эту таблицу. Такие внешние ключи, в основном, используются для хранения древовидной структуры данных в реляционной таблице. СУБД поддерживают автоматический контроль ссылочной целостности на внешних ключах.
1С не использует внешние ключи. Ссылочная целостность обеспечивается логикой приложения.

Ограничения индексов

Индекс может быть создан на основании нескольких полей. В этом случае существует ограничение – длина ключа индекса не должна превышать 900 байтов и не более 16 ключевых столбцов. На практике это означает что при создании индекса, включающего более 16 полей, индекс усекается. Это может оказать влияние на производительность при количестве субконто составного типа более 4х.

В актуальных релизах платформы выполнена оптимизация данного случая и используется хэш по ключу  полей, но это медленней «полноценных» индексов.

Статистика индексов

Microsoft SQL Server  собирает статистику по индексам и полям данных, хранимых в базе. Эта статистика используется оптимизатором запроса SQL Server при выборе оптимального плана исполнения запросов на выборку или обновление данных.

При создании индекса оптимизатор запросов автоматически сохраняет данные статистики о проиндексированых столбцах.

Просмотр статистики — sp_helpstats.

Фрагментация индексов

Чрезмерная фрагментация создает проблемы для больших операций ввода-вывода. Фрагментация не должна превышать 25%. От снижения фрагментации индексов могут выиграть операции сканирования больших диапазонов данных. Для этого рекомендуется выполнять периодическую дефрагментацию индексов. Обратите внимание, что при дефрагментации индексов (по умолчанию) автоматически обновляется статистика.
Смотреть степень фрагментированности индексов можно штатными средствами СУБД или в разрезе объектов метаданных можно например с помощью бесплатного онлайн-сервиса http://www.gilev.ru/sqlsize/

Оптимизация размещения индексов

При объеме таблиц не позволяющем им «разместиться» в оперативной памяти сервера, на первое место выходит скорость дисковой подсистемы (I/O). И здесь можно обратить внимание возможность размещать индексы в отдельных файлах расположенных на разных жестких дисках.

Подробное описание действий http://technet.microsoft.com/ru-ru/library/ms175905.aspx
Использование индекса из другой файловой группы повышает производительность некластерных индексов в связи с параллельностью выполнения процессов ввода/вывода и работы с самим индексом.
Для определения размеров можно использовать выше упомянутую обработку.

Влияние индексов на блокировки

Отсутствие необходимого индекса для запроса означает перебор всех записей таблицы, что в свою очередь приводит к избыточным блокировкам, т.е. блокируются лишние записи. Кроме того, чем дольше выполняется запрос из-за отсутствующих индексов, тем больше время удержания блокировок.
Другая причина блокировок  — малое количество записей в таблицах. В связи с этим SQL Server, при выборе плана выполнения запроса, не использует индексы, а обходит всю таблицу(Table Scan), блокируя целиком. Для того, чтобы избежать подобных блокировок, необходимо увеличить количество записей в таблицах до 1500-2000. В этом случае сканирование таблицы становится долее дорогостоящей операцией и SQL Server начинает использовать индексы. Конечно это можно сделать не всегда, ряд справочников как «Организации», «Склады», «Подразделения» и т.п. обычно имеют мало записей. В этих случаях индексирование не будет улучшать работу.

Эффективность индексов

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

• Запросы, которые указывают «узкие» критерии поиска. Такие запросы должны считывать лишь небольшое число строк, отвечающих определенным критериям.
• Запросы, которые указывают диапазон значений. Эти запросы также должны считывать небольшое количество строк.
• Поиск, который используется в операциях связывания. Колонки, которые часто используются как ключи связывания, прекрасно подходят для индексов.
• Поиск, при котором данные считываются в определенном порядке. Если результирующий набор данных должен быть отсортирован в порядке кластеризованного индекса, то сортировка не нужна, поскольку результирующий набор данных уже заранее отсортирован. Например, если кластеризованный индекс создан по колонкам lastname (фамилия), firstname (имя), а для приложения требуется сортировка по фамилии и затем по имени, то здесь нет необходимости добавлять инструкцию ORDER BY.

Правда при всей полезности индексов, есть одно очень важное НО – индекс должен быть «эффективно используемым» и должен позволять  находить данные с использованием меньшего числа операций ввода-вывода и объема системных ресурсов. И наоборот, неиспользуемые (редко используемые) индексы скорее ухудшают скорость записи данных (поскольку каждая операция, изменяющая данные, должна также обновлять страницы индексов) и создают избыточный объем базы.

Покрывающим (для данного запроса), называется индекс в котором есть все необходимые поля для этого запроса. Например, если индекс создан по колонкам a, b и c, а оператор SELECT запрашивает данные только из этих колонок, то требуется доступ только к индексу.

Для того, что бы определить эффективность индекса, мы можем приблизительно оценить с помощью бесплатного онлайн-сервиса http://www.gilev.ru/querytj/ показывающий «план исполнения запроса» и используемые индексы.

ПРОБЛЕМА НЕУНИКАЛЬНЫХ ИНДЕКСОВ 1С:ПРЕДПРИЯТИЕ 8 | Gilev.ru

О чем статья

В этой статье будет описано, что делать, если при работе с 1С:Предприятие 8.1 Вам встретилось сообщение, содержащее строки:

Cannot insert duplicate key row in object

или

Попытка вставки неуникального значения в уникальный индекс.

Что такое индекс?

Подобно содержанию в книге, индекс в базе данных позволяет быстро искать конкретные сведения в таблице.

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

Хотя индекс и связан с конкретным столбцом (или столбцами) таблицы, все же он является самостоятельным объектом базы данных.

Индексы таблиц в базе данных 1С:Предприятие создаются неявным образом при создании объектов конфигурации, а также при тех или иных настройках объектов конфигурации.

Физическая сущность индексов в MS SQL Server 2005.

Физически данные хранятся на 8Кб страницах. Сразу после создания, пока таблица не имеет индексов, таблица выглядит как куча (heap) данных. Записи не имеют определенного порядка хранения.
Когда вы хотите получить доступ к данным, SQL Server будет производить сканирование таблицы (table scan). SQL Server сканирует всю таблицу, что бы найти искомые записи.
Отсюда становятся понятными базовые функции индексов:
— увеличение скорости доступа к данным,
— поддержка уникальности данных.

Несмотря на достоинства, индексы так же имеют и ряд недостатков. Первый из них – индексы занимают дополнительное место на диске и в оперативной памяти. Каждый раз когда вы создаете индекс, вы сохраняете ключи в порядке убывания или возрастания, которые могут иметь многоуровневую структуру. И чем больше/длиннее ключ, тем больше размер индекса. Второй недостаток – замедляются операции вставки, обновления и удаления записей.
В среде MS SQL Server 2005 реализовано несколько типов индексов:

• некластерные индексы;
• кластерные (или кластеризованные) индексы;
• уникальные индексы;
• индексы с включенными столбцами
• индексированные представления
• полнотекстовый
• XML

Уникальный индекс

Уникальность значений в индексируемом столбце гарантируют уникальные индексы. При их наличии сервер не разрешит вставить новое или изменить существующее значение таким образом, чтобы в результате этой операции в столбце появились два одинаковых значения.
Уникальный индекс является своеобразной надстройкой и может быть реализован как для кластерного, так и для некластерного индекса. В одной таблице может существовать один уникальный кластерный и множество уникальных некластерных индексов.
Уникальные индексы следует определять только тогда, когда это действительно необходимо. Для обеспечения целостности данных в столбце можно определить ограничение целостности UNIQUE или PRIMARY KEY, а не прибегать к уникальным индексам. Их использование только для обеспечения целостности данных является неоправданной тратой пространства в базе данных. Кроме того, на их поддержание тратится и процессорное время.

1С:Предприятие 8.1 начиная с версии 8.1 активно использует кластерные уникальные индексы. Это означает, что при конвертации с 8.0 или переходе с 8.1.7 можно получить ошибку неуникального индекса.

Если неуникальность заключается в датах с нулевыми значениями, то проблема решается созданием базы с параметром смещения равным 2000.

Что делать?

1. Если проблема загрузкой базы данных, то:

1.1. Если Вы делаете загрузку (используйете dt-файл) в базу MS SQL Server, то при создании базы перед загрузкой укажите смещение дат — 2000.

Если уже база создана со смещением 0, то создайте новую с 2000.

1.2. Если есть возможность в файловом варианте работать с базой, то выполните Тестирование и Исправление, а также Конфигурация — Проверка конфигурации — Проверка логической целостности конфигурации + Поиск некорректных ссылок.

1.3. Если нет файлового варианта, попробуйте загрузить из DT в клиент-серверный вариант с DB2 (который менее требователен к уникальности), и затем выполнить Тестирование и Исправление, а также Конфигурация — Проверка конфигурации — Проверка логической целостности конфигурации + Поиск некорректных ссылок.

1.4. Для локализации проблемы можно определить данные объекта, загрузка которого не удалась. Для этого надо включить во время загрузки трассировку в утилите Profiler или включите запись втехнологический журнал событий DBMSSQL и EXCP.

1.5. Если доступна узел (планы обменов), то выполнить обмен. Можно также дополнительно перед обменом выполнить пункт 2.3.5

2. Если проблема неуникальности проявляется во время работы пользователей:

2.1. Найти с помощью метода пункта 1.4 проблемный запрос.

2.1.2. Иногда ошибка возникает во время исполнения запросов, например:

Данная ошибка возникает из-за того что в модуле регистра накопления «Рабочее время работников организаций» в процедуре «ЗарегистрироватьПерерасчеты» в запросе не стоит служебное слово «РАЗЛИЧНЫЕ».

Т.е. должно быть:

Запрос = Новый Запрос(
«ВЫБРАТЬ РАЗЛИЧНЫЕ
|   Основные.ФизЛицо,

. . . . .

В последних выпущенных релизах ЗУП и УПП ошибка не возникает, т.к. там стоит «РАЗЛИЧНЫЕ».

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

2.2.1. «Рыба» скрипта для определения неуникальных записей с помощью SQL:
SELECT COUNT(*) Counter, <перечисление всех полей соответствующего индекса> from <имя таблицы>
GROUP BY <перечисление всех полей соответствующего индекса>
HAVING Counter > 1

2.2.2 Пример. Индекс в ошибке называется «_Document140_VT1385_IntKeyIndNG».

Перечень полей таблицы:

_Document140_IDRRef, _KeyField, _LineNo1386, _Fld1387, _Fld1388, _Fld1389, _Fld1390, _Fld1391RRef, _Fld1392RRef, _Fld1393_TYPE, _Fld1393_RTRef, _Fld1393_RRRef, _Fld1394,

_Fld1395, _Fld1396RRef, _Fld1397, _Fld1398, _Fld1399RRef, _Fld22260_TYPE, _Fld22260_RTRef, _Fld22260_RRRef, _Fld22261_TYPE, _Fld22261_RTRef, _Fld22261_RRRef

Перед выполнением приведенной ниже процедуры сделайте резервную копию базы данных.
Выполните в MS SQL Server Query Analizer:

select count(*), _Document140_IDRRef, _KeyField
from _Document140_VT1385
group by _Document140_IDRRef, _KeyField
having count(*) > 1

 

С его помощью узнайте значения колонок _Document140_IDRRef, _KeyField, дублирующихся записей (id, key).

При помощи запроса:

select *
from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1 or _Document140_IDRRef = id2 and _KeyField = key2 or …

 

посмотрите на значения других колонок дублирующихся записей.

Если обе записи имеют осмысленные значения и эти значения разные, то исправьте значение _KeyField на уникальное. Для этого определите максимальное занятое значение _KeyField (keymax):

 

select max(_KeyField)
from _Document140_VT1385
where _Document140_IDRRef = id1

 

Замените значение _KeyField в одной из повторяющихся записей на правильное:

 

update _Document140_VT1385
set _KeyField = keymax + 1
where _Document140_IDRRef = id1 and _LineNo1386 = lineno1

 

Здесь _LineNo1386 = — дополнительное условие, которое позволяет выбрать одну из двух повторяющихся записей.

Если одна (или обе) из повторяющихся записей имеет очевидно неправильное значение, то ее нужно удалить:

 

delete from _Document140_VT1385
where _Document140_IDRRef = id1 and _LineNo1386 = lineno1

 

Если повторяющиеся записи имеют одинаковые значения во всех колонках, то из них нужно оставить одну:

 

select distinct *
into #tmp1
from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1

delete from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1

insert into _Document140_VT1385
select #tmp1

drop table #tmp1

 

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

 

2.2.3. Второй пример:

SELECT     COUNT(*) AS Expr2, _IDRRef AS Expr1, _Description
FROM         _Reference8_
GROUP BY _IDRRef, _Description
HAVING      (COUNT(*) > 1)

 

 

2.3.4 Пример определения неуникальных записей с помощью запроса 1С:Предприятие:

ВЫБРАТЬ Справочник.Ссылка
ИЗ Справочник.Справочник КАК Справочник
СГРУППИРОВАТЬ ПО Справочник.Ссылка
ИМЕЮЩИЕ КОЛИЧЕСТВО(*) > 1

 

или для бухгалтерии

ВЫБРАТЬ
Подзапрос.Период,
Подзапрос.Регистратор,
<измерения>,
СУММА(Подзапрос.КоличествоЗаписей) КАК КоличествоЗаписей
ИЗ
(ВЫБРАТЬ
Хозрасчетный.Период КАК Период,
Хозрасчетный.Регистратор КАК Регистратор,
<измерения>,
1 КАК КоличествоЗаписей
ИЗ
РегистрБухгалтерии.Хозрасчетный КАК Хозрасчетный) КАК Подзапрос

СГРУППИРОВАТЬ ПО
Подзапрос.Период,
Подзапрос.Регистратор,
<измерения>

ИМЕЮЩИЕ
СУММА(Подзапрос.КоличествоЗаписей) > 1

 

2.3.5 Сделать индекс субд не уникальным. Заксриптовать индекс с помощью Management Studio.

Далее удалить текущий индекс, скорректировать текст и создать скриптом неуникальный индекс.

 

2.3.6 Частный случай при обмене в РБД. Ошибка приходится на «вспомогательные» таблицы, связанные с расчетом итогов или аналитики. Например:

Ошибка при вызове метода контекста (Записать): Попытка вставки неуникального значения в уникальный индекс:
Microsoft OLE DB Provider for SQL Server: Cannot insert duplicate key row in object ‘dbo._AccntRegED10319’ with unique index ‘_Accnt10319_ByPeriod_TRNRN’.
HRESULT=80040E2F, SQLSrvr: Error state=1, Severity=E, native=2601, line=1

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

Индексы 1С 8.3 и MS SQL

Сегодня речь пойдет о индексах СУБД MS SQL и их внутреннем устройстве. Я постараюсь рассказать о индексах и с точки зрения СУБД, и с точки зрения 1С 8.3.

Индексы — набор ссылок, упорядоченных по определенным столбцам, создаваемый с целью оптимизации производительности СУБД MS SQL.

Индексы в 1С

В системе 1С индексы создаются двумя способами — явным и неявным образом.

Создание индексов неявным образом: 

Платформа создает индексы сама по заранее известным для каждого объекта метаданных ключам данных (ссылка, код, наименование, измерения и т.п.)

Создание индексов явным образом возможна тремя способами:

1. Установка флага «Индексировать» у поля (реквизита/измерения). Вариант «Индексировать с доп. упорядочиванием» добавляет в индекс поле «Код» или «Наименование» (прежде всего для динамических списков).
2. Добавление поля в «Критерии отбора«.
3. Указание индексируемого поля в запрос с помощью конструкции «ИНДЕКСИРОВАТЬ ПО«.

Если вы только начинаете программировать в 1С или просто хотите систематизировать свои знания — попробуйте Школу программирования 1С нашего друга Владимира Милькина. Пошаговые и понятные уроки даже для новичка с поддержкой учителя.
Попробуйте бесплатно по ссылке >>

Индексы в СУБД Microsoft SQL

Индексы в СУБД MS SQL представляют из себя страницы с данными по 8 Кбайт каждая.  Несмотря на то, что индексы призваны улучшить производительность СУБД, у них есть определенные недостатки — они занимают место на диске и замедляют работу СУБД на запись строк.

Виды индексов в СУБД MS SQL:

1. Некластерные индексы — такие индексы не перестраивают таблицы, а лишь организуют ссылки.
2. Кластерные индексы нужны для построения таблицы в соответствии с индексом. Данные упорядочены, например, по алфавиту. Недопустим для часто изменяющихся столбцов, т.к. СУБД постоянно физически перестраивает таблицу по этому индексу.
3. Уникальные индексы — своего рода «надстройка» для кластерных и некластерных индексов. Такой индекс уникален по ключевым полям.

Виды ключей в СУБД:

• Первичный ключ (primary) — набор столбцов, уникально характеризующих строку.
• Внешний ключ (foreign) — поле таблицы, хранящее значение первичного ключа с целью организации связи между таблицами. 1С не использует данный вид ключей.

Важные нюансы использования индексов

Длина ключа индекса в основных СУБД (всех, кроме файлового варианта) — не более 900 байт и 16 различных полей.

Запускайте чаще дефрагментацию индексов на уровне СУБД MS SQL: при частом использовании индексов возможно появление эффекта фрагментации, нельзя допускать уровня фрагментации выше 25%.

Отсутствие индексов может привести к полному сканированию таблицы  (table scan), что, в свою очередь, приведет к избыточной блокировке.

Однако не стоит включать индексацию по небольшим наборам данных (например, справочник Организации) — построение индекса может привести к обратному эффекту, быстродействие снизится.

К сожалению, мы физически не можем проконсультировать бесплатно всех желающих, но наша команда будет рада оказать услуги по внедрению и обслуживанию 1С. Более подробно о наших услугах можно узнать на странице Услуги 1С или просто позвоните по телефону +7 (499) 350 29 00. Мы работаем в Москве и области.

Получение информации о размере базы данных, таблиц и индексов на диске (СУБД MS SQL Server)

Рост размера информационной базы является закономерным явлением ее эксплуатации, но, в некоторых случаях, данный процесс свидетельствует об ошибках в архитектуре системы. Среда SQL Server Management Studio предоставляет возможность легко получить информацию о занимаемом БД месте на диске, в том числе: сводную информацию; в разрезе таблиц базы данных; индексов таблиц. Анализ необычных (для системы в целом) данных может выявить ошибки архитектуры и/или ошибки выполнения регламентных операций. Способы получить такую информацию о размере данных на диске будут рассмотрены в данной статье.

«Стандартные отчеты» в пользовательском интерфейсе Management Studio

SQL Server Management Studio предоставляет минимальный необходимый набор стандартных отчетов для получения информации о размере базы данных/ее файлов/таблиц/индексов в режиме пользовательского интерфейса.

Доступ к этим отчетам может быть выполнен через «Обозреватель объектов» (Object explorer) → Правый клик мыши по базе данных → «Отчеты» (Reports) → «Стандартный отчет» (Standard reports)
Стандартные отчеты по использованию дискового пространства

Отчет «Занято места на диске» (Disk Usage)

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

В отчете представлена информация следующего рода:

• Общий объем, занятый на диске (Total space reserved)
• Место, занятое файлами данных (Data files space reserved)
• Место, занятое журналом транзакций (Transaction log space reserved)
• Отражает графически процент пространств в составе файлов данных: индексов (index), данных (data), не выделенного (unallocated) и не используемого (unused)
• Отражает графически процент примененного (used) и неиспользуемого (unused) пространства в составе журнала транзакций
• Выводит записи событий автоматического увеличения (autogrow) и/или сжатия (autoshrink) для базы данных
• Выводит информацию о месте на диске, используемом файлами данных

Отчет «Занято места на диске» (Disk Usage)

Отчеты «Использование дисковой памяти таблицей» (Disk Usage by Table), «Использование дисковой памяти верхними таблицами» (Disk Usage by Top Tables)

Отчет содержит подробные данные об использовании места на диске таблицами, расположенными в базе данных. Отличие этих двух отчетов заключается лишь в том что в отчете «By Top Tables» вывод происходит только для «верхних» (первых) 1000 таблиц.

В отчете представлена информация:

• Количество записей в таблице базы данных (Records)
• Размер зарезервированного пространства на диске (Reserved)
• Размер данных на диске (Data)
• Общий размер индексов таблицы на диске (Indexes)
• Размер не используемого пространства (Unused)

Отчет «Использование дисковой памяти таблицей» (Disk Usage by Table)

Отчет «Использование дисковой памяти секцией» (Disk Usage by Partition)

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

Хотел бы обратить Ваше внимание что в данном отчете неверно рассчитывается дисковое пространство по кластерному индексу. Для получения реально используемого дискового пространства кластерным индексом можно: из «объема, используемого всеми индексами таблицы» (указанном в отчете «Использование дисковой памяти таблицей») вычесть «объем всех не кластерных индексов» (по отчету «Использование дисковой памяти секцией»)

В отчете представлена информация:

• Число записей в индексе/секции (Records)
• Зарезервированное пространство на диске (Reserved)
• Используемое пространство на диске (Used)

Отчет «Использование дисковой памяти секцией» (Disk Usage by Partition)

Хранимые процедуры

Данные о размере базы данных и таблиц также можно получить с помощью хранимой процедуры sp_spaceused Management Studio.

Синтаксис:
sp_spaceused [[ @objname = ] ‘objname’ ]
[,[ @updateusage = ] ‘updateusage’ ]

В процедуре могут быть использованы 2 не обязательных параметра:

• @objname — Полное или неполное имя таблицы, индексированного представления или очереди. Если параметр не указан — результаты возвращаются для всей базы данных.
• @updateusage — Указывает на необходимость запустить процедуру обновления сведений

Примеры запросов по всей базе данных и по конкретной таблице приведены ниже:

— Указываем использование конкретной базы данных
USE [DevBase2]

— Информация по всей базе
exec sp_spaceused

— Информация по конкретной таблице
exec sp_spaceused N’_AccumRg17539′

— Указываем использование конкретной базы данных USE [DevBase2]   — Информация по всей базе exec sp_spaceused   — Информация по конкретной таблице exec sp_spaceused N’_AccumRg17539′

Результат работы хранимой процедуры sp_spaceused

В первом блоке отражен результат запроса по всей базе данных, во втором — по конкретной таблице.

Динамические административные представления

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

Примеры диагностируемых ошибок

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

Для ошибок «Использование остаточного регистра накопления для оборотных данных» и «Ошибки в алгоритме закрытия остаточного регистра» рост таблиц итоговых записей является буквально прямым следствием, но, возможна ситуация, что таков набор данных (приход был, а расхода еще не было).

Для ошибки «Отсутствует перерасчет итогов по регистрам» также характерен рост таблицы итоговых записей, но при этом (особенно для регистра остатков) дополнительным условием является наличие «нулевых» записей в регистре итогов. Подробнее об этом можно прочитать в статье «Что такое итоги регистров накопления».

Для ошибки «Использование избыточных индексов» первоочередным, конечно же, является анализ необходимости такого индекса. Но никто не станет анализировать все индексы базы данных без причин, а вот наличие «тяжелых» индексов таблицы может стать одним из таких сигналов к действию.

Касательно «Хранения массивных двоичных данных в таблицах БД», я считаю это неприемлемым, данные такого рода должны храниться на диске независимо от базы данных с обеспечением доступа к ним из системы, а также с обеспечением отдельной процедуры резервного копирования.

Кластерные и некластеризованные индексы

… и сортировка данных в SQL Server

Этот пост о различии между Clustered Index и Non Clustered Index является приквелом к ​​моему сообщению в блоге о требованиях и использовании кластерного индекса и NonClustered index, [ссылка] .

Согласно MS BOL (MSDN) в SQL Server индексы организованы как B-деревья. Каждая страница в индексном B-дереве называется индексным узлом. Верхний узел B-дерева называется корневым узлом.Нижний уровень узлов индекса называется листовыми узлами. Любые уровни индекса между корневым и конечным узлами вместе называются промежуточными уровнями.

В кластерном индексе: (MSDN)

1. Узлы Leaf содержат страницы данных базовой таблицы.

2. Узлы корневого и промежуточного уровней содержат страницы индекса, содержащие строки индекса.

3. Каждая строка индекса содержит значение ключа и указатель либо на страницу промежуточного уровня в B-дереве, либо на строку данных на конечном уровне индекса.Страницы на каждом уровне Указателя связаны в двусвязном списке.

4. Страницы в цепочке данных и строки в них упорядочены по значению ключа кластерного индекса.

5. В таблице может быть только один кластерный индекс.

6. Не поддерживает включенные столбцы, поскольку они уже содержат все столбцы, которых нет в индексе как включенные столбцы.

В некластеризованном индексе: (MSDN)

1. Конечный слой некластеризованного индекса состоит из страниц индекса, а не страниц данных.

2. Каждая строка индекса в некластеризованном индексе содержит значение некластеризованного ключа и указатель строки. Этот указатель указывает на строку данных в кластеризованном индексе или куче, имеющую значение ключа.

2.a. Если таблица является кучей, что означает, что у нее нет кластерного индекса, указатель строки является указателем на строку.

2.b. Если таблица имеет кластеризованный индекс или индекс находится в индексированном представлении, локатором строки является ключ кластеризованного индекса для строки. SQL Server извлекает строку данных путем поиска в кластеризованном индексе с использованием ключа кластеризованного индекса, хранящегося в конечной строке некластеризованного индекса.

3. Строки данных базовой таблицы не сортируются и не хранятся в порядке, основанном на их некластеризованных ключах.

4. Каждая таблица может иметь до 249 и 999 некластеризованных индексов в SQL Server 2005 и 2008 соответственно.

Индексирование и сортировка данных: (MSDN)

Согласно MS BOL, кластерный индекс реорганизует только страницы данных, так что строки логически сортируются в порядке кластерного индекса. Физический заказ страниц не гарантируется.SQL Server не обязательно физически хранит данные на диске в порядке кластеризованного индекса, но при создании индекса SQL Server пытается физически упорядочить данные как можно ближе к логическому порядку. Каждая страница конечного уровня индекса имеет указатель на страницу, которая логически предшествует текущей странице, и на страницу, которая логически следует за текущей страницей, тем самым создавая двусвязный список. Таблица sysindexes содержит адрес первой страницы конечного уровня. Поскольку гарантируется, что данные логически расположены в порядке кластеризованного индекса, SQL Server может просто начать с первой страницы и следовать указателям индекса от одной страницы к другой, чтобы получить данные по порядку.

Таким образом, физический порядок записей / строк не гарантируется, если в таблице есть кластерный индекс. Среди людей распространено заблуждение, что кластерный индекс сортирует данные физически, а некластеризованный индекс — логически.

Также обсуждал эту тему на форуме TSQL MSDN и получил несколько комментариев и ответов экспертов: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1c98a7ee-7e60-4730-a38c-f0e3f0deddba

Дополнительная информация по заказу:

Sql Server: автоматически ли перестраивает кластерный индекс перестраивает некластеризованные индексы

Sql Server: восстанавливает ли кластеризованный индекс автоматически некластеризованные индексы

18 января 2014 г., Sql Times

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

• Успешно выполнить необходимое техническое обслуживание
• Но не вызывает блокировки доступа к таблице
• Зависимый некластеризованный индекс перестраивается (при необходимости) или не перестраивается.

Какие команды предоставляет Sql Server для облегчения этих операций.

Рассмотрим пример:

СОЗДАТЬ ТАБЛИЦУ dbo.Frag_CI
(
ID INT NOT NULL IDENTITY (1,1)
, Frag VARCHAR (800)
)
ИДТИ

СОЗДАТЬ КЛАСТЕРНЫЙ ИНДЕКС PK_Frag_CI_ID
НА dbo.Frag_CI (ID ASC)
ИДТИ

СОЗДАТЬ НЕЗАКЛЮЧЕННЫЙ ИНДЕКС IX_Frag_CI_Frag
ON dbo.Frag_CI (Frag ASC)
ИДТИ

ВСТАВИТЬ В dbo.Frag_CI (Frag) ЗНАЧЕНИЯ ('1')
GO 1000
 

Теперь проверим фрагментацию. Мы видим, что как кластеризованные, так и некластеризованные индексы значительно фрагментированы.

выберите OBJECT_NAME (object_id) как Table_Name
, index_type_desc
, index_depth
, index_level
, avg_fragmentation_in_percent
, fragment_count
, avg_fragment_size_in_pages
, количество страниц
, avg_page_space_used_in_percent
, record_count
, forwarded_record_count
из сис.dm_db_index_physical_stats (db_id (), по умолчанию, по умолчанию, по умолчанию, ПО УМОЛЧАНИЮ)
где object_id = object_id ('Frag_CI')
ИДТИ
 

CI_nCI_Rebuild_Before

Решение

Вариант 1:

Перестройте таблицу, в результате чего одновременно будут перестраиваться как CI, так и nCI.

ALTER INDEX ALL ON dbo.Frag_CI REBUILD
 

Приведенный выше код позволяет нам перестроить все индексы в таблице одновременно, применяя все доступные параметры для сокращения продолжительности общего времени перестроения i.е. MAXDOP, ALLOW_PAGE_LOCKS и т. Д.

CI_nCI_Rebuild

Вариант 2 (новый):

Незначительное изменение предыдущей команды ALTER INDEX позволяет нам перестраивать только CLUSTERED индекс и не перестраивать соответствующие некластеризованные индексы.

Когда вы запускаете ALTER INDEX с ALL , это влияет на все индексы (очевидно). Без ключевого слова ALL он влияет только на выбранный индекс.

ALTER INDEX PK_Frag_Col1 ON dbo.Frag_CI REBUILD
ИДТИ
 

Вариант 3:

Здесь мы перестраиваем таблицу, используя команду ALTER TABLE, но на удивление она перестраивает только кластеризованный индекс и не затрагивает некластеризованные индексы.Интересно увидеть разницу в поведении ALTER TABLE… .REBUILD ALTER INDEX… REBUILD. Кроме того, для ALTER TABLE нет ключевого слова ALL. (Обратите внимание, что PARTITION = ALL влияет только на разделы, выбранные для восстановления в CI).

ALTER TABLE dbo.Frag_CI REBUILD
 

CI_Rebuild_no_nCI

Вариант 4:

В этом случае мы перестраиваем индекс, используя параметр «CREATE INDEX… WITH DROP_EXISTING». Как и ожидалось, при этом перестраивается только кластеризованный индекс (некластеризованный индекс не перестраивается).В настройке кластерного и некластеризованного индекса ключ кластеризации присутствует на конечных страницах некластеризованного индекса. Таким образом, когда CI перестраивается, ключ кластеризации не меняется, поэтому нет необходимости перестраивать nCI.

СОЗДАТЬ КЛАСТЕРНЫЙ ИНДЕКС PK_Frag_CI_ID
 ON dbo.Frag_CI (ID ASC)
 С (DROP_EXISTING = ON)
 ИДТИ
 

CI_Rebuild_no_nCI

Итак, в заключение, мы видим, что когда мы перестраиваем индекс CLUSTERED, это затрагивает только CI. Ни один из соответствующих некластеризованных индексов (nCI) не перестраивается.Каждый nCI на конечном уровне имеет ключ кластеризации из кластерного индекса. Поскольку это не меняется во время восстановления, nCI не нужно восстанавливать.

Надеюсь, это поможет,
_Sqltimes

Как это:

Нравится Загрузка …

Разница между кластерными и некластеризованными индексами SQL — новый стек

Викаш Кумар

Викаш Кумар пишет статьи на технические темы, работая в компании по разработке программного обеспечения Tatvasoft.com. Вы можете следить за ним в Twitter и LinkedIn.

Индексы используются для ускорения процесса запроса в SQL, что приводит к более высокой производительности. Они похожи на указатели учебников, где вам нужно перейти к определенной главе, найти номер страницы главы и напрямую перейти на эту страницу. Найти нужную главу без указателей может быть сложно, и процесс может быть намного медленнее. Система управления реляционными базами данных (СУБД) должна просмотреть все записи в таблице, чтобы получить желаемые результаты, если индексы отсутствуют.Этот метод называется сканированием таблиц.

Индекс — это ключ, который помогает такой базе данных, как MySQL, Oracle и SQL Server, находить связанную строку с точными значениями ключей в режиме реального времени. По сути, в серверах SQL используются два разных типа индексов: кластеризованный и некластеризованный.

В этой статье мы рассмотрим разницу между этими двумя индексами в реляционной базе данных.

Что такое кластерный индекс?

Кластерный индекс — это своего рода файл, который сортирует информацию в таблице по их ключевым качествам.Для каждой таблицы в базе данных существует только один сгруппированный список. Следовательно, для каждой таблицы существует только одна сгруппированная запись. Вот несколько характеристик кластерного индекса:

• Хранилище данных по умолчанию и отсортированных данных
• Помогает одновременно хранить данные и индексировать
• Сканирование кластерного индекса и поиск по индексу
• Поиск по ключу

Иллюстративная структура B-дерева, добавленная ниже о том, как работают кластерные индексы:

Источник изображения: red-gate

Преимущества:

• Кластерные индексы — идеальный вариант для диапазона или группы, которые получают запросы типа min, max или count.
• Любой релевантный поиск ведет непосредственно к определенному месту в информации с целью, которую вы можете продолжать последовательно просматривать из таблицы.
• Он использует механизм определения местоположения, чтобы найти раздел файла в начале диапазона.
• Замечательный метод поиска по дальности, когда упоминаются ключевые качества охоты.
• Минимизируйте передачу страниц и увеличьте количество попаданий в кеш.

Недостатки:

• Ограничьте перемещение страниц и увеличьте резервный поиск.
• Различные методы для непоследовательного запроса
• Делает много согласованных частей страниц, включая информационную страницу, как страницы списка.
• Дополнительная работа для вставки, обновления и удаления в SQL.
• Отбрасывает более длительные усилия по обновлению записей.
• Почти все данные содержатся в ведущих узлах индекса.

Что такое некластерный индекс?

Некластеризованный индекс хранит метаинформацию в одной области и файлы в другой области.Файл состоит из указателей на расположение этой информации. В отдельной таблице может быть множество несгруппированных записей в виде файла. Некластеризованный индекс определяется в неупорядоченном поле таблицы, и этот тип метода улучшает производительность запросов, поскольку они используют ключи, которые не назначены в качестве первичного ключа. Вот несколько характеристик кластерного индекса:

• Сохраняет только ключевые значения
• Указатели на строки кучи / кластеризованного индекса
• Разрешает доступ к вторичным данным
• Данные моста
• Операции индексного сканирования и индексного поиска
• Создать некластеризованный индекс для таблицы или представления

Преимущества:

• Помогает быстро извлекать данные из таблицы базы данных.
• Накладные расходы можно избежать по сравнению с кластеризованным индексом.
• Его можно использовать для создания более одного индекса, поскольку в СУБД имеется несколько некластеризованных индексов.

Недостатки:

• Храните данные в наиболее логичном порядке, не разрешая физическую сортировку строк данных.
• Процесс поиска по некластеризованному индексу немного дороже.
• Соответствующее обновление требуется каждый раз при обновлении ключа кластеризации, чтобы сохранить его в некластеризованном индексе.

Ниже добавлена ​​иллюстративная структура B-Tree, показывающая, как работают некластеризованные индексы:

Некластеризованные индексы хранят ключ кластеризации для поиска данных в кластеризованном индексе.

В чем разница между этими индексами?

Технические характеристики	Кластеризованный
Хранилище	Хранит страницы данных индекса в конечных узлах.	Это не позволяет хранить страницы данных индекса в конечных узлах.
Размер	Довольно большой	Маленький
Доступ к данным	Быстрее и быстрее	Медленнее
Дополнительное хранилище на диске	Не требуется	Требуется для отдельного хранения данных.
Типы ключей	По умолчанию первичный ключ таблицы — это кластерный индекс.	Используется с ограничением уникальности, действующим как составной ключ для таблицы.
Привлекательный элемент	Производительность поиска данных можно улучшить.	Создает столбцы, которые используются в объединениях.

Изображение функции через Pixabay.

SQL 2016 — Разделение существующей таблицы с помощью индекса Columnstore

— Лори Браун @SQLSupahStah

Недавно я наткнулся на хранилище данных с самой большой секционированной таблицей. Мы переходили на SQL 2016 и хотели убедиться, что разделы по-прежнему работают и находятся на своих местах. Поскольку я чувствовал, что мне нужно освежить свои навыки разделения таблиц, я решил провести небольшой тест по разделению существующей таблицы по годам и, чтобы сделать его более увлекательным, я хотел иметь индекс columnstore, чтобы увидеть, насколько интересными могут быть вещи. .

Я использую таблицу Fact.Purchase в новой базе данных WideWorldImportersDW. WideWorldImporters заменяет AdventureWorks, и его можно изучить и загрузить отсюда: https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/09/wideworldimporters-the-new-sql-server-sample-database/

Я немного поинтересовался в таблице Fact.Purchase и обнаружил, что столбец Date Key отлично подходит для разбивки по годам. И я обнаружил, что даты в таблице обычно относятся к периоду с 2013 по 2016 год.

Сначала вы должны добавить файловые группы, которые будут использоваться для таблицы после ее разделения. Я добавил 4 файловые группы за 2013-2016 годы.

Как только это будет сделано, я должен добавить файл .NDF для каждой файловой группы и сопоставить файл с правильной файловой группой.

Далее мне нужны функция раздела и схема раздела.

ИСПОЛЬЗОВАНИЕ [WideWorldImportersDW]

GO

ФУНКЦИЯ СОЗДАТЬ РАЗДЕЛ [PF_Purch_Year] (дата) КАК ПРАВИЛЬНО ДИАПАЗОН ДЛЯ ЗНАЧЕНИЙ («2014-01-01», «2015-01-01», «2016-01-01»)

GO

СОЗДАТЬ СХЕМУ РАЗДЕЛА [PS_Purch_Year] КАК РАЗДЕЛ [PF_Purch_Year] TO (Purch3013, Purch3014, Purch3015, Purch3016)

GO

В основном я устанавливаю параметры или границы для объектов, которые используют функцию PF_PURCH_YEAR, чтобы помещать значения даты старше 01.01.2014 в файловую группу Purch3013, значения между 01.01.2014 и 01.01.2015 в Purch3014 filegroup, значения между 01.01.2015 и 01.01.2016 в файловую группу Purch3015 и, наконец, все, что новее или равно 01.01.2016, в файловую группу Purch3016.

Теперь, когда у меня все настроено, мне нужно переместить в него данные. Однако я не могу этого сделать с существующим кластеризованным индексом. Мне нужно отбросить кластерный индекс и перестроить его со схемой секционирования. Это немного сбивает с толку, если вы пытаетесь переместить существующие объекты, как вы увидите.

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

ИСПОЛЬЗОВАНИЕ [WideWorldImportersDW]

GO

DROP INDEX [CCX_Fact_Purchase] ON [Факт].[Покупка]

GO

ИЗМЕНИТЬ ТАБЛИЦУ [Факт]. [Покупка] ОГРАНИЧЕНИЕ ОТКАЗА [PK_Fact_Purchase]

GO

DROP INDEX [FK_Fact_Purchase_Date_Key] ON [Факт]. [Покупка]

GO

DROP INDEX [FK_Fact_Purchase_Stock_Item_Key] ON [Факт]. [Покупка]

GO

DROP INDEX [FK_Fact_Purchase_Supplier_Key] ON [Факт]. [Покупка]

GO

А вот и пытаемся восстановить … вот где становится интересно …

ИСПОЛЬЗОВАНИЕ [WideWorldImportersDW]

GO

СОЗДАТЬ КЛАСТЕРНЫЙ ИНДЕКС COLUMNSTORE [CCX_Fact_Purchase] НА [Факт].[Покупка]

С (DROP_EXISTING = OFF)

ON PS_Purch_Year ([Date Key])

GO

Получил ошибку:

Msg 35316, уровень 16, состояние 1, строка 3

Оператор завершился ошибкой, поскольку индекс columnstore должен быть выровнен по секциям с базовой таблицей . Создайте индекс columnstore, используя ту же функцию секционирования и ту же (или эквивалентную) схему секционирования, что и базовая таблица. Если базовая таблица не секционирована, создайте несекционированный индекс columnstore.

Ой ой! Это означало, что таблица уже была частью другой схемы секционирования. Я не думал, что искать это, прежде чем начал. Ух! Я подумал, что должно быть легко изменить выравнивание разделов базовой таблицы, и это так, но это не супер-интуитивно понятно. Поскольку я уже отбросил все свои индексы, я решил, что мне нужно воссоздать исходный кластерный индекс на старом разделе, поскольку так базовая таблица будет связана с чем угодно. Перестроил на старый раздел.Все идет нормально. Когда я попытался восстановить его с помощью DROP_EXISTING = ON, я получил ту же ошибку, что и раньше. Поразмыслив немного, я снова все отбросил, но на этот раз создал обычный кластерный индекс на новом разделе для выравнивания базовой таблицы. Это сработало!

ИЗМЕНИТЬ ТАБЛИЦУ [Факт]. [Покупка] ДОБАВИТЬ ОГРАНИЧЕНИЕ [PK_Fact_Purchase] ПЕРВИЧНЫЙ КЛЮЧ КЛАСТЕРИРОВАН

(

[Ключ покупки] ASC,
[Ключ даты] ASC

) ON PS_Purch_Year ([Date Key])

GO

Теперь базовая таблица выровнена со схемой секционирования, которую я хотел использовать, но мне нужно, чтобы кластеризованный индекс был индексом columnstore.Мы должны отбрасывать и снова создавать, и все становится на свои места.

ИЗМЕНИТЬ ТАБЛИЦУ [Факт]. [Покупка] ОГРАНИЧЕНИЕ ОТКАЗА [PK_Fact_Purchase]

GO

ИСПОЛЬЗОВАНИЕ [WideWorldImportersDW]

GO

СОЗДАТЬ КЛАСТЕРНЫЙ ИНДЕКС COLUMNSTORE [CCX_Fact_Purchase] НА [Факт]. [Покупка]

С (DROP_EXISTING = OFF)

ON PS_Purch_Year ([Date Key])

GO

ИЗМЕНИТЬ ТАБЛИЦУ [Факт]. [Покупка] ДОБАВИТЬ ОГРАНИЧЕНИЕ [PK_Fact_Purchase] ПЕРВИЧНЫЙ КЛЮЧ БЕЗ ВКЛЮЧЕНИЯ

(

[Ключ покупки] ASC,
[Ключ даты] ASC

) ON PS_Purch_Year ([Date Key])

GO

СОЗДАТЬ НЕЗАКРЫТЫЙ ИНДЕКС [FK_Fact_Purchase_Date_Key] ВКЛ [ФАКТ].[Покупка]

(

[Ключ даты] ASC

) ON PS_Purch_Year ([Date Key])

GO

СОЗДАТЬ НЕЗАКЛЮЧЕННЫЙ ИНДЕКС [FK_Fact_Purchase_Stock_Item_Key] НА [Факт]. [Покупка]

(

[Ключ номенклатуры] ASC

) ON PS_Purch_Year ([Date Key])

GO

СОЗДАТЬ НЕЗАКЛЮЧЕННЫЙ ИНДЕКС [FK_Fact_Purchase_Supplier_Key] НА [Факт]. [Покупка]

(

[Ключ поставщика] ASC

) ON PS_Purch_Year ([Date Key])

GO

Успех !!!! Теперь мне просто нужно было убедиться, что все находится там, где я ожидал.Я украл следующие запросы из MSDN…

ВЫБРАТЬ *

ИЗ системных таблиц AS t

ПРИСОЕДИНЯЙТЕСЬ к sys.indexes AS i

ON t. [Object_id] = i. [Object_id]

JOIN sys.partition_schemes ps

ВКЛ i.data_space_id = ps.data_space_id

WHERE t.name = «Покупка»;

GO

По мере того, как вы просматриваете приведенные выше результаты, я вижу, что все мои индексы находятся в схеме секционирования PS_Purch_Year.

ВЫБРАТЬ t.имя AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id,

f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue

ИЗ системных таблиц AS t

ПРИСОЕДИНЯЙТЕСЬ к sys.indexes AS i

НА t.object_id = i.object_id

JOIN sys.partitions AS p

ON i.object_id = p.object_id И i.index_id = p.index_id

ПРИСОЕДИНИТЬСЯ к sys.partition_schemes AS s

ВКЛ i.data_space_id = s.data_space_id

JOIN sys.partition_functions AS f

ВКЛ s.function_id = f.function_id

ЛЕВОЕ СОЕДИНЕНИЕ sys.partition_range_values ​​AS r

ON f.function_id = r.function_id и r.boundary_id = p.partition_number

ГДЕ t.name = «Покупка»

ЗАКАЗАТЬ ПО p.partition_number;

Этот запрос показывает границы.

Я также нашел действительно удобный запрос, созданный Дэвидом Питером Хансеном, который дает вам информацию о разделах в таблице гораздо более кратко.https://davidpeterhansen.com/view-partitions-in-sql-server/ Как говорится… .пойди, прочти все это. J

Надеюсь, это поможет тому, кто хочет разбить существующую таблицу. Я сделал это в своей собственной системе с небольшой таблицей, а не в производственной системе, поэтому обязательно протестируйте свой процесс на тестовой системе, прежде чем вы случайно вызовете проблему в производственной среде.

No related posts.