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.