[Last Call] Learn how to a build a cloud-first strategyRegister Now


Update stats monitoring

Posted on 2009-04-19
Medium Priority
Last Modified: 2012-05-06
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.

Question by:Radhalakshmi
  • 2
LVL 13

Expert Comment

ID: 24181909
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.
LVL 19

Accepted Solution

grant300 earned 200 total points
ID: 24189493
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.



Author Closing Comment

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

            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.blocked blk_spid,

    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'
LVL 19

Expert Comment

ID: 24200316
That should do the trick.  Glad I could help.


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Applications for our next round of the Experts Exchange Scholarship Contest are starting to roll in. It made us wonder what our past winners are up to these days. Here's a look at what four winners experienced with the contest and what they're doing…
Exchange administrators are always vigilant about Exchange crashes and disasters that are possible any time. It is quite essential to identify the symptoms of a possible Exchange issue and be prepared with a proper recovery plan. There are multiple…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month17 days, 16 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question