#tsql2sday: Start Your Dynamic SQL with a Comment.

T-SQL
6 Comments

When you write dynamic SQL, start like this:

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.

Previous Post
This Week, Fundamentals of Query Tuning Classes are Free!
Next Post
[Video] Office Hours Live: Ask Me Anything

6 Comments. Leave new

  • Great tip!

    Now if only .NET ORM’s would do that 🙂

    Reply
    • Hah. Getting the team to put which application is running the query in the connection string is a great start.

      Reply
  • I like to do this with SSRS, if I’m using a query as well. It allows you to find which report quickly.

    Reply
  • Thanks for this. I will help with tracking bits o fixes!

    Reply
  • 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

    Reply
  • Good point.. 🙂

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.