SELECT*

Today in my environment i got this error and realized that this happened due to memory pressure. To confirm this whether is this really happened due to memory or not  i have ran the below queries. Symtoms for Memory pressure: If you run the below command and if it shows last waittype as RESOURCE_SEMAPHORE then it meas it is clearly memory issue 1)SELECT * FROM SYSPROCESSES WHERE lastwaittype LIKE '%RESOURCE%' 2) Another query you run the below one. Which shows long running queries in SQL Server along with memory ,CPU. And at this time there is also chance that CPU also is hike at that moment. The below query will also provide you query and its execution plan. Which intern also give you the recommend indexes SELECT session_id,DB_NAME(DATABASE_ID), text, query_plan FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) ; 3) Another way to look at his in the 'perfmon' counter. Which is perfo...

SELECT TOP ( 10 ) SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1, ( ( CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset ) / 2 ) + 1) AS statement_text , execution_count , total_worker_time / 1000 AS total_worker_time_ms , ( total_worker_time / 1000 ) / execution_count AS avg_worker_time_ms , total_logical_reads , total_logical_reads / execution_count AS avg_logical_reads , total_elapsed_time / 1000 AS total_elapsed_time_ms , ( total_elapsed_time / 1000 ) / execution_count AS avg_elapsed_time_ms , qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY total_worker_time DESC -----There is also another query for the same SELECT s.session_id, r.status, r.blocking_session_id 'Blk by', r.wait_type, wait_resource, r.wait_time / (1000 * 60) 'Wait M', r.cpu_time, r.l...