UPDATE STATEMENT GETTING STUCK I am facing a performance issue with an update statement. It takes too long to update, but when I perform a select on that updating table it completes very fast.

UPDATE mo
   SET mo.RNCNEID    = ts.RNCID,
       mo.SiteNEID   = ts.SiteID,
       mo.SectorNEID = ts.SectorID
FROM COPS_UMTS_HUAWEI.PER.tHuawei_CELL_Ncell_DY_HR AS mo
 INNER JOIN COPS_UMTS_HUAWEI.Aggregation.tsector   AS ts  
     ON mo.rncname      = ts.rncname
    AND mo.cellid       = ts.cid
    AND ts.VendorID     = 6
    AND ts.TechnologyID = 2
WHERE mo.RNCNEID IS NULL;


After running the below query:

SELECT COUNT(*)
FROM   COPS_UMTS_HUAWEI.PER.tHuawei_CELL_Ncell_DY_HR WITH (NOLOCK)
WHERE  RNCNEID IS NULL;


...it completes very fast.
It takes too long to update but when I do select on that updating table its completes very fast .


If the UPDATE query is taking a long time, but the SELECT part is fast when run on its own, then one possible reason is that the UPDATE is getting blocked due to incompatible locks being held by another session.

You can check for this while UPDATE query is running by, for example, running sp_WhoIsActive:

EXEC sp_WhoIsActive;




In this screenshot, I'm trying to run an UPDATE in session_id 57, but it's blocked by another session that's holding locks that my query needs.  You can see this in the wait_info and blocking_session_id columns, which show LCK_M_X waits (which is an exclusive lock, usually required by another UPDATE query running) and 53 (the other session that is holding the lock).


  After running the below query...Query completes very fast .


Running that count(*) query will likely make sure all of the pages needed for the query are in memory (meaning it will be faster, because they won't have to be read from disk first).  This probably just masking the blocking issue, as the blocking query finishes faster when the pages are in memory.