January 20, 2011 at 6:29 am
I hope this is an appropriate forum for this question.
If you were new to Perf Mon (like I am), but quickly had to implement a few 'best practices' on your server, what would they be and how easy is it for them to be set-up?
What would you get Perf Mon to look out for etc?
Thanks in advance
Barry
January 20, 2011 at 7:32 am
This is a somewhat big topic. There are probably 15-20 measures, minimum, that you ought to capture and interpret. You should also start capturing wait states to see what things are causing stuff to run slowly. The best recommendation i can make, pick up a copy of my book. I spend a couple of chapters on exactly this topic, which numbers to collect and what they might mean. I'm also going to be presenting a full day on this at SQL Rally in May.
"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
January 20, 2011 at 7:35 am
http://sqlinthewild.co.za/index.php/2009/02/14/do-you-know-how-to-use-perfmon/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2011 at 2:12 pm
Follow Grant's and Gail's recommendations.
Also these counters should be a good list to begin with.
PhysicalDisk(_Total)\Avg. Disk Queue Length --(should get individual disks counters)
PhysicalDisk(_Total)\Avg. Disk sec/Read --(should get individual disks counters)
PhysicalDisk(_Total)\Avg. Disk sec/Write --(should get individual disks counters)
PhysicalDisk(_Total)\Current Disk Queue Length --(should get individual disks counters)
MSSQL\Processor(_Total)\% Processor Time
Process(sqlservr)\% Processor Time
SQLServer:Access Methods\Full Scans/sec
SQLServer:Access Methods\Index Searches/sec
SQLServer:Access Methods\Page Splits/sec
SQLServer:Buffer Manager\Buffer cache hit ratio
SQLServer:Buffer Manager\Free pages
SQLServer:Buffer Manager\Lazy writes/sec
SQLServer:Buffer Manager\Page life expectancy
SQLServer:Buffer Manager\Pages/sec
SQLServer:Memory Manager\Memory Grants Pending
SQLServer:Memory Manager\Target Server Memory (KB)
SQLServer:Memory Manager\Total Server Memory (KB)
SQLServer:Plan Cache(_Total)\Cache Hit Ratio
SQLServer:SQL Statistics\Batch Requests/sec
SQLServer:SQL Statistics\SQL Compilations/sec
SQLServer:SQL Statistics\SQL Re-Compilations/sec
Procesor: % PrivilegeTime
System: Processor Queue Length
System: Context Switches/Sec
Memory: Available Mbytes
SQLServer:Locks Average Wait Time (ms)
SQLServer:Locks Lock Waits /sec
SQLServer:Locks Lock Wait Time (ms)
PagingFile: %Usage
PagingFile: %Usage Peak
Thank You,
Best Regards,
SQLBuddy
January 20, 2011 at 3:12 pm
Don't put much, if any, meaning into the queue length counters (as the podcast I linked to explained) as they are near-meaningless on SAN or other shared storage arrays. In addition, SQL's IO patterns intentionally drive the queue length high during read-aheads.
The page splits/sec is also near-meaningless as it doesn't distinguish between page splits in the middle of an index (which are usually bad) and page allocations at the end of the index (which are fine). See the presentation Paul Randal did Tuesday for more detail. http://sqlskills.com/BLOGS/PAUL/post/PASS-Performance-VC-recording-and-slides-on-index-fragmentation-available.aspx
The processor queue length is useless on a SQL server machine, as SQL doesn't queue up threads for window's scheduler, it uses its own scheduler. SQL Server will only keep as many windows threads runnable as there are processors allocated (these are SQL's schedulers). It then internally schedules SQL threads on and off those, but that's out of sight of the windows scheduler and hence won't reflect in the processor queue length.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2011 at 9:28 pm
Follow Gail's advice. She made a very good explanation.
Thank You,
Best Regards,
SQLBuddy
January 25, 2011 at 2:34 pm
Thanks for all the advice, I have managed to get hold of a workstation that I can use as my monitoring 'server', so I shall have a play.
One thing I have noticed is that perfmon is good for recording what has happened, for analysis at a later date.
How does perfmon differ from SQL Profiler, is that used in much the same way?
Sorry for so many questions, but I am new to the whole issue of performance monitoring for SQL Server, and I am trying to find a way that I can proactively monitor my SQL Servers.
What other tools are available?
Are there any good tools that would alert me if anything was happening on my SQL Server that I should be concerned about (i.e. as it is happening, so I can investigate)?
January 25, 2011 at 3:02 pm
Perfmon records counter values, values for certain measurements. Profiler captures complete events from SQL Server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 27, 2011 at 6:20 am
Everything that has been said thus far indicates that Perfmon counters can be used at a later date, to see what has already happened on a server.
Is it possible for Perfmon to act as a real-time monitor - to alert you when a certain threshold has been reached, such as CPU?
Thanks for all your help
Barry
January 27, 2011 at 8:01 am
barryFS (1/27/2011)
Everything that has been said thus far indicates that Perfmon counters can be used at a later date, to see what has already happened on a server.Is it possible for Perfmon to act as a real-time monitor - to alert you when a certain threshold has been reached, such as CPU?
Thanks for all your help
Barry
Yes. It's strongly suggested that you collect the counters into files for later use. The PerfMon utility can be used to create Data Collector Sets that output to a file. You can modify these so that they only collect data over certain periods, once every 10 seconds or once every 5 minutes, set the counters you want, and output it all to a file which you can use later for reporting or whatever. You can even take perfmon counter sets and combine them with the data from trace events to see what's happening on the server as queries are called.
"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
January 27, 2011 at 9:44 am
Follow the Great advice from Grant. He is Right.
Performance monitor is a very powerful tool. It can always be used for real time monitoring. You can add the required counters and do real time monitoring or collect the counter values for a specfic period of time say 24 hours using either Perfmon counter logs or datacollector sets.
Also you can setup real time alerts using perfmon to notify you when a threshold has been reached say 90% CPU etc..
Use this method. It works very well.
http://www.sqlservercentral.com/articles/Administration/smtpsqlserverperformancealerts/2005/
Thank You,
Best Regards,
SQLBuddy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply