Need help on update stat running through proc ..

[Former Member]

Version : 16

Executing update statistics through store proc . How to find on which table update statistics is running .

dbcc sqltext shows Proc_name .

sp_lock shows Sh_intent on ops..sysstatistics .

How to find on which table Update Statistics is running ..

Example :

create proc sp_test1

as

update index statistics XXX

go

excec sp_test1

go

From Other Session ..

dbcc sqltext(SPID) ---> Shows sp_test1

sp_lock <SPID>,@verbose=1

fid    spid   loid        locktype                                                                                                                                                                                                                                                        partitionid page        row    objectName                                                                                                                                                                                                                                                       id          class                          context                                                                                                                                                                                                                                                        

------ ------ ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

0      318    636         Sh_intent                                                                                                                                                                                                                                                       0           0           0      ops..sysstatistics                                                                                                                                                                                                                                               24          Non Cursor Lock                                                                                                                                                                                                                                                                               

[Former Member]

Thanks Mark got the info from monProcessObject . 

But is there anyway to find the same info without MDA table .  there are chances that 'monitoring is not enabled..

[Mark_A_Parsons]

You could get the same info from sysprocesses.cmd and syslocks, but keep in mind that querying these fake tables can get expensive (cpu, spinlock contention, etc) ... not something you'd want to be doing very often and certainly not on a system with large numbers of active logins and/or locks.

You may be able to get what you're looking for from dbcc pss(), but that's going to require capture/parsing by the calling process (ie, you won't be able to capture/parse the data in a SQL batch/proc).

[Former Member]

Syslocks (sp_lock) is not giving table name (on which  update stat is running) . it gives sysstatistics .  but I am running update stat on table 'Item'

What i understand because table is DOL that is why it running in isolation level o ( Do not lock table) that is why not showing in sp_locks .

sp_lock 319:

fid    spid   loid        locktype                                                                                                                                                                                                                                                        partitionid page        row    objectName                                                                                                                                                                                                                                                       id          class                          context                                                                                                                                                                                                                                                       

0      318    636         Sh_intent                                                                                                                                                                                                                                                       0           0           0      ops..sysstatistics                                                                                                                                                                                                                                               24          Non Cursor Lock                                     

From :  MDA table giving all info ..

DB                   TableName                      ObjectName                     ObjectType                     Command                        StartTime                

-------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------------

ops                  Item                           XIE6Item                       user index                     UPDATE STATISTICS              2016-08-24 13:53:27.676  

[Mark_A_Parsons]

Try syslocks instead of sp_locks.

[Former Member]

syslocks also giving same .. object id : 24 which is sysstatistics . That is why raised this question . I got the needed info from MDA tables as you suggested earlier .

[Mark_A_Parsons]

Which update stats command are you running?

You do realize that the spid could have multiple rows in syslocks? (for sysattributes as well as the table you're running update stats against)

I would expect to see at least some sort of shared lock on the table and/or index (depending on the update stats command).

[Former Member]

you can also test ..as beow on DOL table ..

dbcc sqltext shows Proc_name .

sp_lock shows Sh_intent on ops..sysstatistics .

How to find on which table Update Statistics is running ..

Example :

create proc sp_test1

as

update index statistics XXX

go

excec sp_test1

go

From Other Session ..

dbcc sqltext(SPID) ---> Shows sp_test1

sp_lock <SPID>,@verbose=1

[Mark_A_Parsons]

OK, I see what you're saying.

syslocks will show shared locks on the table if the table is using allpages locking (APL); but if using datapages/datarows locking (DOL) then syslocks shows no rows for the table.

I would expect to see a shared lock on the (DOL) table... so this would seem to be a bug ... but for some reason the little voice in the back of my head thinks this may be the correct behavior (namely, 'update stats' does not obtain any locks on the table it's scanning) ... I'd have to defer to someone with more details of the under-the-covers behavior of update stats in relation to DOL tables ...

[former_member188958]

The update stats command on DOL tables uses an isolation level zero scan.

-bret

[Former Member]

These tables are data row (DOL) locking  tables .