2

I run UPDATE INDEX STATISTICS on some HUGE table. And it runs already about 1 hour. Is there some procedures/ways to find the percentage of executing this command? sp_who, sp_sysmon don't help. Thanks.

1

There is no direct way of doing it in SybaseASE (as opposed to sql server - which exposes DMV data).

You can get close to see if your UPDATE INDEX STATISTICS is getting CPU, Physical IO and is not being blocked.

I use below query :

select l.spid
    ,p.status as status
    ,Db_name(l.dbid) as dbname
    ,p.hostname as hostname
    ,p.program_name as program_name
    ,substring(Object_name(l.id, l.dbid), 1, 30) as object
    ,p.blocked
    ,case 
        when type = 1
            then "Exclusive table lock"
        when type = 2
            then "Shared table lock"
        when type = 3
            then "Exclusive intent lock"
        when type = 4
            then "Shared intent lock"
        when type = 5
            then "Exclusive page lock"
        when type = 6
            then "Shared page lock"
        when type = 7
            then "Update page lock"
        when type = 8
            then "Exclusive row lock"
        when type = 9
            then "Shared row lock"
        when type = 10
            then "Update row lock"
        when type = 11
            then "Shared next key lock"
        when type = 256
            then "Lock is blocking another process"
        when type = 512
            then "Demand lock"
        end as "TYPE_OF_LOCK"
    ,l.page "Page"
    ,Substring(p.cmd, 1, 16) as executing_command
    ,p.physical_io as "PHYSICAL_I/O"
    ,p.cpu as cpu
from master..syslocks l
    ,master..sysprocesses p
where l.spid = p.spid
1

Predicting the progress is very difficult, but if you know the number of logical IOs that your UPDATE STATISTICS command took on a particular table, then you can use that next time as a rough indicator how for it has progressed (assuming the indexes are the same). You can get this info from MDA tables like monSysStatement or monProcess.

  • @RobV - I have your indispensable quick reference guide for Sybase ASE; it is FANTASTIC! – Max Vernon Jan 16 '16 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.