5 Replies Latest reply on Mar 11, 2019 5:40 PM by darichar

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

    wing@szeto.us

      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.

        • Re: DPA showing a high wait query with all info except query text
          darichar

          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.

            • Re: DPA showing a high wait query with all info except query text
              darichar

              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>

            • Re: DPA showing a high wait query with all info except query text
              wing@szeto.us

              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.