November 18, 2014 at 3:48 am
Hi,
We're seeing an exclusive lock on a table with only a few rows (4) which seems to relate to updatestats. The system is a third party tool, and so tracking exactly what might be happening is proving to be troublesome.
sp_lock indicates the following row
5465252449260TAB[UPDSTATS] XGRANT
Assuming there isn't something in the code that's performing an update stats on this table (it's an "active users" table so updated fairly often), I was thinking this might be automatic stats update, but that asks 2 further questions - 1 why is it an exclusive lock and 2 why has it been running for nearly 10 days now.
Anyone any ideas?
Thanks
Dave
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply