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

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

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

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

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

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


sql-server entity-framework performance multi-tenant parameter-sniffing
person Mike    schedule 19.10.2012    source источник
comment
AFAIK, вам нужно использовать хранимые процедуры, чтобы исправить это. Если только в EF нет какой-то функции, которая может помочь вам обойти это.   -  person RBarryYoung    schedule 19.10.2012
comment
OPTIMIZE FOR UNKNOWN не имеет описанного вами поведения. Вы путаете это с OPTION (RECOMPILE). Если вы используете Enterprise Edition, вы можете просмотреть руководства по планам, чтобы дать подсказку. Или есть флаг трассировки (4136), чтобы полностью отключить отслеживание параметров, но это относится к < b> экземпляр, а не база данных.   -  person Martin Smith    schedule 16.02.2014

Ответы (2)


arrow_upward
3
arrow_downward

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

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 [email protected].

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

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

person E.J. Brennan    schedule 19.10.2012
comment
По сути, это то, что делает подсказка запроса OPTIMIZE FOR UNKNOWN. К сожалению, я не могу использовать ни один из подходов с Entity Framework, потому что не могу изменить создаваемый запрос. - person Mike; 19.10.2012

arrow_upward
1
arrow_downward

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

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

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

person user98258    schedule 06.08.2018