SSMS Stopped Working

It happened to me before.

I was running a stored procedure and it stopped after about 11 minutes of running, without completion.

The problem is because it uses too much memory (private memory set).

It stopped when there is no more memory able to allocate for the process.

You can see that for my case, the private working started from 148 MB to 984 MB (and the physical memory reached 93%) and then it stopped.

So, the reason is not that physical memory has reached 100%, even though this is inevitable. The real reason is that it wasn't able to get memory to do processing.

That is the reason why it stopped.

Now, let's investigate the reason why it happened.

It is because of the stored procedure which keeps updating the screen due to SELECT query.

The stored procedure's function is to copy data from one table and try to sync it to another table if it is not exist, otherwise, existing record will be updated accordingly.

Thus, the solution that I have derived is to not perform SELECT query to check if a record is in existence or not.

Instead of performing SELECT query, for each record from the source table, the revised script will perform UPDATE directly, and if @@rowcount is equal to 1, then it means similar record exist and update is successful, otherwise, similar record is not in existence and INSERT query will then be performed.

This actually solved the whole thing and the stored procedure was able to execute to completion.

Besides that, using the revised script, the CPU utilization is virtually zero and private memory set is low.

1.) The snippet SQL script with SELECT query to match existing record. (Not recommended, fail to complete)

fetch next from @maincursor into @cMyID,@cName while (@@fetch_status <> -1)

BEGIN

-- If not found, insert, else update ------------- 
SELECT recID FROM [TBL_1] where cMyID=@cMyID 

if @@ROWCOUNT >=1 

BEGIN 
  --- UPDATE ----- 
  BEGIN TRY 
    UPDATE [TBL_1] SET cName=@cName WHERE cMyID=@cMyID 
  END TRY 
  BEGIN CATCH 
  --- Update Exception ---- 
  END CATCH 
END 

else 

BEGIN 
  -- INSERT ----- 
  BEGIN TRY 

    INSERT INTO [TBL_1] (cMyID,cName) VALUES (@cMyID,@cName) 
  END TRY 

  BEGIN CATCH 
    --- Insert Exception ---- 
  END CATCH 

END 

fetch next from @maincursor into @cMyID,@cName

END 

2.) The SQL script using UPDATE query to match existing record. (Recommended)

fetch next from @maincursor into @cMyID,@cName while (@@fetch_status <> -1)

  BEGIN
  
  -- If not found, insert, else update -------------
  
        --- UPDATE -----
        BEGIN TRY
            UPDATE [TBL_1] SET cName=@cName WHERE cMyID=@cMyID
 
            if @@ROWCOUNT >=1 
               BEGIN
                  --- Success ----
               END
            ELSE
               BEGIN
               -- INSERT -----
                 BEGIN TRY
                  INSERT INTO [TBL_1] (cMyID,cName) VALUES (@cMyID,@cName)
                   
                 END TRY
                 BEGIN CATCH
                   --Insert exception---
                 END CATCH
            END
        END TRY
        BEGIN CATCH
           -- Update exception ---
        END CATCH

  fetch next from @maincursor into @cMyID,@cName

  END

Can we assume that this is an issue for SQL Server Management studio (SSMS) ?