November 18, 2014 at 4:50 am
Update stats doesn't normally take any locks at all. It runs read uncommitted. It could be that something earlier in that session started a transaction and took the lock and that it's just update stats running at the moment.
What object is the lock on and what was the last command that session ran? (Also, who's it from and where)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2014 at 5:14 am
Hi Gail,
The object in question is a table called "active_logins" - which has 4 rows in it
Last query for the spid is
FETCH API_CURSOR0000000000000704
the spid is being initiated by a third party application through an ODBC connection from a different server note: this pre-dates the recent performance issues seen in the schannel WSSU updates. The third part hasn't been particularly helpful.
I was wondering whether it's worth enabling 8721 and tracing to identify if this is actually an automated stats update?
Thanks
Dave
November 18, 2014 at 5:29 am
Urgh.
No, I don't think the trace flag would help. Both automatic and user stats updates run implicitly in read uncommitted, they don't take any locks other than schema stability.
Gut feel, something else in that session opened a transaction and took an exclusive lock and held it.
Do you have any info on when the last transaction started and when that lock was taken? Go to the DMV, not sp_lock.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply