DPA showing a high wait query with all info except query text

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:

1. Click on Options and Advanced Options
2. Click on the DB Instance Options tab and select the instance where you are seeing this issue. Also check the Support Options checkbox
3. Click on the LOG_SQLWAIT_INFO_FOR_SQLHASH option to edit the setting. Add the hash value for the SQL without text
4. The next time DPA sees the query, it will log the sql_handle and other into the idc.log file on the DPA server machine. By default, this file is located in the C:\Program Files\SolarWinds\DPA\iwc\tomcat\logs directory
5. Search this file for the hash value and you should find an entry for it that includes the SQL handle (add "0x" in front of the value in log file), statement_start_offset and statement_end_offset
6. Once you have that value, you can run this query to get the SQL text if it's available:

Retrieve SQL text

select SUBSTRING(text, (<statement_start_offfset>/2)+1,  

        ((CASE <statement_end_offset>

          WHEN -1 THEN DATALENGTH(st.text) 

         ELSE <statement_end_offset>

         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>

I would also suggest you upvote this item

It would help as you then wouldn't have to do all the extra logging steps to get this data to see what is happening here. 

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.

Is this a SQL Server instance where you see this? If so, have you tried any of the suggestions above to capture the sql_handle?