Use of LIO Ratio (LIOs/CPU Time) to measure performance

[Former Member]

• ElapseTime = CPU Time + WaitTime

A crude form to account for CPUTime would be something like this

• CPUTime = Time to perform LIOs + Time to Parse and Compile + CPU Spins because of spinlock contention

Now from monProcessActivity we do get CPUTime + Logical IOs for a given SPID.

We had a number of challenges when we converted our servers to ASE157 with respect to performance. We had extensive data collectors and had minute by minute deltas for monProcessActivity. From this data we started analyzing the LIORatios for different applications (program_name). We noticed that for some of the applications the LIORatio was very low.

On investigation we found the following:

• The parse and compile times for some of the high frequency queries was way high and this is the reason for the low LIO Ratio

Now this begs the question as of why the parse and compile times are high.

• We do use statement cache for all servers
• However we have disabled statement cache for queries with temp tables (TF- 467)

Here are the reasons why statement cache would not effectively

• Data type mismatch
  • insert into myTables (col1,col2) values(1.0,1.1)
  • insert into myTables(col1,col2) values(0,0)

          In the above example the statement cache engine does not know the datatype of the columns when it is caching these statements. So based on the literal values passed it will created different sets of statements in the cache. For some of the table with many numeric columns we see that the number of cached statements was higher than 10000. It means the reuse count of the cached statements is very low.

• Literals in the select clause
  • select '123' as col1 from myTable
  • select '234' as col2 from myTable

     Though 'auto literal parametrization' is turned on if there were to be a literal in a select clause it does not convert such literals into parameters

• Literal parameters in derived tables
  • select * from (select '123' as col1 from myTable) a
  • select * from (select '234' as col1 from myTable) a

     The literals in derived tables as shown above are not parameterized. So there will 2 different statements in the statement cache

• LIKE clause
  • select * from emp where lname like 'GP%'

     In this case too the literal is not parametrized

• TEMP Tables (TF - 467)
  • This is an interesting case. When you too many short lived connections which use temp tables then TF-467 is something you may want to consider.
  • If the connections you have to the dataserver persist for too long then TF-467 can hurt you

In conclusion the parse and compile time for queries in ASE157 can be signficantly higher than the older versions. Statement cache usage is imperative to good performance. However need to be cognizant about the scenarios where statement cache would be not effective.

LIO Ratio is a good way to start investigating some such problems.

We have also used LIO Ratio to solve some of the issues with with spinlock contention and will discuss about this in the next post.