question

Troy_2012 avatar image
Troy_2012 asked

How to read counters for SQL server performace

Dear All, I'm confusing how to read the counters through the performance monitor ,So I selectd a counter so can one of the expert explain how to read this or other counters : 1- **Object** Processor, **Counter:** %Processor , The best performance value that we want to see: **< 80%** I did a monitoring for my laptop to get some values : ![alt text][1] [1]: /storage/temp/792-cpu.jpg 1- How we can read these values for **Average :7.019** , Min: 1.434 , Max:21.610 Is this number in Byte or KB ..? 2-Which value we used (Last ,Average ,Min,Max) and how to convert to check if < 80 %? 3- Do we need to change the Scale?
sql serverperformance counters
cpu.jpg (62.2 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
1. It's the %age of processor use - the rest of the time is idle time. It's not a number with a unit. 2. It's already a percentage. Watch the Average. 3. No. Performance monitoring is a way bigger subject than just the CPU usage. See, for example, the Red Gate book on the subject: http://www.red-gate.com/community/books/dynamic-management-views
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Troy_2012 avatar image
Troy_2012 answered
Hi ThomasRushton, you right,I have punch of counters for OS Memory & Paging Performance Counters I will use - Memory-Available Mbytes. - Memory-Pages Input/Sec. - Memory-Pages/Sec. - Paging File-%Usage. - Paging File-%Usage Peak MSSQL Data Access Performance Counters - SQLServer: Buffer Manager: Buffer cache hit ratio. - SQLServer: SQL Statistics: Batch Requests/Sec. - SQLServer: Locks: Lock Waits / Sec: _Total. - SQLServer: General Statistic: Processes Block But as I explained I have a confusing with reading these counters for Object Processor, Counter: %Processor your explanation very good ,now If you don't mined what about these counters : ![alt text][1] For **Memory-Available Mbytes** the Best Value we need in GB ,and as you explained the Average is **29,743.769** so this number mean in MB I'm good (Correct me If I'm wrong) For **SQLServer: Buffer Manager: Page life expectancy** If this value is less than **300**, it indicates that there is pressure on the buffer pool mainly because of page reads,from the Image the **Average** is **73,679.008** ,But here the Scale is **0.1** . Do I need to divide the **Average** by **Scale (0.1)** then take the resulat and compare it with **300** [1]: /storage/temp/793-counter2.jpg

counter2.jpg (63.7 KiB)
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The scale applies to the graph display, not to the raw numbers.
0 Likes 0 ·
So my analysis is correct. SQLServer: Buffer Manager: Page life expectancy ( Average is 73,679.008 ) >300. But why with the CPU you said it's already a percentage.
0 Likes 0 ·
To be careful with the question some counter require comparative percentage as CPU processor counter we took the Average, How about PAGING FILE-%Usage the Preferred Value is < 70% so we will take the average or Not, and from the image I uploaded the Doc that mentioned (Reference or Suggested value=KB 889654 ) what is that mean ?
0 Likes 0 ·
Troy_2012 avatar image
Troy_2012 answered
![alt text][1] [1]: /storage/temp/800-counter3.jpg

counter3.jpg (35.5 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Troy_2012 avatar image
Troy_2012 answered
This infromation from real Server ,How we read the values for Average , Min, Max ? Are these numbers reached the risk **>80%** Please Could someone help me ? experts please ? ![alt text][1] [1]: /storage/temp/801-cpu333.jpg

cpu333.jpg (41.0 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.