SQL-Ex blog

Мне бы хотелось иметь легкий неблокирующий способ выполнения отладки T-SQL в продакшене, но отладка в T-SQL отличается от отладки кода С#. Поэтому если ваш код T-SQL делает не то, что вы от него ожидали, вот некоторые лучшие способы его отладки.

Вариант 1: использование операторов PRINT

С давних времен разработчики вставляли такие строчки:

BEGIN TRAN

	PRINT 'Изменение даты начала доступа'

	UPDATE dbo.Users

		SET LastAccessDate = GETDATE()

		WHERE DisplayName = N'Brent Ozar';

	PRINT 'Дата доступа сделана, начало изменения репутации'

	UPDATE dbo.Users

		SET Reputation = Reputation / 0

		WHERE DisplayName = N'jorriss';

	PRINT 'Изменение репутации сделано'

COMMIT

Так что, когда оператор терпит неудачу, они, по крайней мере, видели, в какой части это произошло:



Этот подход имеет несколько недостатков:

• PRINT не сразу выводит данные. SQL Server кэширует данные, которые требуется выводить в сообщениях. Если вы отлаживаете долгоиграющий процесс, то, вероятно, захотите сразу увидеть сообщения, как только он выполнились.


• PRINT передает данные по сети, независимо от того, хотите вы их видеть или нет, добавляя накладные расходы к вашим командам. Это не имеет большого значения для большинства магазинов, но, когда у вас начинает выполняться более 1000 запросов в секунду, вы захотите снизить накладные расходы, где только можно. Вам на самом деле хочется получать отладочные сообщения только тогда, когда они нужны.

Давайте дополним нашу игру командой RAISERROR.

Вариант 2: использование RAISERROR, произносится как raise-roar

Как? Вы не заметили тут ошибки? Ладно, признаюсь, я тоже этого не понимал, — указал мне на это Грег Лоу из SQLDownUnder. Давайте немного усложним наш код:

DECLARE @Debug BIT = 1;

BEGIN TRAN

	IF @Debug = 1

		RAISERROR (N'Starting access date changes', 0, 1) WITH NOWAIT

	UPDATE dbo.Users

		SET LastAccessDate = GETDATE()

		WHERE DisplayName = N'Brent Ozar';

	IF @Debug = 1

		RAISERROR (N'Done with access date, starting reputation changes', 0, 1) WITH NOWAIT

	UPDATE dbo.Users

		SET Reputation = Reputation / 0

		WHERE DisplayName = N'jorriss';

	IF @Debug = 1

		RAISERROR (N'Done with reputation changes', 0, 1) WITH NOWAIT

COMMIT

Я добавил параметр @Debug, и мое сообщение о статусе печатается, только когда @Debug = 1. Сейчас в этом примере мне на самом деле не нужен параметр - но в хранимых процедурах и функциях реального мира вы захотите иметь его, и вы захотите установить для него значение по умолчанию 0, например:

CREATE OR ALTER PROC dbo.DoStuff

   @MyParam VARCHAR,

   @Debug BIT = 0 AS

...

Так образом вы включаете вручную функцию отладки только тогда, когда вам это нужно, и приложение не вызывает @Debug, так что просто сохраняется значение по умолчанию, 0.

Я также переключился на RAISERROR вместо PRINT, поскольку RAISERROR имеет удобный параметр “WITH NOWAIT”, который сообщает SQL Server выдать клиенту сообщение о состоянии прямо сейчас, не дожидаясь пока заполнится буфер.

Когда вы отлаживаете большие или сложные процессы, вам, вероятно, захочется динамически управлять сообщениями о состоянии. Например, есть хранимая процедура, которая выполняется часами, и вы хотите видеть, какие части потребляют наибольшее время. Вы не будете сидеть там с секундомером, и вы не собираетесь возвращаться позже, надеясь, что запросы все еще будут в кэше планов. Вместо этого вы захотите добавить дату/время в сообщение RAISERROR.

К сожалению, RAISERROR не поддерживает конкатенации строк. Поэтому вы должны передать в единой строке все, что вы хотите, например:

DECLARE @Now NVARCHAR(50);

SET @Now = CONVERT(NVARCHAR(50), GETDATE(), 26);

RAISERROR (N'Done with reputation changes at %s', 0, 1, @Now) WITH NOWAIT

Что дает вам дату в конце вывода:



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

Вариант 3: использование табличных переменных

Вы вероятно слышали от меня или других предупреждение, что табличные переменные приводят к плохой производительности. Это справедливо в большинстве случаев - хотя иногда они на самом деле быстрей, как это обсуждалось в классе Основы TempDB. Однако табличные переменные имеют реально крутое поведение: он игнорируют транзакции.

BEGIN TRAN

	DECLARE @Progress TABLE (StatusDate DATETIME2, StatusMessage NVARCHAR(4000));

	INSERT INTO @Progress VALUES (GETDATE(), N'A one');

	INSERT INTO @Progress VALUES (GETDATE(), N'And a two');

ROLLBACK

SELECT * FROM @Progress ORDER BY StatusDate;

Так что, хотя я выполнил откат, а не фиксацию, я все равно получу содержимое табличной переменной:



Это полезно, когда вы:

• Отлаживаете долгоиграющие процессы.


• Ваш процесс имеет логику типа try/catch, begin/commit, когда что-то может сбоить или произойти откат.


• Желаете получить результаты в табличном формате, возможно, даже с многими столбцами, XML, JSON, что-нибудь еще.

Итак, у вас есть это - 3 способа работы над отладкой без использования отключенного отладчика SSMS. Сам я обычно использую RAISERROR - это достаточно легко реализовать, и это техника, которую вы будете использовать всегда. Есть и другие способы, и вы можете рассказать о своих фаворитах в комментариях.

Ссылки по теме

1. Освоение TempDB: основы


2. О хранилище запросов, навязывании плана и табличных переменных


3. Вызов исключений и обработка ошибок с помощью THROW в SQL Server