Кэшированные результаты скалярной функции SQL CLR

Общая проблема

После перехода на SQL Server 2014 выполнение скалярной функции CLR, которая ранее давала разные результаты для каждого выполнения в SQL Server 2008 R2, теперь дает идентичные результаты для каждого последующего вызова.

Тест/Иллюстрация

DECLARE @i int = 0;
WHILE @i < 10
 BEGIN
    print dbo.getEligLoadTXID();
    --> Simulate delay as this code never gets called more than once every few seconds in our environment
    WAITFOR DELAY '00:00:00.1';
    SET @i += 1;
 END;

В настоящее время мы находимся в процессе обновления нашей среды с SQL Server 2008 R2 до SQL Server 2014. Одна из проблем, с которыми мы столкнулись, связана с выполнением скалярной функции CLR. Функция относительно проста; он генерирует внутренний идентификатор на основе количества тиков (не будем спорить о необходимости/назначении этой функции).

В среде SQL Server 2008 R2 выполнение функции в цикле возвращает ожидаемые результаты (каждое выполнение приводит к новому значению). Однако при тестировании в нашей новой среде SQL Server 2014 один и тот же код возвращает ОДИНАКОВОЕ значение для каждого выполнения в цикле.

Результаты в SQL Server 2008 R2:

20161213502167209995
20161213502168210095
20161213502169210195
20161213502170210295
20161213502171210395
20161213502172210495
20161213502173210595
20161213502174210695
20161213502175210795
20161213502176210895

Результаты в SQL Server 2014:

20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787
20161213502199924787

Мы используем этот код для создания определенного идентификатора для каждого файла определенного типа, который мы получаем. Новое значение генерируется для каждого файла, но процесс SSIS, который обрабатывает это, обычно занимает 4-5 секунд для обработки каждого файла. Итак, эта функция вызывается раз в несколько секунд из совершенно разных соединений, но мы все равно получаем дублирующиеся результаты.

Возвращаемое значение периодически меняется, поэтому мы не получаем только одно значение в течение всего дня, но в течение определенного периода времени, будь то 3 секунды или 30 секунд, функция будет возвращать один и тот же результат.

Что нам не хватает?

Определение функции CLR

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static String getTransactionID()
{
    DateTime NOW = DateTime.Now;

    long ticks = NOW.Ticks - new DateTime(NOW.Year, NOW.Month, NOW.Day, 0, 0, 0, 0).Ticks;

    return String.Format("{0:0000}{1:00}{2:00}{3}", NOW.Year, NOW.Month, NOW.Day, ticks.ToString());
}

sql-server sql-server-2014 sqlclr
person Tim Darnall    schedule 13.12.2016    source источник

Ответы (1)


arrow_upward
6
arrow_downward

Это ожидаемое поведение, начиная с SQL Server 2012.

Проблема в том, что вы указали через атрибут SqlFunction, что эту функцию следует рассматривать как «детерминированную»:

IsDeterministic = true

Детерминированная функция возвращает одно и то же значение для одних и тех же входных значений. А учитывая, что у вашей функции нет никаких входных параметров, то все ее исполнения по сути одинаковы.

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

Чтобы исправить это, вы должны иметь возможность установить IsDeterministic = false или просто удалить IsDeterministic = true,, поскольку значение по умолчанию для IsDeterministic равно false.

P.S. Я знаю, что вы сказали не спорить о том, почему у вас есть эта конкретная функция, а просто упомянуть, что на случай, если вы (или другие) не знали об этом, текущее значение галочки должно отображаться в DMV:

SELECT [cpu_ticks] FROM sys.dm_os_sys_info;

П.П.С. Также лучше всего использовать типы Sql* для входных параметров и возвращаемых значений. Это означает, что используйте SqlString вместо String для возвращаемого типа. Это может потребовать добавления using System.Data.SqlTypes;.

person Solomon Rutzky    schedule 13.12.2016
comment
Спасибо за ответ! Вы не только ответили на мой вопрос, но и подробно рассказали. - person Tim Darnall; 21.12.2016