Our developers are looking at a production issue and want to view a sql query as it is a potential problem. They say it is truncated in Introscope and are asking if there is any way to view the entire query. I know we can increase the maxlength parameter in the agent but is there anywhere where the complete sql query is logged?
Please be careful with and avoid metric explosions when attempting to expose the entire SQL statements.
SQL statements are not logged, so you need to increase the max-length. This will expose more of the SQL-statement, but it will still be normalized (i.e. ? for constant values). If you want to see SQL parameter values also you must change the SQL normalization in the agent profile. In this latter case great care is highly advised as this will cause each individual SQL statement to be captured as a separate metric - also known as metric explosion. A preferable option is to do this in a controlled manner (test/QA environment and/or for a very limited period). Specifically check if your metric count is close to your configured limit as the collector will report overload to the MoM when it hits its metric limit and the MoM will reassign agents for that collector to other collectors that will then in turn realize increased metric counts (because they may not have seen this "redirected" metric before) and you may experience a domino effect if these collectors are also close to their metric limits.
There is an extension that will capture the full SQL query including the parameters in Transaction Traces. None of the problems mentioned by Henrik, all the data you need. Someone should be able to point to you in its direction.
Yes Florian is referring to SQL Agent with Parameters extension which is now a fully supported field pack.
Please open a support issue to get access to the field pack binaries and deployment instructions as needed.
Thanks Kulbir. I will open a support ticket.
How about enabling the property introscope.agent.sqlagent.sql.rawsql .