Aggregating Metrics from Syntactically Similar Queries

You can aggregate monitoring data for multiple statements in the statement cache. Although the statements are syntactically distinct—having individual comments or differences in their text format—represent semantically identical queries.

By providing a way to identify semantically equivalent statements, the show_condensed_text function can be used to aggregate the values from the monCachedStatement table into a single row in the query result.

monCachedStatement stores detailed monitoring information about the statement cache, including:

Resources used during previous executions of a statement,
How frequently a statement is executed
The settings in effect for a particular plan
The number of concurrent uses of a statement

This information can be helpful when you are troubleshooting, and when you are deciding which statements to retain in the cache. Although you can use show_cached_text, to view logically identical SQL statements that include nonunique SQL text, you may have difficulty identifying them in the output, especially if such statements are numerous. For example, if you have three queries that start with this text:

select * from db1.tableA where col1 in (?,?,?) additional_comment
select * from db1.tableA where col1 in (?,?,?) different_additional_comment
select * from db1.tableA where col1 in (?,?,?) different_additional_comment

Each query creates a separate entry in the global statement cache, compiles its own query plan, and produces three different SQL texts when you select from monCachedStatement.

However because these three queries have different SSQLIDs, metrics are distributed across multiple entries, and are reported separately in the top SQL statements monitor, resulting in:

Expensive, but logically identical, SQL statements being unrealized, because they end up in multiple SSQLIDs, each of which shows only a part of the total elapsed time
Logically identical SQL statements that use different query plans

Use show_condensed_text to group the statements reported in the monCachedStatement so the metrics for semantically identical statements can be aggregated together.

show_condensed_text uses these rules to condense SQL text (including SQL text larger than 16KB):

Removes all comments and plan hints.
Changes all keywords to upper case. However, object identifiers (for example, table names, column names, and so on) remain unchanged. For example, this statement:
```
select id from sysobjects
```
is condensed to:
```
SELECT id FROM sysobjects
```
Adds correlation names (using the format T1, T2, and so on) if the query contains multiple tables. For example, this statement:
```
select error, id from sysmessages, sysobjects where id = error
```
is condensed to:
```
SELECT T1.error, T2.id FROM sysmessages T1, sysobjects T2 WHERE T2.id = T1.error
```
Removes spaces:
- Between tokens – keeps one space between tokens. All other spaces (for example, return characters, newline characters, tab characters, and so on) are removed. For example, the spaces in this statement are removed:
```
select     name from sysdatabases 
```
  and the statement is condensed to:
```
select name from sysdatabases 
```
- From parenthesis – removes all spaces after the left parenthesis and before the right parenthesis. For example, this statement:
```
select * from sysdatabases where name in ( ( select name from sysdevices ) )
```
  is condensed to:
```
SELECT * FROM sysdatabases WHERE name IN ((SELECT name FROM sysdevices))
```
- Before and after operators – keeps a single space before and after operators. For example, this statement:
```
select error from sysmessages where error> 100
```
  is condensed to:
```
SELECT error FROM sysmessages WHERE error > $
```
Replaces all literals with $, regardless of their placement. For example, this statement:
```
select name = ”mydb” from sysdatabases where name = ”master”
```
is condensed to:
```
SELECT name = $ FROM sysdatabases WHERE name = $
```
Reduces all in-lists to a single parameter. For example, this list:
```
IN(100, 200, 300, 400)
```
is condensed to:
```
IN($)
```
However, if the in list:
- Does not contain a subquery – show_condensed_text reduces the in-list to a single parameter. For example, this query:
```
select name from sysdatabases where name in(“master”, “tempdb”, “model”)
```
  is condensed to:
```
SELECT name FROM sysdatabases WHERE name in ($)
```
- Contains a subquery – show_condensed_text leaves the subquery, but other literals in the in lists are replaced with a $. For example, this query:
```
select * from sysdatabases where name in (“master”, (select name from sysdatabases), “tempdb”)
```
  is condensed to:
```
select * from sysdatabases where name in ($, (select name from sysdatabases), $)
```

Changes between value and value to >= AND <=. For example, this statement:

select name from sysdatabases where dbid between 1 and 5

is condensed to:

SELECT name FROM sysdatabases WHERE dbid >= $ AND dbid <= $

Reduces repeated UNION ALL and UNION clauses to a single iteration. This example, which includes a series of UNION ALL clauses that are all the same:
```
unique statement UNION ALL unique statement UNION ALL unique statement
```
is condensed to:
```
unique statement UNION ALL $
```
However, show_condensed_text retains all UNION ALL clauses if they are distinct. For example, show_condensed_textkeeps these show_condensed_text clauses:
```
 select name from sysdatabases 
union all select name from sysobjects 
union all select name from syscolumns
```
But this statement:
```
 select name from sysdatabases 
where name = “master” union all select name 
from sysdatabases where name = “tempdb” 
union all select name from sysdatabases where name = “model”
```
is condensed to:
```
SELECT name FROM sysdatabases WHERE name = $ UNION ALL $
```
Reduces or clauses, even though they may have a different order than the original SQL text. For example, this statement:
```
 select count(*) from sysmessages where error<10 or error > 1000000 or error = 1000
```
is condensed to:
```
SELECT COUNT(*) FROM sysmessages WHERE error = $ OR error < $ OR error > $
```
Applies the same rules for queries to subqueries.