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:

The view sys.dm_exec_query_stats will have statistical information of the cached queriesThe 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=1print 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.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan