Многопользовательские базы данных SQL Server и сниффинг параметров

Question

У меня есть многопользовательская база данных в SQL Server 2012, где строки каждого арендатора идентифицируются tenant_id столбец (он же Shared Database, Shared Schema). У некоторых арендаторов, особенно у новых, очень мало рядов, а у других их много.

Оптимизатор запросов SQL Server обычно строит план запроса на основе параметров, предоставленных во время его первого выполнения, а затем повторно использует этот план для всех будущих запросов, даже если предоставляются другие параметры. Это известно как сниффинг параметров.

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

Существует подсказка для запроса, которую вы можете использовать, чтобы запретить SQL Server кэшировать планы запросов (OPTIMIZE FOR UNKNOWN) но это приводит к дополнительным издержкам, поскольку план запроса генерируется каждый раз при вызове запроса. Дополнительная проблема заключается в том, что мы используем Entity Framework, который не дает возможности указать OPTIMIZE FOR UNKNOWN подсказка по запросам.

Таким образом, вопрос заключается в том, что является наилучшей практикой для многопользовательских баз данных в отношении отслеживания параметров? Есть ли способ отключить сниффинг параметров по всей базе данных без необходимости указывать его при каждом запросе? Если так, то это даже лучший подход? Должен ли я разделить данные другим способом? Есть ли какой-то другой подход, о котором я не думаю?

Answer 1

У меня были похожие проблемы, и я успешно решил их, передав свои параметры следующим образом:

CREATE PROCEDURE [dbo].[InsertAPCheck]
@APBatchID  int = Null,
@BankAccountID  int = Null
AS
  /* copy parameters to temporary variables */
  SELECT @xAPBatchId = APBatchId, @xBankAccountID = @BankAccountID
 .
 .
 /* now run the meat of your logic using the temp variables */
 SELECT * FROM myTable where Id=@xAPBatchId.....etc.

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

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

Answer 2

Для динамического SQL, такого как SQL, который генерирует Entity Framework, вставьте комментарий в текст команды, который содержит идентификатор для текущего арендатора. Это по существу разделяет кэш планов выполнения SQL по арендатору, сохраняя планы выполнения изолированными для арендатора, но позволяя их повторно использовать одному и тому же арендатору.

Чтобы добавить комментарий в текст команды, вы можете создать подкласс / реализовать DbConnection/IDbConnection а также DbCommand/IDbCommand и применить шаблон декоратора. Звонки в DbCommand/IDbCommand.Execute* может добавить закомментированный идентификатор арендатора перед вызовом внутреннего метода, а затем удалить комментарий после возврата. Инициализируйте Entity Framework или любой другой ORM, который вы используете, с оформленным соединением.

Если у вас много арендаторов, возможно, имеет смысл разделить кэш плана по категориям. В противном случае вы будете делать то же самое, что и OPTION (RECOMPILE) потому что планы истекают из кэша, прежде чем они будут повторно использованы.