This section describes two methods for finding queries impacted by new query optimization techniques in Adaptive Server 15.0.
Abstract query plans capture, load, and reuse query plans for executing queries. By default, captured query plans are stored in the sysqueryplans table. Both Adaptive Server 12.5.2 and 15.0 use query hash keys, so the query plans for identical queries can be matched.
This is an overview of the steps for capturing query plans:
Enable query plan capture on the 12.5 server. Do this at the session level with set plan dump on, or at the server level with sp_configure "abstract plan dump", 1.
On the 12.5 server, execute one module of the application you want to test.
Disable abstract query plan dump and bcp out sysqueryplans.
Enable query plan capture on the 15.0 server (use the same process as in step 1).
On the 15.0 server, execute the same module you executed in step 2.
Disable abstract query plan dump on the 15.0 server.
In the 15.0 server, create a table named queryplans_125.
bcp in the data from the 12.5 server into queryplans_125.
On the 12.5 server, copy the 15.0 data into the test database on the 12.5 server. Use select into to create a table called queryplans_150.
Create an index on hashkey, type, and sequence for both tables.
Run some of the sample queries listed below on both servers to identify plan differences. You can add to the complexity of these queries and eliminate the duplicates.
This generates a list of the queries that have changed:
select t.hashkey into #qpchgs from queryplans_125 t, queryplans_150 f where t.hashkey = f.hashkey and t.sequence = f.sequence and t.type = 100 -- aqp text vs. sql and f.type = 100 -- aqp text vs. sql and t.text != f.text union all select f.hashkey from queryplans_150 f where f.sequence not in (select t.sequence from queryplans_125 t where f.hashkey = t.hashkey) union all select t.hashkey from queryplans_125 t where t.sequence not in (select f.sequence from queryplans_150 f where f.hashkey = t.hashkey) go
This example eliminates duplicates:
select distinct hashkey into #qpchanges from #qpchgs go drop table #qpchgs go
This example captures the SQL text for the queries identified:
select t.hashkey, t.sequence, t.text from queryplans_125 t, #qpchanges q where q.hashkey=t.hashkey and t.type = 10 -- sql text vs. aqp
To compare the abstract query plan text, first determine which 15.0 query plans may be longer:
select t.hashkey, t.sequence, t.text, f.sequence, f.text from ueryplans_125 t, queryplans_150 f where t.hashkey = f.hashkey and t.sequence=*f.sequence and t.hashkey in (select hashkey from #qpchanges) and t.type = 100 -- aqp text vs. sql and f.type = 100 -- aqp text vs. sql union all
Find the query that has a longer query plan in the 12.5 server (this may cause duplicates with the result set for the example above where the query plans for the 12.5. and the 15.0 server are equal):
select t.hashkey, t.sequence, t.text, f.sequence, f.text from ueryplans_125 t, queryplans_150 f where t.hashkey = f.hashkey and t.sequence*=f.sequence and t.type = 100 -- aqp text vs. sql and f.type = 100 -- aqp text vs. sql order by t.hashkey, t.sequence, f.sequence
Often an application executes the same query more than once. If this happens, and the query plans differ between 12.5 and 15.0 servers, the query results in duplicates in the result set of the last example.
None of these queries capture the execution metrics,
so you cannot tell by looking at the output whether the query plans
change the performance characteristics.
This method for finding queries impacted by new query optimization techniques does not uniquely identify a query within the monitoring tables using the hashkey. This method relies on the monSysStatement monitoring table and related pipes, and uses only the monSysStatement, monSysSQLText, and monSysPlanText monitoring tables, and not the monProcessStatement, monProcessSQLText, and monProcessPlanText monitoring tables. Monitor tables that start with monSys... are “stateful” and keep track of historically executed statements, but tables that start with monProcess record only the currently executing statement.
Although the context of your connection’s pipe state is retained for the current connection, it is independent of other connections (allowing multiuser access to the monitoring table historical data). For example, if you query the monSysStatement table and get a result of 100 rows, the next time you query this table, you see only the new rows added since the last query finished. However, if another DBA connects at this point and queries monSysStatement, his or her result set contains all the rows.
If you sample the monitor tables repeatedly using a polling process, you are viewing only the change in the table since your last query. If you reconnect, the session appears as a new session and the original state is lost, so the result set may contain rows already provided in the previous session.
Size the pipes correctly for your configuration. If the pipes are too small, statements or queries may be dropped from the ring buffer, and you must increase the number of available pipes or run sample queries more frequently. For example, if a module of an application submits 100,000 statements, setting the pipes to 100,000 may be impossible because of memory constraints. However, if you know these statements are issued over the period of an hour, then an average of 1,667 queries per minute are issued. If you guess at a peak of double, then 3,333 queries per minute are issued. In this situation, it is useful to set the pipes to 5,000 and sample every minute to avoid losing statements.
These are the general steps for finding queries impacted by new query optimization techniques using monitoring tables:
Configure the pipe setting in the 12.5 server for statement pipe active, sql text pipe active, and statement plan text pipe.
Issue a query similar to the following to create a temporary repository in tempdb for the monSysStatment, monSysSQLtext, and monSysPlanText tables:
select * into tempdb..monSysStatement from master..monSysStatement where 1=2
Create a monitoring process that queries the monSysStatment, monSysSQLtext, and monSysPlanText tables once every minute and inserts the result set into the temporary tables you created in step 2. For example:
insert into tempdb..monSysStatement (select * from master..monSysStatement)
To run this once every minute, you can place the query in
a loop with waitfor delay "00:01:00".
Execute one module of the application to be tested.
Stop the application and halt the monitoring.
bcp out the monitoring table you collected in step 3.
Repeat steps 1 – 5 on Adaptive Server 15.0.
Create a set of tables in the test database named after the monitoring tables but include version information in their names. For example, monSysStmt125, monSysStmt150, and so on.
Load the tables you created in step 8 with the information you collected in step 3 (use either bcp or an insert...select statement).
Create an index on the monSysStmt125 and monSysStmt15 tables on the SPID, KPID, DBID, ProcedureID, BatchID, ContextID, and LineNumber columns.
You can now search for queries that are impacted by the 15.0 server optimization techniques using the monSysStmt125 and monSysStmt15 tables.
Consider the following while you investigate queries:
SQL queries (as opposed to triggers or procedures) have a ProcedureID value of 0.
You can uniquely identify queries for a user with the SQL batch, which are counted in order by BatchID. The first SQL batch sent by an SPID is given a BatchID of 1, the second BatchID of 2, and so on.
Within each SQL batch, the context (or “nesting level”) may change as triggers, procedures, or subprocedures are executed. The context may increment as the nesting increases and decrement as the execution drops from the nested procedure.
Line numbers refer to the statement within the current context.
This technique is accurate only within 100 milliseconds because it is based on the Adaptive Server CPU tick length, which defaults to 100 milliseconds. Statements that execute in less time than 100 milliseconds display as 0. This can cause a reporting error if, for example, an insert that took 10 milliseconds now takes 20 milliseconds, particularly if this insert is executed 1,000,000 times a day.
If you issue the same sequence of test statements, monSysStatements may be identical in both servers with the exception of the starting BatchID. You can use the SPID and KPID combinations on both server to identify the queries (assuming the application uses multiple connections).
For example, this query selects a list of SQL statements that executed more slowly on the 15.0 server than the 12.5 server. On the 12.5 server, the statements have SPIDs of 123, KPIDs of 4567890, and their BatchIDs started with 101; on the 15.0 server the statements have SPIDs of 24, KPIDs of 1234567, and their BatchID started with 12:
select f.BatchID, f.ContextID, f.LineNumber, CPU_15=f.CPUTime, CPU_125=t.CPUTime, Wait_15=f.WaitTime, Wait_125=t.WaitTime, Mem_15=f.MemUsageKB, Mem_125=t.MemUsageKB, PhysIO_15=f.PhysicalReads, PhysIO_125=t.PhysicalReads, LogicalIO_15=f.LogicalReads, LogicalIO_125=t.LogicalReads, Writes_15=f.PagesModified, Writes_125=t.PagesModified, ExecTime_15=datediff(ms,f.StartTime,f.EndTime)/1000.00, ExecTime_125=datediff(ms,t.StartTime,t.EndTime)/1000.00, DiffInMS= datediff(ms,f.StartTime,f.EndTime)- datediff(ms,t.StartTime,t.EndTime) into #slow_qrys from monSysStmt150 f, monSysStmt125 where f.SPID=24 and f.KPID=1234567 and t.SPID=123 and t.KPID=4567890 and t.BatchID=f.BatchID+(101-12) -- calculate offset for Batches. and t.ContextID=f.ContextID and t.LineNumber=f.LineNumber and (datediff(ms,f.StartTime,f.EndTime) > datediff(ms,t.StartTime,t.EndTime)) order by 18 desc, f.BatchID, f.ContextID, f.LineNumber
You can edit this same query to return the queries that run faster on a 15.0 server than a 12.5 server:
select f.BatchID, f.ContextID, f.LineNumber, CPU_15=f.CPUTime, CPU_125=t.CPUTime, Wait_15=f.WaitTime, Wait_125=t.WaitTime, Mem_15=f.MemUsageKB, Mem_125=t.MemUsageKB, PhysIO_15=f.PhysicalReads, PhysIO_125=t.PhysicalReads, LogicalIO_15=f.LogicalReads, LogicalIO_125=t.LogicalReads, Writes_15=f.PagesModified, Writes_125=t.PagesModified, ExecTime_15=datediff(ms,f.StartTime,f.EndTime)/1000.00, ExecTime_125=datediff(ms,t.StartTime,t.EndTime)/1000.00, DiffInMS= datediff(ms,f.StartTime,f.EndTime)-datediff(ms,t.StartTime,t.EndTime) into #slow_qrys from monSysStmt150 f, monSysStmt125 where f.SPID=24 and f.KPID=1234567 and t.SPID=123 and t.KPID=4567890 and t.BatchID=f.BatchID+(101-12) -- calculate offset for Batches. and t.ContextID=f.ContextID and t.LineNumber=f.LineNumber and (datediff(ms,f.StartTime,f.EndTime) < datediff(ms,t.StartTime,t.EndTime)) order by 18 desc, f.BatchID, f.ContextID, f.LineNumber
This method uses sysqueyrmetrics (which includes performance metric columns including logical IOs, CPU time, elapsed time, and so on) to find queries that are impacted by the 15.0 query processor. The method uses exact query matching, which is based on the hash key but yields execution statistics.
This method displays the query text but not the query plan, so you must first identify the slow queries in the 15.0 server and then compare the plans to those of the earlier server. The first part of this method is similar to the method that captures abstract query plans, but then uses sysquerymetrics to capture more information.
See the Reference Manual: Tables for a complete description of the sysquerymetrics table.
The steps are:
Enable query plan capture on the 12.5 server. You can do this at the session level with set plan dump on, or at the server level with sp_configure "abstract plan dump", 1.
Execute one module of the application to be tested on the 12.5 server.
Disable abstract query plans dump (set plan dump off) and bcp out sysqueryplans on the 12.5 server.
Enable query plan capture on the 15.0 server with set plan dump group_name on.
Enable metrics capture on the 15.0 server. You can do this at the server level with sp_configure "enable metrics capture", 1 or at the session level with set metrics_capture on.
Execute the same module from step 2 on the 15.0 server.
Disable abstract query plans dump (set plan dump off) on the 15.0 server.
On the 15.0 server, create a table named queryplans_125 in your test database; bulk-copy in the 12.5 data.
Create a table on the 15.0 server named queryplans_150 in your test database, and copy the 15.0 server abstract query plan data into this table.
Create an index on the hashkey, type and sequence columns for both abstract query plan tables.
Use sp_metrics to back up the sysquerymetrics data or copy it to table in the test database.
Run the following queries to identify plan differences.
This query creates a list of queries that have changed for the 15.0 server:
select t.hashkey into #qpchgs from queryplans_125 t, queryplans_150 f where t.hashkey = f.hashkey and t.sequence = f.sequence and t.type = 100 -- aqp text vs. sql and f.type = 100 -- aqp text vs. sql and t.text != f.text union all select f.hashkey from queryplans_150 f where f.sequence not in (select t.sequence from queryplans_125 t where f.hashkey = t.hashkey) union all select t.hashkey from queryplans_125 t where t.sequence not in (select f.sequence from queryplans_150 f where f.hashkey = t.hashkey) select distinct hashkey into #qpchanges from #qpchgs go drop table #qpchgs
This query selects a list of the queries that have changed for 15.0 and are running slowly. This query does not tell you if the queries ran faster or slower in the 15.0 server; it identifies queries in the 15.0 server that exceed a specified limit and whose query plans have changed from the 12.5 server release:
select hashkey, sequence, exec_min, exec_max, exec_avg, elap_min, elap_max, elap_avg, lio_min, lio_max, lio_avg, pio_min, pio_max, pio_avg, cnt, weight=cnt*exec_avg qtext from <db>..sysquerymetrics -- database under test where gid = <gid> -- group id sysquerymetrics backed up to and elap_avg > 2000 -- slow query is defined as avg elapsed time > 2000 and hashkey in (select hashkey from #qpchanges)
