guess its going to take you a long time then

if your performance tuning instead of using profiler, run your SP in SSMS  and look at the execution plan.

try using the wizard in SSMS, connect to Server, RMC on database, select Tasks, select generate scripts

are you using nolock hint, read uncommitted isolation?

select upper (substring(col,0,patindex('%[0-9]%',col))+' '+substring(col,patindex('%[0-9]%',@col,99))
from your table

could you post the indexes and execution plan?

there are many ways to achieve what you are asking as you can see from your research, before anyone could help we would require some more information around the process.

with cte as
(select count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Muscatel_MPCA_FCT1_LogData_T
WHERE SQLDateTime >= '2018-07-01'
AND SQLDateTime <= '2018-07-13 'AND Station_No in (19,20,21,22,23,24)and Fail_Description NOT LIKE '%[PASS]%'
group...

can you post what you have done so far

should also added, what i'm thinking is that your update could be part of a nested transaction, even though you have issued a commit no data has been committed and...

can you consistently replicate this issue by running the same SQL? 

prior to issuing commit  open a new session and run 
select * from sys.sysprocesses
where spid=add spid

you could try this 
SELECT name AS schema_name,USER_NAME(principal_id) AS owner_name
FROM sys.schemas

select user_name(objectproperty(object_id,'OwnerId')), * from sys.objects
where user_name(objectproperty(object_id,'OwnerId'))=' user here'