Synpase SQL Tip 2 – Comments in Views/Procs

Synapse SQL Dedidcated Pools (aka SQLDW) does not support comments in views or procs in the same was as standalone SQL. This is annoying as comments can be very useful with tracking changes and lineage of objects, especially when used in conjunction with Schema Compare tools in Visual Studio.

Everyone has seen that incident where somehow PRD has a different View than DEV, and comments help us see why that is the case and if its a forward port neded to fix or back port due to some naughty developer or some panic Hot Fix direct to PRD.

Comments for Views

In SQLDW, you can add comments into a statement, so what we have started doing at Prodata is adding comments after the first WORD of the statement on View

For example here is a handy view which is work in progress on my attempt at request aggregation for sp_whoIsActive style analysis of queries in SQLDW.

CREATE VIEW [dbo].[Requests] AS WITH
/*
	Description: Return Data for SQLDW Requets, Steps, Operations in summary from
	used By:	 sp_WhoIsActive, sp_WhoWasActive
	History:	 11/04/2021 Bob, Created for Perf Tuning
*/
requests as 
	(
		select row_number() over (order by r.request_id desc ) as [#] ,   r.request_id, r.session_id, r.command 
		, r.submit_time, r.start_time, r.total_elapsed_time
		, r.status
		, r.resource_class
		, getdate() as collection_time 
		, s.login_name
		, s.app_name
		, s.client_id
		, s.sql_spid
		from sys.dm_pdw_exec_requests r
		INNER JOIN sys.dm_pdw_exec_sessions s on s.session_id=r.session_id
		WHERE r.session_id <> SESSION_ID ( ) 
		AND app_name <> 'Microsoft SQL Server Management Studio'
	),	steps as (
			select   request_id, operation_type , total_elapsed_time, SUBSTRING(command,1,(CHARINDEX(' ',command + ' ')-1)) as command
			From sys.dm_pdw_request_steps
			WHERE location_type='Compute'
	)
	select TOP 5000 r.[#], r.request_id,
	r.start_time,	r.status,  r.total_elapsed_time as request_time
	, m.total_elapsed_time as step_time
	, r.command
	, m.steps, op.op_commands, m.operations
	, r.session_id
	, r.submit_time

		, r.resource_class
		, r.collection_time 
		, r.login_name
		, r.app_name
		, r.client_id
		, r.sql_spid
	from requests r
	LEFT JOIN (
		SELECT request_id, string_agg(command,',') WITHIN GROUP (ORDER BY total_elapsed_time DESC) as op_commands
		FROM (
			SELECT request_id, command + '(' + convert(varchar,count(*)) + ':' + convert(varchar,sum(total_elapsed_time)) +'ms)' as command , sum(total_elapsed_time) as total_elapsed_time
			FROM STEPS
			WHERE operation_type='OnOperation'
			group by request_id,command
		) op1
		GROUP BY request_id
	) op on op.request_id= r.request_id
	LEFT JOIN (
		SELECT request_id, string_agg(operation,',') WITHIN GROUP (ORDER BY total_elapsed_time DESC) as operations, count(*) as steps, sum(total_elapsed_time) as total_elapsed_time
		FROM (
			SELECT request_id, replace(operation_type,'Operation','') + '(' + convert(varchar,count(*)) + ':' + convert(varchar,sum(total_elapsed_time)) +'ms)' as operation, sum(total_elapsed_time) as total_elapsed_time, count(*) as steps
			FROM STEPS
			group by request_id,operation_type
		) m1
		GROUP BY request_id
	) m on m.request_id= r.request_id
	order by r.submit_time desc;
GO

Comments for SProcs

Stored Procedures are different in that you can add the comment after the AS, but not before the ALTER/CREATE command like in standalone SQL

ALTER PROC [dbo].[sp_WhoWasActive] AS
/*
	Desc: Return Running Statements
	History: 29/03/2021 Bob, Created
*/
BEGIN
    SET NOCOUNT ON;
	
	SELECT TOP 500 * FROM Requests 
	ORDER BY submit_time desc 

END

Of course, like Brent Ozar recently blogged, never ever ever use the double line flavour of comments. This is true on all flavours of SQL.

https://www.brentozar.com/archive/2021/04/never-ever-ever-start-t-sql-comments-with-two-dashes/

Leave a Reply