0

We have a weekly maintenance plan that does your typical Integrity Checks and then goes and does an Update Statistics on all the databases on our server.

Over the weekend it appears that our job was hung up on the Update Statistics (with full scan) job. Looking at the Activity Monitor it appeared to have a Task State of SUSPENDED for the command Update Statistics. And for a Wait Type was LCK_M_X.

My Question: Why would it be suspended? A lock on a table in the database it was doing statistics on couldn't have stopped it? What would hang an update statistics?

  • We have steps that are triggered if the particular step (update stats) fails, and it never got to that point so all evidence points to the update stats being hung up. – Andy E Feb 2 '15 at 16:35
  • Did you look for blocking? – Aaron Bertrand Feb 2 '15 at 16:38
  • is auto update statistics turned ON ? A LCK_M_X wait means a thread is waiting to be granted an exclusive lock on something. You should look at the resource associated using sys.dm_os_waiting_tasks DMV. Check this blog post from Paul. – Kin Shah Feb 2 '15 at 16:39
  • yes auto update statistics is turned on – Andy E Feb 2 '15 at 16:40
  • Is it sync or async? – Aaron Bertrand Feb 2 '15 at 16:41
0

We were able to determine that only one table in the database in question had not had it's statistics updated. We did that by running the following

select object_name (i.id)as objectname,i.name as indexname,i.origfillfactor,i.rowcnt,i.rowmodctr ,STATS_DATE(i.id, i.indid) as ix_Statistics_Date,o.instrig,o.updtrig,o.deltrig,o.seltrig
from sysindexes i INNER JOIN dbo.sysobjects o ON i.id = o.id
where rowcnt >1000 and i.name not like'sys%'and object_name(i.id)not like'sys%'
order by objectname desc
go

So we ran the update statistics on just the table in question. And we were able to replicate the scenario in question as it was still waiting and not finishing. So then knowing the session ID of my waiting process I took a look at the locks table

select * from sys.dm_tran_locks

Looking at the results I found a different session ID associated with the Object_ID of my table in question. I was able to then go kill that session ID and the update statistics finished no problem.

What I think happened here was that a user left a program open that had the exclusive lock on my table, and perhaps that along with auto statistics in sync mode may have caused a log jam when doing our full scan update stats.

  • So the other session was blocking it. You should try to figure out what that session was doing - you don't want to have to manually kill a session every time this happens. – Aaron Bertrand Feb 2 '15 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.