When you write dynamic SQL, start like this:
CREATE OR ALTER PROC dbo.MyProc AS BEGIN DECLARE @StringToExecute NVARCHAR(4000); SET @StringToExecute = N’SELECT /* MyProc */ ‘; …
Right after the SELECT (or INSERT or UPDATE or whatever), immediately put a comment – using /*, of course, because you’re not a terrible person.
That way, when you’re looking at the plan cache or monitoring tools, you can see what generated the dynamic SQL, and where you need to go if you need to performance tune or fix it.
You can’t put the comment at the very beginning because SQL Server tracks the