10/04/2013

Queries for Troubleshooting Performance Issues SQL Server

Posting a few queries for SQL - Server to troubleshoot issues with Performance.

----------------------------
-- Display Cursor Info
----------------------------
select 

   cur.* 
from 
   sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
   cur.fetch_buffer_size =
   and cur.properties LIKE 'API%' -- API

----------------------------
-- Display Memory Usage
----------------------------
select
   type,
  sum(virtual_memory_reserved_kb) as [VM Reserved],
  sum(virtual_memory_committed_kb) as [VM Committed],
  sum(awe_allocated_kb) as [AWE Allocated],
  sum(shared_memory_reserved_kb) as [SM Reserved],
  sum(shared_memory_committed_kb) as [SM Committed]--,
  --sum(multi_pages_kb) as [MultiPage Allocator],
  --sum(single_pages_kb) as [SinlgePage Allocator]
from
  sys.dm_os_memory_clerks
group by type

 
http://technet.microsoft.com/en-us/library/cc966540.aspx

http://support.microsoft.com/kb/907877
  

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.