Submit a ticketCall us

Training ClassThe Orion® Platform Instructor-led Classes

Provided by SolarWinds® Academy, these trainings will introduce users to the Orion Platform and its features, management, and navigation. These courses are suitable for users looking to discover new tips, tricks, and ways to adapt their Orion products to better suit their monitoring needs:
Deploying the Orion Platform
Configuring Orion views, maps, and accounts
Configuring Orion alerts and reports

Reserve your seat.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > "SQL Text" for long SQL Statement Text truncated due to excessive length

"SQL Text" for long SQL Statement Text truncated due to excessive length

Table of contents

Updated 11/30/2017

Overview

DPA will truncate SQL text after a certain length. For the SQL server, historically the length was 7999 characters. As of DPA version 11.0 a support option was added to extend this SQL text length on each server that DPA is monitoring. 
SQLTEXT_SQLSERVER_MAX_TEXT_LENGTH description: "Max length of SQL text fetched from SQL Server 2005+ or Azure SQL DB (max = 49999)."   

Even with this longer parameter, DPA will not recollect text that was collected and truncated before. DPA does the quick poll query to see what is active one time per second. A hash value is created for the SQL statement. If it is determined that DPA does not have the text of the SQL statement for that hash, it will collect it. If DPA does have the SQL text, it does not recollect thing information. SQL statements that were collected and truncated are not recollected even if the longer length parameter is set.

This article details a procedure for setting DPA to collect longer text, and how to try to get it to recollect the SQL with more characters. 

Environment

  • DPA version 11.0 and later.

Steps

  1. In DPA, set the Options menu item to a longer length: Options > Administration tab > Advanced Options > DB Instance Options tab > Support Options check box > Select the SQL server instance from the drop down menu.
  2. Look for SQLTEXT_SQLSERVER_MAX_TEXT_LENGTH and increase this to a value up to 49999.
  3. If you want DPA to recollect the SQL text at the new longer length, you have to delete the SQL text from the DPA repository to get the longer text on items that were collected before. If you run "select ID, name from COND", you will see the IDs of the monitored instances. The table for the SQL text would be CONST_ID, where ID is the ID for the server where you have truncated text. You would need to either truncate those CONST_ID tables, or run "DELETE from CONST_ID where H = <hash I need to be recollected at longer length>" 
  4. Restart DPA.
  5. Wait for the SQL statement to run again on the monitored server.  

 

 

 

 

Last modified

Tags

Classifications

Public