Identify Memory and Performance Issues in T-SQL Queries and Fix them

Method 1 : Use Dynamic Management View

SELECT
txt.text, total_elapsed_time
FROM
sys.dm_exec_query_stats stat
CROSS APPLY sys.dm_exec_sql_text (stat.sql_handle) txt
ORDER BY
total_elapsed_time desc

The view sys.dm_exec_query_stats will have statistical information of the cached queries
The view sys.dm_exec_sql_text will show the actual query executed. The output will show the results based on the time, the query takes to run, which you can identify and improve upon.

Method 2 : Use SQL profiler

Sometime a query may have code that runs for ever. In such cases, the query never seems to complete execution. You can identify such queries using a SQL profiler.

For eg: Run this code

while 1=1
print 1

The above code will print 1 for ever, thus consuming too much memory. To identify these queries, run a SQL profiler and see the result. As you notice the column CPU, Reads, Writes and Duration will be NULL for that code. To rectify, you can stop that code to release the memory.