Abstract query plans can be used to assess the impact of an Adaptive Server software upgrade or system tuning changes on your query plans. You need to save plans before the changes are made, perform the upgrade or tuning changes, and then save plans again and compare the plans. The basic set of steps is:
Enable server-wide capture mode by setting the configuration parameter abstract plan dump to 1. All plans are then captured in the default group, ap_stdout.
Allow enough time for the captured plans to represent most of the queries run on the system. You can check whether additional plans are being generated by checking whether the count of rows in the ap_stdout group in sysqueryplans is stable:
select count(*) from sysqueryplans where gid = 2
Copy all plans from ap_stdout to ap_stdin (or some other group, if you do not want to use server-wide plan load mode), using sp_copy_all_qplans.
Drop all query plans from ap_stdout, using sp_drop_all_qplans.
Perform the upgrade or tuning changes.
Allow sufficient time for plans to be captured to ap_stdout.
Compare plans in ap_stdout and ap_stdin, using the diff mode parameter of sp_cmp_all_qplans. For example, this query compares all plans in ap_stdout and ap_stdin:
sp_cmp_all_qplans ap_stdout, ap_stdin, diff
This displays only information about the plans that are different in the two groups.
