Performance Monitoring for Prepared SQL?

Question

My company is looking into changing to full use of prepared SQL across the board for some applications. For a few days, I've been trying to answer the question of "Do we have a way to monitor for performance" such as seeing a SQL transaction having high duration/CPU/IO actions and seeing what it was doing to produce that high load. Much of what I've come across has been concerns that this is unnecessary/obsolete in Microsoft SQL Server. I've raised concerns about this but in the mean time I'm still trying to come up with a way to track activity.

The best I've identified is that we can have our standard performance monitoring that we've always had, but we will also need a sort of "magic decoder ring" trace/XE session. This would have to track Prepare/Unprepare SQL actions to get the handle, and the associated statement completed messages to get what the associated query is for each SPID. Then if we encounter in our performance monitoring a transaction saying something like "sp_execute 32 etc etc..." we would look at the SPID requesting this action, take that handle # 32 (or whatever it is) and look at our magic decoder ring to figure out what the parameterized SQL statement is that is being run for analysis.

The answer we would be using currently needs to support SQL2008R2 and up (we have many hundreds of this version deployed at present as we are starting to move to next gen), which is why I mention SQL trace as we still utilize this over XE for many deployments due to the limitations of XE before SQL2012.

We will still be looking at whether or not prepared SQL is a good idea for us, as some new information coming up in research suggests it may not be. I'm simply asking if there is a clean way to directly monitor actions done via prepared SQL without such a cumbersome "magic decoder ring" method for keeping track of what the prepared SQL is.

Answer 1

You would most likely need to trap statement-level events since the "completed" RPC call for sp_execute isn't going to show the batch itself.

Also, you need more than just the SPID. Session IDs get re-used, hence it is not granular enough of a key. You will also need the Connection ID since that is more unique and is what the cached array of prepared statements is tied to.

Finally, what is the impetus for moving to prepared statements? The only benefit of them is saving some number of bytes in the network request if your app is frequently sending over the same parameterized queries. It is generally better to call Stored Procedures so that you are not sending over the queries each time anyway. I did recently here someone suggest that prepared statements could be faster than Stored Procedures, but it was a very specific use-case: simple DML statements executed at least 1 - 2 BILLION times per day (i.e. the same INSERT INTO dbo.Table (@1, @2, @3); executed 2 billion times with different values).

If you are executing non-parameterized ad hoc queries, then using prepared statements is worthless at best, and at worst it is a step backwards given how much harder they are to monitor.

Please also see my answer, also here on DBA.SE, to the following question:

What is the sense and benefit of using SqlCommand.Prepare()?