March 11, 2010 at 6:51 am
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
March 11, 2010 at 7:33 am
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
March 13, 2010 at 2:10 am
This White Paper from Microsoft will help you focus you efforts in the right place:
http://msdn.microsoft.com/en-us/library/cc966413.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply