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

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

Приведу простой пример. Я находился в одном из офисов, где в это время была запущена система Microsoft CRM. Когда открылось первое окно приложения, система приняла решение отобразить на экране группу клиентов по запросу. При этом для ограничения числа возвращаемых клиентов было использовано предложение TOP. Все функционировало прекрасно, за одним исключением: не был предусмотрен подходящий индекс для поддержки запроса. Иначе говоря, приложение открывалось гораздо медленнее, чем следовало бы. После добавления индекса проблема была устранена и быстродействие приложения в процессе запуска повысилось. Но реакцией на такое изменение могли быть в лучшем случае нахмуренные брови представителей поставщика, а в худшем — запрет на предложенную модификацию в соответствии с соглашением о поддержке продукта.

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

Так следует ли нам смириться с низким быстродействием или есть смысл попытаться поискать компромисс?

Для многих клиентов мы подготовили сценарии, улучшающие индексирование баз данных приложений, а затем написали дополнительные сценарии, возвращающие систему индексирования в исходное состояние. Клиент получает инструкцию запускать «сценарий № 2» всякий раз перед обращением к поставщику за обновлением продукта. Вторая проблема состоит в том, что поставщик, возможно, исправил недоработку в обновленной версии, так что перед повторным применением усовершенствованных индексов обычно приходится проводить дополнительный раунд проверок и испытаний. Такой подход хорош при работе с приложениями, которые не изменяются на регулярной основе. Однако дело в том, что у разработчиков новых приложений сложилась практика практически беспрерывно дополнять их обновлениями.

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

Проблема взаимоблокировок

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

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

Индексы и блокировки

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

Поясню эту мысль на примере. Опубликованный в листинге код формирует таблицу, которую мы будем использовать при тестировании.

Если мы будем обновлять столбец PrimaryContact с помощью первичного ключа, объем блокируемых ресурсов будет минимальным:

BEGIN TRAN;

UPDATE dbo.Customers
  SET PrimaryContact = N'Freddie Mercury'
  WHERE CustomerID = 3;

Я отметил, что мой сеанс — session_id 53, и могу теперь проверить (в другом окне запроса) блокировки, сохраняемые при использовании обновления (см. экран 1).

SELECT resource_type, request_mode,
   request_type, request_status
FROM sys.dm_tran_locks WHERE
   request_session_id = 53;

 

Блокируемые ресурсы
Экран 1. Блокируемые ресурсы

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

Теперь давайте создадим индекс, включающий столбец PrimaryContact, и добавим включенный столбец PhoneNumber:

CREATE INDEX
   IX_dbo_Customers_PrimaryContact
ON dbo.Customers
(
  PrimaryContact
)
INCLUDE
(
  PhoneNumber
);
GO

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

BEGIN TRAN;

UPDATE dbo.Customers
  SET IsReseller = 0
  WHERE CustomerID = 3;

 

Блокируемые ресурсы не изменились
Экран 2. Блокируемые ресурсы не изменились

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

BEGIN TRAN;

UPDATE dbo.Customers
  SET PrimaryContact = N'Freddie Mercury'
  WHERE CustomerID = 3;

 

Блокируемые ресурсы при добавлении индекса
Экран 3. Блокируемые ресурсы при добавлении индекса

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

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

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

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

Мне показалось очень странным, что всякий раз, когда возникала взаимоблокировка, поступал запрос на эксклюзивную блокировку на уровне таблицы (см. экран 4).

 

Запрос на блокировку таблицы
Экран 4. Запрос на блокировку таблицы

Почему запрашивается эксклюзивная блокировка таблицы в случае обновления одной строки этой таблицы?

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

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

Посвятив какое-то время исследованию свойств таблицы и индексов, я понял, в чем состоит проблема (см. экран 5).

 

Причины блокировок на уровне таблицы
Экран 5. Причины блокировок на уровне таблицы

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

Блокировки могут эскалироваться — передаваться на более высокий уровень. По умолчанию, как только вы получите 5000 блокировок по ключу, SQL Server примет решение получить блокировку более высокого уровня (осуществить эскалацию блокировки), чтобы свести к минимуму непроизводительные затраты на управление блокировками. В версиях до SQL Server 2008 эскалация осуществлялась до уровня таблицы, а в SQL Server 2008 и более поздних версиях эскалация производится до уровня раздела. Такой тип эскалации возможен только в случаях использования множества блокировок по ключу, так что в данной ситуации он неприменим.

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

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

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

SELECT t.name AS TableName,
    i.name AS IndexName
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
ON i.object_id = t.object_id
WHERE t.is_ms_shipped = 0
AND i.allow_row_locks = 0
OR i.allow_page_locks = 0;

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

Листинг. Создание тестовой таблицы
CREATE DATABASE IndexText;

GO
USE IndexText;
GO

CREATE SEQUENCE dbo.CustomerIDs
AS int
START WITH 1;
GO

CREATE TABLE dbo.Customers
(
  CustomerID int NOT NULL
    CONSTRAINT PK_dbo_Customers PRIMARY KEY
    CONSTRAINT DF_dbo_Customers_CustomerID
      DEFAULT (NEXT VALUE FOR dbo.CustomerIDs),
  CustomerName nvarchar(100) NOT NULL,
  PrimaryContact nvarchar(50) NOT NULL,
  PhoneNumber nvarchar(20) NOT NULL,
  IsReseller bit NOT NULL,
  CreatedWhen datetime NOT NULL
    CONSTRAINT DF_dbo_Customers_CreatedWhen DEFAULT (SYSDATETIME()),
  LastUpdated datetime NOT NULL
    CONSTRAINT DF_dbo_Customers_LastUpdated DEFAULT (SYSDATETIME())
);
GO

INSERT dbo.Customers (CustomerName, PrimaryContact, PhoneNumber, IsReseller)
VALUES (N'Big Time Movie Productions', N'Sandra Bullock', N'02 9552-4232', 1),
    (N'Even Bigger Movies', N'Tom Hanks', N'02 9234-2343', 1),
    (N'Yet Another Production Company', N'Justin Bieber', N'03 8283-2323', 0),
    (N'A Tiny Production Company', N'John Nobody', N'07 2342-2342', 0);
GO