Memory Useage

Question

Hi to All,

How do we find which Query is taking more memory in sql server 2008.




Regards,
Pabgupta.

Answer 1

I think you want to find which are high on Logical Reads, Physical Reads. The below query gives you the TOP 250 expensive queries. You can changed the order by clause to decide whether you want it by High CPU, High Reads etc.

    SELECT  TOP(250) SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1
           ,((CASE qs.statement_end_offset
                WHEN -1 THEN DATALENGTH(qt.TEXT)
                ELSE qs.statement_end_offset
            END - qs.statement_start_offset)/2)+1)
           ,qs.execution_count
           ,qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes
           ,qs.total_physical_reads, qs.last_physical_reads
           ,qs.total_rows, qs.last_rows, qs.max_rows, qs.min_rows
           ,qs.total_worker_time/1000000 total_worker_time_in_S, qs.last_worker_time/1000000 as last_worker_time_in_S, qs.max_worker_time/1000000 as max_worker_time_in_S
           ,qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.max_elapsed_time/1000000 max_elapsed_time_in_S
           ,((qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count) / 1000000 total_wait_time_in_S
           ,qs.last_execution_time, qp.query_plan
      FROM  sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
     WHERE  qt.dbid = DB_ID()
  ORDER BY  qs.total_logical_reads DESC -- logical reads
  --ORDER BY qs.total_logical_writes DESC -- logical writes
  --ORDER BY qs.total_physical_reads DESC -- physical reads
  --ORDER BY qs.total_worker_time DESC -- CPU time
  --ORDER BY qs.total_elapsed_time DESC -- Duration

Answer 2

What is the physical memory on the server?

What is @@version?

Do you have MAX memory set?

How many CPU-s?

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012