asked on

Update stats monitoring

Is there a way to find out which table update stats is currently running?
When DBAs run update stats and Im running a process parallely,I need to figure out if my tables are getting a lock coz of update stats.

alpmoon

You can execute sp_who to see whether any 'UPDATE STAT' is running and sp_lock to see what table has locks by the process running update stats. But, 'update stat' doesn't hold an exclusive lock on a user table. It only holds exclusive locks on system statistic tables while it is writing statistics data at the end (for only milliseconds). However, if your concern is shared locks, you can use sp_who and sp_lock to find out tables locked by update stat process. Use object_name() function to see the actual table names you see in sp_lock output.

ASKER CERTIFIED SOLUTION

grant300

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Radhalakshmi

ASKER

Thnx for the insight.
Came up with this query which can be fine tuned and filtered for my need.

select
v.name lock_type,
       l.spid spid,
       l.id table_id,
       object_name(l.id, l.dbid) table_name,
       db_name(l.dbid) db,
       p.cmd command,
p.physical_io io,
       suser_name(suid) usr,
       p.hostname,
       p.program_name,
       p.loggedindatetime,
       p.ipaddr,
       p.blocked blk_spid,
       p.time_blocked

from master.dbo.syslocks l,
master.dbo.sysprocesses p,
master..spt_values v
where l.spid = p.spid
and l.type = v.number
and v.type = 'L'
and db_name(l.dbid) <> 'master'

grant300

That should do the trick. Glad I could help.

Regards,
Bill