We are using DPA version 12.0.3083. We are tracking a performance issue an hour earlier and found a few queries having some wait time. Using the drill-down, we are able to see the query context except one. This particular one would only show 'Sql Statement not available'. From other DPA info, we know it is a query from our web server but why it can't show the query context. Can someone shred some light on this? This doesn't happen often to us. In fact, we haven't encountered this 'sql statement not available' for a long while.
Thanks for replying. The query that in question is a frequently executed one like at least 100 times per hour. Would your explanation still apply?
More details here, during the troubleshooting time, when DPA reported a high wait time under a particular session ID, there were also various other queries running under the same session ID. Since there was one particular one which ran the longest (5~7 secs) (the frequently run one), I assume this was the one DPA trying to capture and display the query text. There were a few others by this session ID run under 1 or 2 secs. If your reason for DPA not able to display a query text is for non-frequently run query, my situation here would have to be for another query DPA trying to display a query context not the longest one and frequently run query I found.
DPA collects wait time information once per second, but only collects the SQL Hash for the statement in those polls. Once every 2 minutes, DPA will go back to collect SQL text for anything new. My guess is the query was aged out of the cache and DPA was not able to get it in time. This can happen for a query that runs just once or very few times. If the query executes again or executes with some regularity, DPA should eventually collect the text. If the query never executes again, it's just one of those timing issues that can happen unfortunately.
There is another option you can use if this query executes more often. You can have DPA log the sql_handle value for this SQL and then query the SQL Server DMVs to see if the sql text or statistics are available. To do this, follow these steps:
Retrieve SQL text
select SUBSTRING(text, (<statement_start_offfset>/2)+1,
WHEN -1 THEN DATALENGTH(st.text)
END - <statement_start_offset>)/2) + 1) AS statement_text
from sys.dm_exec_sql_text (<sql_handle>)
Retrieve SQL Statistics
select * from sys.dm_exec_query_stats
where sql_handle = <sql_handle>
and statement_start_offset = <statement_start_offset>
and statement_end_offset = <statement_end_offset>
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process. Learn more today by joining now.