on 03-11-2016 10:15 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
"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.
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
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,.
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.
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
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
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
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
Annie,
From what I recall, APF, or Asynchronous Pre Fetch is a strategy used by the ASE Optimizer to read extents of pages at a time when it THINKS that the table data is faily clustered. An extent is defined as 8 contiguous pages.
For APL tables, if they are newly created and populated, the pages are all full (as per the set fillfactor percent), wether a clustered index is present or not, and countiguous to each other within eight page extents. This fact will be reflected in the table statistics which you can check with optdiag. APL tables are also a doubly linked list. Because of these facts, and in this case, a Table Scan will be an exremely fast and efficient operation and a great candidate for AFP assuming the required buffer pools are configured in cache.. In this case, the most efficient would be an 8K buffer pool, assuming a 2K page server.
For DOL Tables, if they are newly created then each page is full (as per the set fillfactor percetn), however the pages are not doublly linked list. They may be countiguous within extents, a fact that will also be reflected in the statistics. Because DOL tables are not linked, and that OAM pages and Allocation Unit pages and row ids are used in a search, a table scan is not always the most efficient access method. In this case, clearly the APL table will outperform the DOL table. BTW, I believe that creating a clustered index on the DOL table will compact the table, but not change the result.
In addition, SELECT * FROM an APL table with a clustered index should never refference the index because that would incur more IO than just performing the table scan. So, I would not see that as the issue.
Is the index on the DOL table as well? As I said, I don't believe it will make a difference, but when making comparisons of this type is is best to compare apples to apples right?
Anyway, I hope this helps.
Regards,
Jean-Pierre
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
76 | |
9 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.