Acitivity Monitor Question

  • Hey i have a basic question

    In Activity Monitor most of the process id's are in sleeping state(i believe sleeping state use less CPU resources).......So it means my queries are in a better state(they don't need any tuning right)

    what type of queries are we supposed to concentrate more for tuning(queries in suspended state or queries in runnable state or queries in Sleeping state)

    Your help is highly appreciated

    Regards,

    Sandeep

  • Sleeping or running doesn't indicate whether or not a query needs tuning. You need to pick a methodology of monitoring that will inform you of where the problems lie. I use one of two methods. If I'm reasonably confident the system is configured correctly, I'll try using trace events to capture query executions and then I'll run reports on the data there to determine which queries are running the longest or using the most resources or most frequently called, and I'll tune those queries. If, on the other hand, I'm unsure of the system setup, I'll monitor wait states to determine what is causing stuff to slow down on the server and then I'll try addressing performance through settings, hardware, or configuration until I'm certain that I'm only dealing with code or database structures and then I'll go back to the other tuning method.

    "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

  • This White Paper from Microsoft will help you focus you efforts in the right place:

    http://msdn.microsoft.com/en-us/library/cc966413.aspx

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply