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.

RadhalakshmiAsked:
Who is Participating?
 
grant300Commented:
Actually, you can write a query that joins the sysprocesses table to the syslocks table by spid/fid, filter for the UPDATE STAT rows, filter for the dbid you are interested in (and running from), and then GROUP BY syslocks.id

If you use the object_name() function, be aware that it looks the ID up in the sysobjects table of whatever the current database is.  You get garbage if the ID is from some other database.

Regards,
Bill

0
 
alpmoonCommented:
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.
0
 
RadhalakshmiAuthor Commented:
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'
0
 
grant300Commented:
That should do the trick.  Glad I could help.

Regards,
Bill
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.