Query plan collection
By default, query plans are collected if the duration for a query is above the value set in the “Query Plan collection threshold” value set in the Settings menu. The default value is 5 seconds. A value of -1 means that query plan collection is globally disabled for all servers.
Query plan collection can be an expensive operation and can use a lot of disk space if many query plans are collected. It is advised to only collect query plans when necessary. Keep an eye on the handle_activity_duration_get_ms column in the dbo.v_execution_info view to see the query plan collection time.
Individual threshold values for servers and databases can be set in the dbo.collection_config table.
Changes to the dbo.collection_config table are immediate and will take effect without a service restart.
The values in the dbo.collection_config table can be:
server_name: mandatory. The query plan collection threshold value will be applied to this server.
database_name: optional. If null, the query plan collection threshold will be applied to the whole server. If not null, the value will be applied only to the given database.
query_plan_collection_threshold_seconds: mandatory: will override the global “Query Plan collection threshold” for a given server and database (optional). A value of -1 means, that the query plan collection will be disabled for the given server and database (optional).
Example 1:
Global “Query Plan collection threshold” = 5
dbo.configuration_table: server_name = server1, database_name = null, query_plan_collection_threshold_seconds = 10
The above means, all servers will have a threshold of 5 except all databases on server1, that will have a threshold of 10.
Example 2:
Global “Query Plan collection threshold” = -1
dbo.configuration_table: server_name = server1, database_name = database1, query_plan_collection_threshold_seconds = 10
The above means, all servers will have query plan collection disabled except the database1 on server1, that will have query plans collected for queries with a duration greater than or equal to 10 seconds.