Return
-
Title
Removing SQL Text is out of Batch Size messages from the SQL PI cache -
Description
PI extension is enabled
SQL Text is truncated under SQL Performance panel
Message displayed is "SQL text is out of the collected batch configured size" for historically collected SQL statements in SQL PI using a SQL Server PI repository.
-
Cause
By default, the SQL text limit is limited to 16,300 characters because of product performance considerations. It is set by the parameter: paecResolverBatchTextLimit and can however this threshold can be increased, as long as the user understands that performance might be impacted.After the batch text limit size has been increased, historical SQL Statements in the SQL Server PI repository may still include this message. -
Resolution
Restart the FglAM for the database agents and SQL PI repository agent.Run the following script in the SQL Server PI repository database using a query utility like Quest Toad or SQL Server Management Studio to count the number of rows affected. Replace the value SPIMSSQL with the name of the SQL Server PI repository database.SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p0] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p1] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p2] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p3] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p4] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p5] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p6] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p7] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p8] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p9] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p10] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p11] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p12] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p13] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p14] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p15] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p16] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p17] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p18] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p19] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p20] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p21] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p22] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p23] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p24] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p25] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p26] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p27] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p28] where sql_short_text like 'SQL text is out of the collected batch configured size%' SELECT count(*) from [SPIMSSQL].[dbo].[pass_syntax_dim_p29] where sql_short_text like 'SQL text is out of the collected batch configured size%'Then run the following script in the SQL Server PI repository database using a query utility like Quest Toad or SQL Server Management Studio to delete the rows with the message beginning with "SQL test is out of the collected batch configured size". Replace the valus SPIMSSQL with the name of the SQL Server PI repository database.
delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p0] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p1] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p2] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p3] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p4] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p5] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p6] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p7] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p8] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p9] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p10] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p11] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p12] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p13] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p14] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p15] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p16] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p17] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p18] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p19] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p20] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p21] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p22] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p23] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p24] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p25] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p26] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p27] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p28] where sql_short_text like 'SQL text is out of the collected batch configured size%' delete from [SPIMSSQL].[dbo].[pass_syntax_dim_p29] where sql_short_text like 'SQL text is out of the collected batch configured size%'Restart the FglAM for the database agents and SQL PI repository agent.NOTE: It also takes 24 hours from after the batch text size change for the data from the 1m tables to be moved into the pass_syntax_dim_p* tables. If the batch text is/was changed within 24 hours of the delete statements then it should be run again after the 24 hours to remove any lingering rows.The information in the script(s) provided is known to work successfully; however, they have not been officially tested by our Quality Control.If any of these instructions are changed and/or incorrectly used, intentionally or unintentionally, this solution becomes unsupported by our Support and Development.Support and Development recommend always making a backup of the current database prior to execution of any script(s) that may modify it.For customization of the Foglight, please contact our Professional Services Organization -
Defect ID
SSDB-13703 -
Attachments