What is in my statement cache?

I was talking with one of our customers the other day who had a whole bunch of disparate applications attaching to his server.

It really is not unusual from what I have seen to have legacy applications connecting to ASE that the DBA has no knowledge of at all, things just ‘work’ 🙂 .

Anyway, from what I explained I thought it would be worth detailing here, there are specific formats and properties of the various entries in

the statement cache that help identify the possible setup of the source application. 

We have enhancements in 15.7 and above to allow dynamic sql to be stored in the regular statement cache also (more on that below).

The statement cache stores the statement text object, the LWPs (lightweight procedures) themselves reside in the procedure cache.

Note that monCachedProcedures\monCachedStatement are the two MDA tables relevant to looking at the individual LWPs.

Here I am making use of of a few things…

show_cached_text(<ssql_id>) –ssql_id being the id of the statement in the statement cache (also the lwp procedure id)

dbcc prsqlcache (<ssql_id>, 0||1) –ssql_id is optional but without it will print the whole statement cache. 1 for second parameter will show query plan in showplan format.

show_cached_plan_in_xml(<ssql_id>,0,6) –very useful, this can display for you the compile time bind values as well as execution time bind values.

..Ok so I’m not actually using this last one for the purposes of this post, but thought it was definitely worth mentioning, it’s a great addition.

The ssql_id of a statement can be found in the showplan output or you can actually search monCachedStatement based on the statement text.

The configuration option ‘enable stmt cache monitoring’ will need to be enable to run the queries against monCachedStatement.

This exposes a number of other fields that we now store with the entry in the statement cache, and in fact are key players

when it comes to identifying the correct statement on subsequent executions, more later..

select mp.ObjectName, PlanID,QuotedIdentifier,convert (char(100),show_cached_text(ms.SSQLID)),UseCount,StmtType,SSQLID,Hashkey from monCachedStatement ms, monCachedProcedures mp

where ms.SSQLID=mp.ObjectID

and show_cached_text(ms.SSQLID) like ‘select id from sysobjects%’ –yeh, I know..not the best query

Also, I would strongly suggest not querying the MDA tables like this directly on a production server, make persistent copies of the data and then query those

tables.

ObjectName                     PlanID      QuotedIdentifier TransactionIsolationLevel                                                                        UseCount    StmtType SSQLID      Hashkey    

—————————— ———– —————- ————————- ———————————————————————- ———– ——– ———– ———–

*ss0883087873_1444758355ss*    216         0                1                         select id from sysobjects where id =@@@V0_INT(@@@V0_INT INT)           1           1        883087873   1444758355 

*ss0227085536_1999730024ss*    128         0                1                         select id from sysobjects where id=@@@V0_INT(@@@V0_INT INT)            1           1        227085536   1999730024 

*ss0243085593_1258308435ss*    130         0                1                         select id from sysobjects where id= @@@V0_INT(@@@V0_INT INT)           2           1        243085593   1258308435 

*ss0259085650_1191925839ss*    132         0                1                         select id from sysobjects where id = @@@V0_INT(@@@V0_INT INT)          2           1        259085650   1191925839 

*ss0931088044_1309953367ss*    222         0                1                         select id from sysobjects where id = 1                                 1           1        931088044   1309953367 

*ss1075088557_1046503768ss*    240         0                1                         select id from sysobjects where id= @simon(@simon INT output)          1           1        1075088557  1046503768 

*ss1059088500_1394150455ss*    238         1                1                         select id from sysobjects where id= @p0(@p0 INT output)                1           1        1059088500  1394150455 

*ss0067084966_1394158647ss*    104         1                1                         select id from sysobjects where id= @P0(@P0 INT output)                4           1        67084966    1394158647 

*ss0419086220_0208369987ss*    151         1                1                         select id from sysobjects where id= @dr_ta0(@dr_ta0 INT output)        1           1        419086220   208369987  

*ss0467086391_0208369987ss*    161         0                1                         select id from sysobjects where id= @dr_ta0(@dr_ta0 INT output)        2           1        467086391   208369987  

*ss0547086676_1393102647ss*    171         0                1                         select id from sysobjects where id= @s(@s INT output)                  1           1        547086676   1393102647 

*ss1027088386_1394150455ss*    234         1                0                         select id from sysobjects where id= @p0(@p0 INT output)                1           1        1027088386  1394150455

*ss1155088842_1664707590ss*    250         1                1                         select id from sysobjects where id= @0001(@0001 INT output)            1           1        1155088842  1664707590 

The next lot will only be seen with ‘streamlined dynamic SQL’ = 1.

Part of the enhancement under this option was that dynamically prepared statements which previously were only available in a connection

specific dynamic sql cache are now able to be stored in the regular statement cache to be shared across connections.

*sq0051084909_1393122376ss*    157         1                1                         select id from sysobjects where id= ?                                  19          3        51084909    1393122376 

*sq0051084909_1393122376ss*    197         1                1                         select id from sysobjects where id= ?                                  19          3        51084909    1393122376 

*sq0947088101_1393122376ss*    224         1                0                         select id from sysobjects where id= ?                                  1           3        947088101   1393122376 

*sq0627086961_1393122376ss*    181         0                1                         select id from sysobjects where id= ?                                  3           3        627086961   1393122376 

*sq0083085023_1394150455ss*    106         1                1                         select id from sysobjects where id= @p0                                2           2        83085023    1394150455 

*sq0387086106_0208369987ss*    145         1                1                         select id from sysobjects where id= @dr_ta0                            1           2        387086106   208369987  

*sq0611086904_1393102647ss*    179         0                1                         select id from sysobjects where id= @s                                 1           2        611086904   1393102647 

*sq0867087816_1664707590ss*    214         1                1                         select id from sysobjects where id= @0001                              1           2        867087816   1664707590 

*sq0915087987_1309953367ss*    220         0                1                         select id from sysobjects where id = 1                                 1           2        915087987   1309953367 

If your clients are standard ct-lib isql, then the most common form of statement you will see in the statement cache is an entry with the three ampersands

as the prefix of the variable name. These will be generated when ‘enable literal parameterization’ is enabled at the server-level or literal_autoparam is on at session level. You may also see these generated from any client application issuing statements with fixed (non parameterised at client-side) search arguments.

These statements may be regular or prepared, but crucially, not ‘dynamically’ prepared – more on that in a bit.

select id from sysobjects where id =@@@V0_INT(@@@V0_INT INT)

select id from sysobjects where id=@@@V0_INT(@@@V0_INT INT)

select id from sysobjects where id= @@@V0_INT(@@@V0_INT INT)

select id from sysobjects where id = @@@V0_INT(@@@V0_INT INT)

All with a name of *ss<ssql_id>_<hashkey>ss*

I’m not going to go into literal parameterisation in this post, but to put it simply it takes constant search arguments and replaces them with parameters

to enable the plan to be re-used for a different set of search arguments, in essence to cut down on compilation overhead. Lots of good stuff in this area

that could be discussed but not here and now.

Note the differing white space around the equality operator, having no white space on either side of the operator will form a unique statement, more than

one space though and they’ll be stripped and it’ll get lumped in with the single space ‘versions’. Comments are kept with the statement (and are used in hashkey generation) if they are sent to the ASE.

Note these all have a StmtType of 1, these are all language statements or ad-hoc sql (from the perspective of the server), their lwp name will start with *ss

select id from sysobjects where id = 1

This one is a regular statement but with no literal parameterisation.

Next we have what we call parameterised language statements, these can of course be ad-hoc queries using a declared variable (the first example):-

select id from sysobjects where id= @simon(@simon INT output)

If you see the following naming convention for the variables, then this is likely jConnect JDBC driver  (jconn3.jar (6.x) or jconn4.jar (7.x)):-

select id from sysobjects where id= @p0(@p0 INT output)

Parameterised language statements may be sent from pretty much any client (ODBC,JDBC, ADO.NET,OLEDB, ct-lib, esql etc) and are likely to either

be prepared statements with unnamed placeholders, i.e. a question mark (but not dynamically prepared), or statements which were intended to be dynamically prepared that the server didn’t permit ( ? placed in the wrong place).

Upper case P used by jTDS driver:-

select id from sysobjects where id= @P0(@P0 INT output)

Note the number suffix will be incremented dependant on the number of variables in the statement.

ODBC driver (native Sybase ODBC)\ OLEDB driver \ ADO.NET driver (for the ADO.NET ? can only be used with NamedParameters=false):-

select id from sysobjects where id= @dr_ta0(@dr_ta0 INT output)

If you look the the next one in the list, you’ll see it has the same hashkey as the one above. The difference here is the quoted_identifiers option.

If this is set it will create a new lwp.

Next:-

select id from sysobjects where id= @s(@s INT output)

This is just a statement using named parameters – AseCommand.Parameters.Add(“@s”, SqlDbType.Int).Value = 1 for .NET driver for example.

Most APIs have a way of doing this.

The next one again appears to be a repeat statement, ah, but hang on it has a different isolation level.

Before 15.7, change of isolation level was a trigger to bump the schemacnt and recompile, but not any more, it will generate a new lwp.

This also applies to optimization goal & optimization level – NOTE this is relevant for statement cache lwps not stored procedures.

Next:-

select id from sysobjects where id= @0001(@0001 INT output)

This is usually the format seen with the older DataDirect odbc drivers (dare I speak of them 🙂 ).

Now we have a few of these:-

select id from sysobjects where id= ?

The first two are in fact exactly the same, check the lwp name, the two rows represent 2 query plans, i.e. one was in-use so we had to compile

another (or clone on later versions).

These are dynamically prepared statements, ct_dynamic from ct-lib, SQLPrepare from native ODBC, PreparedStatement in JDBC, Ase\Odbc\OleDbcommand.Prepare().

OLEDB\ODBC\jConnect with whatever property determines this for the relevant connection string (DYNAMICPREPARE=1\DYNAMIC_PREPARE=true).

You will only see question marks as search arguments (with no quotes) from dynamically prepared statements.

They have a StmtType of 3.

They will be sent by the client as create proc statements with an external name to communicate with the server, don’t get

confused by the statements you may see in monSysSQLText, these may look something like

create proc myquery –custom name set in ct_dynamic call

create proc jtds000001 –jtds

create proc dyn100 –jdbc, possibly other on some versions

create proc A0 – ODBC\OLEDB

These are pretty much meaningless from a debug perspective, the internal name of the lwp stored begins with *sq.

See above, all of these are now in the statement cache and that is down to ‘streamlined dynamic SQL’. Without

that feature enabled you will not see dynamic prepared statements in the statement cache.

Finally, lets look the final five, these begin with *sq but look like regular ad-hoc queries, or rather parameterised language statements.

Note the StmtType=2, this means they are cursors, specifically either straight language based cursor or TDS cursors based on a language

statement. An lwp is created for the cursor, which means it is converted to a procedural cursor (or execute cursor).

This is also a ‘streamlined dynamic SQL’ enhancement.

I’m in danger of getting too much into the nitty gritty here but feel we need to make the job complete!

You won’t see a StmtType=2 for a dynamically prepared statement (that uses cursors) as the cursor is declared on top of the external lwp name, not the

language statement under the external lwp.

Cursors will be used if the correct method for whatever API is called to make it a cursor – or the correct connection parameter is set (UseCursor=1 for ODBC\OLEDB, IMPLICIT_CURSOR_FETCH_SIZE=<X>\SELECT_OPENS_CURSOR=true for jConnect).

The very last entry there is a regular ad-hoc declare cursor\open cursor (which will not go through literal parameterisation by the way)

just to show that the *sq lwps are not necessarily restricted to the premise of a prepared statement, although as already demonstrated they *usually* are.

If you made it this far hopefully you found something useful you can take away!