December 28, 2010 at 9:42 pm
Application is running very slow? how to find which sp is running ? how much memory space is taken?
December 28, 2010 at 9:48 pm
if you have 2008 run activity mnitor for the instances.
if you in 2005 there are dmvs to check this.
if in 2000 check in sp_who2 'active' and see blocking spid. then dbcc inputbuffer(spid) will give you the detail of sql text.
----------
Ashish
December 28, 2010 at 9:58 pm
crazy4sql (12/28/2010)
if you have 2008 run activity mnitor for the instances.if you in 2005 there are dmvs to check this.
Actually, it's SSMS 2008 that utilizes those DMVs to produce the activity report. If you have SSMS 2008, you can run the activity monitor against a SQL 2005 instance. 🙂
if in 2000 check in sp_who2 'active' and see blocking spid. then dbcc inputbuffer(spid) will give you the detail of sql text.
You're assuming that there is blocking going on. It could be just lack of indexes on large tables causing table scans. 🙁
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 29, 2010 at 7:28 am
2005 or above, I'd go to sys.dm_exec_requests to see which processes are running slow, which ones have wait states, which ones are blocked. You can combine it with sys.dm_exec_sessions to eliminate non-user processes:
SELECT der.*
FROM sys.dm_exec_requests AS der
JOIN sys.dm_exec_sessions AS des
ON der.session_id = des.session_id
WHERE is_user_process = 1
That doesn't even begin to scratch the surface of what's possible with DMOs in 2005 & 2008.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply