Store Procedure Optimization Tips:-

1. Include 'SET NOCOUNT ON' Statement.
2. Use Schema name with object names.
         [dbo.MyTable ]
3. Don't  Use the Prefix 'SP_' in the Store Procedure name.
4. Use IF Exists (SELECT 1) Instead of (SELECT *).
5. Use the 'SP_ExecuteSQL' Store procedure instead of Execute Starement.
6. Avoid using SQL Server Cursor whenever Possible.
7. Keep the transaction as short as possible.
8. Use TRY_CATCH for error handling.

How to Find Currently Running Query In SQL Server

Following script find out which are the queries running currently on your server.

SELECT sqltext.TEXT,
req.session_id,
req.status,req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext