ParameterSampling

Synopsis

[SQL]    ParameterSampling=n

n is either 1 or 0. The default is 0.

Description

When ParameterSampling is enabled (n = 1), InterSystems SQL will save the complete set of query parameters when statements are prepared. This data is projected to INFORMATION_SCHEMA.STATEMENT_PARAMETER_STATS, which includes information on the efficiency of the query. Enabling parameter sampling can be useful for evaluating the efficacy of a schema, especially when testing a new schema.

When ParameterSampling is enabled, the complete set of query parameters is not immediately available in INFORMATION_SCHEMA.STATEMENT_PARAMETER_STATS and their collection may take some time to appear. It may take some time because the statement index information is updated by a system daemon regularly throughout the day. Enabling parameter sampling is not intended to display a live view of your system, but is intended to provide historical information about your queries.

Changing This Parameter

On the SQL page of the Management Portal (System Administration > Configuration > SQL and Object Settings > SQL), select Turn on parameter sampling to sample the parameter value for query execution to enable ParameterSampling.

To set the desired value for ParameterSampling from the InterSystems Terminal, use the SetOption(“ParameterSampling”)Opens in a new tab method of the %SYSTEM.SQL.UtilOpens in a new tab class. See the class reference for details.

You can also change ParameterSampling with the Config.SQLOpens in a new tab class (as described in the class reference) or by editing the CPF in a text editor (as described in Editing the Active CPF).