Activity Monitor questions
-
Hi all,
Got a pretty broad question here. I've been looking at the activity monitor on a server that I use, mainly because I was trying to find out why a particular query would randomly be slow at some times and fast at others. My theory was that there was some other query running at the times where mine was slow, and so there were lock contentions or something to that effect.
What I found though, I have no idea how to interpret. Can someone explain what is going on when I see this in the activity monitor?
-
That's just a set of processes. There is lots of information there, but little data.
Personally, I don't like using that because it's so messy. Instead I run queries against the Dynamic Management Objects (DMO). If you run a query on sys.dm_exec_requests, you can see most of this information, but you can also add a WHERE clause to filter information so that you only see blocked processes:
SELECT *FROM sys.dm_exec_requests AS der
WHERE blocking_session_id > 0
And you can get more sophisticated from there.
For intermittent poor performance, blocking and resource contention are good candidates. Another possibility is bad parameter sniffing. What you'd need to do there is check the execution plan when the query is running slow and when it's running fast and compare the two. If they're different, it might be bad parameter sniffing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Also, if you are looking for a more detailed and long term activity monitor, it is something you can actually setup yourself. Microsoft provided us with SQL traces and the ability to import those trace files into a table for reviewing months of performance data at a time. It can track things like the query being run, how long it took to run, hardware usage, blockers, etc. Here is an introductory article into traces with links to Microsoft's site that details out more information:
Jason
Webmaster at SQL Optimizations School
-
I find as a first step that Adam Mechanic's free utility sp_whoisactive will find a good many problems right off the bat.
Todd Fifield
-
Very Good advice from Grant. That's what you need to look for...
Thank You,
Best Regards,
SQLBuddy
-
As Grant said, use the DMOs. I also like the suggestion of using sp_whoisactive by Adam Machanic.
Activity Monitor, if left open can be a pretty resource intensive utility. However, if you run the query that activity monitor uses - it consumes far fewer resources. The behind-the-scenes query used by Activity Monitor actually looks at the DMOs.
You can read more on it here[/url]. Just make sure you also read the link that is referenced there to get to the actual query.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
HI GRANT ,
similar issues occurs in one of our production server if i go into details
1. application team complaining that queries are executing slow and want reason for that in this case what should i do [or ] follow
2. customer complaining that the data which they are uploading is taking so much of time and continues to the next time with out completion
(1.) inthis case when they have to upload the next day data customer canelling the previous day data which is still uploding staus
inthis case what should i follow
3..mayin this by cheking sp_who2 active [or ] or using dmvs [or ] dealocks what ever the things which may kill the process in activity monitor
customer dose not want to us to kill any process with out approvalll....
so please guide or suggest us with root cause by that i can handel this issue woludn't repeat again
Thanks
Naga.Rohitkumar
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply