When server-wide capture mode is enabled, plans for all queries that can be optimized are saved in all databases on the server. Some possible system administration impacts are:
When plans are captured, the plan is saved in sysqueryplans and log records are generated. The amount of space required for the plans and log records depends on the size and complexity of the SQL statements and query plans. Check space in each database where users will be active.
You may need to perform more frequent transaction log dumps, especially in the early stages of server-wide capture when many new plans are being generated.
If users execute system procedures from the master database, and installmaster was loaded with server-wide plan capture enabled, then plans for the statements that can be optimized in system procedures are saved in master..sysqueryplans.
This is also true for any user-defined procedures created while plan capture was enabled. You may want to provide a default database at login for all users, including System Administrators, if space in master is limited.
The sysqueryplans table uses datarows locking to reduce lock contention. However, especially when a large number of new plans are being saved, there may be a slight impact on performance.
While server-wide capture mode is enabled, using bcp saves query plans in the master database. If you perform bcp using a large number of tables or views, check sysqueryplans and the transaction log in master.
