[Перевод] Три способа отладки T-SQL кода09.05.2023 02:01

Написание нового кода = ошибки. С этим всё просто.

Избавится от ошибок — вот это сложная задача.

Программисты привыкли, что в их средствах разработки есть встроенные инструменты, показывающие, какая строка кода сейчас работает, отображают текущее содержимое переменных, выводят сообщения о процессе выполнения и тд. Какое-то время в SQL Server Management Studio тоже был отладчик кода, но, начиная с версии SSMS v18, он был удален. Хотя даже когда отладчик был, я не фанател от него: SQL Server буквально мог прекратить обработку других запросов, пока выполнял ваш запрос. Это была катастрофа, особенно когда ваш запрос блокировал других пользователей, и всё это происходило на рабочей базе.

Мне бы хотелось, чтобы у нас был простой способ отладки T-SQL на рабочей базе без блокировок, но отладка T-SQL отличается от отладки в C#. Так что если ваш T-SQL код делает не то, что вы ожидали, вот несколько хороших способов для его отладки.

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

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

BEGIN TRAN
	PRINT 'Starting access date changes'

	UPDATE dbo.Users
		SET LastAccessDate = GETDATE()
		WHERE DisplayName = N'Brent Ozar';

	PRINT 'Done with access date, starting reputation changes'

	UPDATE dbo.Users
		SET Reputation = Reputation / 0
		WHERE DisplayName = N'jorriss';

	PRINT 'Done with reputation changes'
COMMIT

Чтобы видеть в какой части кода возникла ошибка, когда он упадет:

У этого подхода есть пара проблем:

PRINT не выводит информацию мгновенно. SQL Server кэширует информацию, которая должна быть выведена в Сообщениях. Если вы отлаживаете длительный процесс, скорее всего вам бы хотелось увидеть сообщения об ошибках мгновенно, как только они возникают.
PRINT передает данные по сети, хотите вы того или нет, увеличивая накладные расходы ваших запросов. Это не играет большой роли до тех пор, пока вы не превысили 1,000 запросов в секунду, после этого вам захочется везде сократить расходы, где это только возможно. Вы ведь в действительности хотите видеть отладочные сообщения только тогда, когда они вам нужны.

Давайте попробуем улучшить нашу отладку с помощью RAISERROR.

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

Что? Вы не знали, что это слово произносится по-другому? Ладно, открою секрет, я тоже не знал, как оно произносится — Грег Лоу (Greg Low) из 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 не поддерживает объединение строк. Поэтому вы должны передать в 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

И это позволит вам получить дату и время на выходе:

Вы можете даже передавать несколько аргументов — тут можно найти больше информации о том, как работает RAISERROR.

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

Возможно, вы слышали от меня или других разработчиков, что использование табличных переменных понижает производительность. В большинстве своем это правда –, но иногда они работают очень быстро, как раз об этом мы говорили в этом курсе Fundamentals of 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 Debugger. Я обычно использую RAISERROR — это достаточно легко реализовать и этим механизмом будут пользоваться вечно. Конечно же, вариантов отладки гораздо больше, делитесь вашими любимыми способами в комментариях.