When you write dynamic SQL, start like this:
|
1 2 3 4 5 |
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 exact point at which your query begins, and that’s what shows up in most plan cache or monitoring tools. If you put the comment immediately after the first word of the query, however, it’s easy to spot the query’s source.
This becomes even more important in SQL Server 2022 because of the PSPO implementation of cached plans.
6 Comments. Leave new
Great tip!
Now if only .NET ORM’s would do that 🙂
Hah. Getting the team to put which application is running the query in the connection string is a great start.
I like to do this with SSRS, if I’m using a query as well. It allows you to find which report quickly.
Thanks for this. I will help with tracking bits o fixes!
Great idea! One of my clients has about two dozen sprocs with dynamic SQL, and I’m always wondering where the SQL came from when troubleshooting. In cases where there is more than one dynamic SQL statement in a sproc, putting the sproc name plus a number might help identify the offending code.
Cheers,
Mike
Good point.. 🙂