13-я статья об отрицательном опыте в работе с MS SQL Server25.09.2017 09:03

Предисловие

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

Статья написана с целью неповторения данных ошибок. И, как говорится, отрицательный опыт — это тоже опыт и порой даже ценнее положительного.

Ошибки

Процентное приращение файлов БД (базы данных)
Т к рост файла (будь то данные или журнал транзакций) БД-весьма ресурсоемкая операция, то благими намерениями может показаться выставление этого роста именно в процентных соотношениях. Соглашусь, во многих рекомендациях сказано, что лучше выставлять не процентный, а фиксированный прирост, выраженный в МБ. Однако, не раскрывается почему именно так. Исходя из практики, не рекомендуется устанавливать прирост файла БД выше 1 ГБ, т к MS SQL Server быстрее выделит 2 раза по 1 ГБ, чем сразу 2 ГБ. Также, если выделять меньше 32 МБ (исходя опять же из практики), то рано или поздно сама база данных начинает просто висеть. Отлично, определились, что приращивать файлы БД стоит фиксировано от 32 до 1024 МБ. Но вот почему еще нельзя в процентах указывать прирост файлов БД? Оказывается, что как только файл станет меньше 1 МБ, то СУБД округляет эту величину до 0 МБ и прекращает увеличивать этот файл. В результате возникает простой системы. Чтобы узнать на сколько увеличивать файл, достаточно сделать анализ за сутки-на сколько вырастает каждый из файлов в МБ, и выставить соответствующее число, но в диапазоне от 32 до 1024 МБ. Сбор статистики по росту файлов БД можно получить следующим образом.
Очень много внешних ключей на таблицу
Вы когда-нибудь пробовали смотреть план при удалении хотя бы одной строки из таблицы, на которую ссылаются чуть ли не сотни других таблиц? Вы удивитесь, сколько там вложенных циклов. И все они-это проверки по внешним ключам. Поэтому если таблицы большие (миллионники), то лучше выключить внешние ключи на таблицу, в которой будут удаляться данные, затем удалить все необходимые и связанные с ними данные, и после этого включить внешние ключи. Аналогичная ситуация и с каскадными обновлениями и удалениями. Если внешних связей очень много (сотни), то даже удаление 1 строки может привести к долгой и очень обширной блокировке.

Много лишних индексов

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

Код

select DB_NAME(t.database_id)		as [DBName]
	 , SCHEMA_NAME(obj.schema_id)	as [SchemaName]
	 , OBJECT_NAME(t.object_id)		as [ObjectName]
	 , obj.Type						as [ObjectType]
	 , obj.Type_Desc				as [ObjectTypeDesc]
	 , ind.name						as [IndexName]
	 , ind.Type						as IndexType
	 , ind.Type_Desc				as IndexTypeDesc
	 , ind.Is_Unique				as IndexIsUnique
	 , ind.is_primary_key			as IndexIsPK
	 , ind.is_unique_constraint		as IndexIsUniqueConstraint
	 , t.[Database_ID]
	 , t.[Object_ID]
	 , t.[Index_ID]
	 , t.Last_User_Seek
	 , t.Last_User_Scan
	 , t.Last_User_Lookup
	 , t.Last_System_Seek
	 , t.Last_System_Scan
	 , t.Last_System_Lookup
from sys.dm_db_index_usage_stats as t
inner join sys.objects as obj on t.[object_id]=obj.[object_id]
inner join sys.indexes as ind on t.[object_id]=ind.[object_id] and t.index_id=ind.index_id
where (last_user_seek	is null or last_user_seek		4 and t.[object_id]>0 --исключаются системные БД

Нерациональное использование ресурсов
Часто можно встретить в рекомендациях, что необходимо журнал транзакций и файл данных БД выносить на разные носители данных. Если использовать RAID 10 с 4-мя и более SSD-дисками, то нет смысла изоляции файлов друг от друга. Для еще большей скорости, при необходимости БД tempdb можно разместить на диске, который был сформирован из ОЗУ. Также слишком большие объемы ОЗУ, которые предоставляются СУБД, приведут к тому, что последний заполнит всю память неактуальными планами запросов.
Битые резервные копии
Как это не банально может звучать, но всегда нужно не просто проверять созданные резервные копии, а также перемещать их на тестовый стенд и восстанавливать. И все это необходимо автоматизировать с последующим уведомлением администраторам как о проблемных, так и об успешных восстановлениях.
Ложная отказоустойчивость
Прежде чем делать из двух и более серверов кластер, необходимо убедиться в том, что система хранения данных тоже отказоустойчива, т к при выходе из строя последнего, сведет к нулю всю отказоустойчивость.
Сложная диагностика без простых проверок
Если происходит простой системы, то необходимо сначала проверить журналы MS SQL Server, а уже потом копаться более детально, т к зачастую все проблемы записываются именно туда. Не проводить простых проверок-это то же самое, что не померить температуру пациента, а сразу проводить сложную диагностику.
Забытые таблицы
Таблицы могут распухнуть ненужными старыми данными, которые либо необходимо архивировать в отдельную БД, либо удалять. Также таблицы могут перестать использоваться. Необходимо об этом помнить.

Это все 8 отрицательных опытов, с которыми мне приходилось сталкиваться.
Не повторяйте приведенных выше ошибок.

Источники:

» Документация по SQL
» Автоматизация по сбору данных о росте таблиц и файлов всех баз данных