Post reply

Activity Monitor questions

  • September 14, 2011 at 12:57 pm

    #390406

    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?

  • September 15, 2011 at 5:47 am

    #1381857

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • September 16, 2011 at 11:43 am

    #1382697

    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:

    SQL Trace

    Jason

    Webmaster at SQL Optimizations School

  • September 16, 2011 at 11:47 am

    #1382699

    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

  • September 16, 2011 at 7:23 pm

    #1382854

    Very Good advice from Grant. That's what you need to look for...

    Thank You,

    Best Regards,

    SQLBuddy

  • September 16, 2011 at 10:05 pm

    #1382863

    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

  • November 8, 2012 at 11:12 pm

    #1557447

    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