TRUNCATE TABLE PARTITION takes hours when deleting an empty partition on Sybase ASE 16

[Neil]

We are having a performance issue with our sybase ASE database version 16.0.03.11.

The TRUNCATE TABLE x PARTITION y runs for hours although the actual partition has 0 records.

How does the truncate work when there are 0 records in the partition ? and why would this take so long ? The DROP PARTITION on the other hand is very quick. The table has millions of records and many indexes though the particular partition that is being truncated is empty.

How should I debug such an issue ? what kind of traces can be recorded or checks performed to understand what the DB is doing during the truncate statement.

Appreciate any help as I am not a DB expert.

Thank you

[Bret_Halford]

A possible explanation, if the table has global index(s), is that TRUNCATE TABLE <table-name> PARTITION <partition-name> causes all global indexes to be rebuilt, even if the target partition is empty.  CR 829608 was opened for this a few days ago; there isn't yet a KBA for it.

A debugging approach for finding out what a process is doing is to use "dbcc stacktrace(spid)" to find out what internal functions are currently being executed.  Run it multiple times for a long-running process to get a profile of what functions are involved.  The function names are sometimes cryptic, but usually some sense of what is going on can be extracted from them.  If the top of the stack shows function names including "idx" on "indx", for instance, would point towards index building, while functions names including "oam" or "gam" would indicate ASE was searching through OAM or GAM structures to find extents belonging to the object.

To use dbcc stackstrace, first use sp_who to find the spid of the process performing the TRUNCATE, then substitute in that spid in

set switch on 3604 -- causes internal dbcc command outputs to be sent to client
go
dbcc stacktrace (spid)
go

Cheers,
-bret

[Neil]

Thank you Bret and appreciate the detailed response ! If it is rebuilding all the global indexes, this explains the long time being spent. I will capture the stacktrace as instructed above. Very helpful, thanks again !