APFs on APL vs DOL

[annieM]

Hi

Do we have any explanation why APFs on an APL vs DOL(newly created tables almost same ratios etc in the optdiag) tables with the same table structure in the same environment show rather big differences?

For APL

APFs Issued                  380.9        1114.9      45712      94.0 %

For DOL

APFs Issued                    52.3        133.4        6272      12.0 %

Devices

On the same device

APL

  Reads                                                                        

    APF                          144.4        422.8      17333    100.0 %

    Non-APF                        0.1          0.2          8      0.0 %

    Writes                            0.0          0.0          0      0.0 %

DOL

Reads                                                                  

      APF                            35.6        104.3        4277      20.5 %    

     Non-APF                      137.9        403.7      16552      79.5 %  

     Writes                            0.0          0.0          0      0.0 %

The only difference maybe is that an APL table has a cluster index but the query is select * from table with a full table scan or select min(col1)

where col1 is non index column.

ASE16.0 SP01 PL02

Annie

[kevin_sherlock]

APF is typically a physical IO strategy, but can be satisfied from cache because APF strategies are chosen by the optimizer before determining if pages are in cache.  Post the output of the sysmon section that details APF's satisfied from cache.   Also, try a test where you clear the object from cache before your select statements such that all IO's are done from disk to see if you notice different numbers.  You can do that with "sp_unbindcache", and "sp_bindcache" (if necessary).

Also, there are lots of other variables here that aren't spelled out.  Are the tables bound to the same cache (or both in default data cache), and what if any storage properties are set for the tables.

Would be useful to see "sp_help" output from both tables as well as "sp_spaceused" info from both.

sp_sysmon output is serverwide stats so not sure if you might be seeing effects of other tables there or not.  Try to get APF numbers for each table from monOpenObjectActivity instead.

[annieM]

Hi Jean-Pierre, Hi Kevin,

Thank you.

The customer run one statement at a time and theses are the only statements running-no other activities on the server.

Tables are bound to the same default cache.

Datacache purge with DBCC purgesqlcache before every run.

Both tables are newly created both are on the same device.

optdiag and sp_sysmon are almost identical inclusive "TotalAvg Bytes Sent/received/  (of course with a bit higher number of pages and row size of DOL in optiag)

Table at about 4G.

The only notable differencese are I/O busy (doulbe with DOL) and APFs

Annie

[Former Member]

Annie,

Thank you.

Apparently the only differences are those you outline, and, the clustered index missing on the DOL table? No extraordinary cache bindings, so the test is straight forwared enough.

I insist that the issue is related to the fact that in this case, as per my explanation above, the table scan on the APL table is going to outperform the table scan on the DOL.

Thank you and regards,

Jean-Pierre

P.S. The only thing you did not report on was the number and size of buffer pools in the default

        data cache... You have a 2K pool... What else.

[kevin_sherlock]

"dbcc purgesqlcache" only affects the statement cache (ie, the SQL statements themselves).  That does not affect the data.

To effectively remove table data from "default data cache", simply execute:

exec sp_unbindcache <mybd>,<mytable>

As I say, some APF numbers greatly depend on what is in cache and what isn't. 

[annieM]

8k page size with 16K and 64K

[annieM]

we've tested with bind/unbind, too

The same effect.

Ah - have found the way to attacg files.

Here are only APF and network part (and i/o Busy in APL file for DOL it doub

sysmon_apl_apf.txt.zip

sysmon_dol_apf.txt.zip

[annieM]

Yeah, maybe this is the explanation. I also want to try to repro it. I tryed with a smaller table but didn't get the result they have. May be need to increase the table size Annie

[Former Member]

8K page server, with a 16K buffer pool (what size?) and a 64K buffer pool (what size?)

Also, if you really want a true test, and executing both:

-- To clear statement cache

dbcc purgesqlcache

go

AND

-- To remove the table

exec sp_unbindcache <mybd>,<mytable>

go

I would just BOOT the server and run the first test, then BOOT the second server and run it again.

You have to compare the executions under exactly the SAME conditions.

Othewise, it is going to be difficult. So, the bottom line, is this a production issue, or, is it just a good to know test?

Jean-Pierre

[annieM]

Ahh... sorry copy/pasted a wrong dbcc.

Here to purge data caches DBCC cachedataremove.

We've tryed both with this dbcc and with bind/unbind

purge statement for both tables, start always with the same apl/dol rattio.

Annie

[annieM]

We were purging with DBCC cachedataremove and bind /unbind and also purging statement cache to have the same for both tables but always got the same result.

cache is to test a single 4G table

8 Kb    491520 Kb  69132.00 Mb  69132.00 Mb    10  

16 Kb    102400 Kb    500.00 Mb    500.00 Mb    10  

64 Kb    491520 Kb  32768.00 Mb  32768.00 Mb    10

Also decreased to 1 partition. but the sysmon output keep the same apl/dol ratio.

The only thing:  it wasn't me running these tests, but i'm pretty sure they did everything as instructed.

Another "strange" thing (at least for me) that with these cache sizes we have "LRU Buffers gubbed" (as in all cases rather similar for apl and dol) but this I think still not explain this ap/dol difference,.

[Former Member]

Please let us know when you have. And mark the correct answer? Good luck! Regards, Jean-Pierre

[annieM]

Sure,

Thank you for all help

Annie

[kevin_sherlock]

So, looking at your sysmon files:

Measurement	APL	DOL
Total APFs Requested	48663	52330
APF Buffers Found in Cache	1469	44774
APFs Used	47191	7557
APFs Issued	47194	7556
APF Waits for I/O	8646	4880

So, for the case of the DOL table, many many more pages were found in cache.  And i'll also note that large IO (64K) was much higher in your APL sysmon output.  Seems to me that your APL table large io effectiveness was measured as more optimal that that of the DOL table.  Meaning to me anyway that the "contiguous"ness of these tables are much different.  You should also post optdiag output for both.

Aside from that , it looks to me like the APF "effort" here as measured by "Total APFs Requested" is pretty similar. Just that in the case of DOL, many, many more of those were satisfied from cache.

[annieM]

This files look beter with notepad++ 😉

[annieM]

I've removed table/index names.

dol.txt.zip

apl.txt.zip

[annieM]

I've missed to add in my first post that the main reason for asking this is the performance:

the same statement on APL executs 2-2,5 times faster.

[former_member182259]

A lot depends on the query as to why.   Remember, APF's can be of any size - and can be of the table or index leaf pages.   But let's take a query like:

select * from table order by <pkey>

where <pkey> is some list of columns that is in a clustered index.   In APL, the table is essentially the clustered index and as a result, ASE can do table scan with pure APF's (and even large I/O's) to perform the query.   With DOL, it likely will have do an index leaf scan and a resulting datapage lookup for each leaf - which, of course, can take a lot longer.    The index leaf scan can use APFs' - but the data page lookups might not.

It would be better to post the query and the output of the typical qp diagnostics such as:

set showplan on

set switch on 3604

set statistics io, time, resource, plancost on

set show_lio_costing on

set nodata on

set statement_cache off

go

<query>

go

set showplan off

set switch off 3604

set statistics io, time, resource, plancost off

set show_lio_costing off

go

[annieM]

Hi Jeff,

Thank you.

I've asked them to send output with statistics time but haven't received an answer yet.

The query is just an ordinary:

select * from table

or

select an aggreagte finction(col1)  from table_name

col1 is not indexed

The aim is to have a table scan.

In all cases apl/dol relations are almost the same.

I'll aske them to send the output from the script you've added here.

Annie

[Former Member]

Jeff,

I have been saying this all day:

" In APL, the table is essentially the clustered index and as a result, ASE can do table scan with pure APF's (and even large I/O's) to perform the query.   With DOL, it likely will have do an index leaf scan and a resulting datapage lookup for each leaf - which, of course, can take a lot longer..."

Getting the plans and the statistics will only confirm it. A very nice academic exercize nonetheless.

However, to me the point is why are we going through with it?

In the worse case scenario, after we get the plans, if this is a matter of life and death (LOL) we can just force the plan we want on the DOL table and check the IOs again.

Regards,

Jean-Pierre

[Former Member]

Annie,

Like I replied following Jeff´s last note.

If what you want to do is have a table scan on the DOL table, just try it, and compare IOs.

E.g.

SET SHOWPLAN ON

GO

SET STATISTICS IO ON

GO

SELECT * FROM TABLE (INDEX 0)

GO

If this gets you what you want, then, you can always creae an abstract query plan and make this permanent.

Regards,

Jean-Pierre