Update stats monitoring

Posted on 2009-04-19
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
    LVL 13

    Expert Comment

    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

    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

    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

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

              l.spid spid,
              object_name(, 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

    That should do the trick.  Glad I could help.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    Title # Comments Views Activity
    sybase license update 3 444
    SYBASE Query combine date spans 7 626
    Best Fit Equation for a Line 5 808
    SQL Query Help! 11 105
    I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
    This paper addresses the security of Sennheiser DECT Contact Center and Office (CC&O) headsets. It describes the DECT security chain comprised of “Pairing”, “Per Call Authentication” and “Encryption”, which are all part of the standard DECT protocol.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now