Повысить или понизить уровень гранулярности блокировок на каком-либо индексе или таблице можно с помощью процедуры sp_indexoption.

И всё-таки выработка стратегии блокировок – задача скорее разработчика, чем администратора. Для программиста огромным подспорьем будет уже картина блокировок, полученная вами на этапе поиска проблемы.

Снимаем дедлоки

Как уже говорилось, дедлоки, завязанные на несколько объектов, снимаются довольно легко. Ведь такой дедлок означает, что к одним и тем же объектам базы данных разные транзакции обращаются в разном порядке. Достаточно установить один какой-то порядок и проследить, чтобы он везде выполнялся. Если вы решили везде обращаться сначала к А, и только потом к Б, а необходимо сделать наоборот, то надо просто установить сначала блокировку на А, а потом можно работать с Б и снова с А в своё удовольствие. Например, это можно сделать так:

declare @l tinyint select @l = 1 from A with (tablockx) where 1 = 2

Менее понятно, что делать с дедлоками в рамках одной таблицы. Часто они решаются четким указанием режима уровня блокировок (pagelock, например). Тогда сервер не будет повышать эту блокировку до табличного уровня без крайней необходимости. Возможно, что придётся поработать с индексами и планами запросов, приводящих к взаимной блокировке. Ну и наконец, самый надёжный способ борьбы с дедлоками на уровне таблицы – это подсказка with (tablock) или изменение режима блокировок с помощью sp_indexoption. Конечно, производительность в этой ситуации может пострадать (а может и нет, зависит от конкретной БД). Но зато дедлоков не будет наверняка.

Проблемы на аппаратном уровне

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

Заключение

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