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

Sometimes you may notice that some T-SQL queries are taking too much time to execute and thus slowing down the performance of SQL Server and other applications. You can find and rectify these queries using the following methods:

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.

You may also find the following posts helpful

Find the Most Time Consuming Code in your SQL Server Database

List Dynamic Management Views (DMV) by Category in SQL Server 2008