Медленный sql. Как найти, что вызывает замедление в хранимой процедуре MS SQL

Вводная

Представьте что у вас гигантская хранимая процедура MS SQL, и она в целом медленно работает. 

Как найти медленное место в этой процедуре? 

Решение

Для поиска проблемных мест можно воспользоваться SQL планом в Management Studio. Однако его не совсем удобно использовать, когда выполняется 100+ запросов в процедуре и получается громадное дерево, которое не очень удобно использовать. 

В этом случае можно использовать метод простановки меток по коду и отслеживание сколько времени займет выполнение от прошлой до этой метки.

В хранимой процедуре (или большом блоке sql кода) проставляем такие метки:

declare @dt datetime = getdate()

exec [dbo].as_traceCode @name ='s1', @dt=@dt output
... 
exec [dbo].as_traceCode @name ='s2', @dt=@dt output
...
exec [dbo].as_traceCode @name ='s3', @dt=@dt output

При запуке получим такие принты: 

Это позволит понять где у нас медленные участки кода, провести их оптимизацию и посмотреть новые значения с учетом правок. 

Сама процедура as_traceCode: 

CREATE OR ALTER PROCEDURE [dbo].as_traceCode
	@name nvarchar(256),
	@dt datetime output,
	@updateTime bit = 1
AS
BEGIN
	print  @name + '=' + cast(DATEDIFF(ms,@dt,GETDATE()) as nvarchar)+'ms'
	if(@updateTime=1) set @dt = getdate()
END

Если параметр updateTime, то время не будет обнуляться между метками и просто будет показывать время от начала выполнения кода, а не от прошлой метки.

P.S. Лучше создать копию процедуры, чтобы можно ее было "мучать" как угодно и отслеживать как меняются метрики времени при удалении или изменении частей копии процедуры.