Solved

Performance counter for SQL Server

Posted on 2014-01-22
5
626 Views
Last Modified: 2014-01-26
I need to collect some counters to study the performance for my SQL Server.

*Memory\Pages/sec : This measures the rate at which pages are read from or written to disk to resolve hard page faults. If the value is greater than 1,000, as a result of excessive paging, there may be a memory leak.
=> Is 1,000 is the threshold value as the measured value range from 0.1 - 1

*Disk(_Total)\Avg. Disk Write Queue Length : threshold is set to 2 * no of spindle. For spindle, is it talking about number of HDD ?

Processor Queue Length : threshold is 2 x number of CPU. However does it talk about physical CPU or core ?

Beside, what's threshold value for the following counter :
•      SQL Server:Buffer Manager\Buffer Cache Hit Ratio
•      SQL Server:Buffer Manager\Page Life Expectancy
•      SQL Server:SQL Statistics\Batch Requests/Sec
•      SQL Server:SQL Statistics\SQL Compilations/Sec
•      SQL Server:SQL Statistics\SQL Re-compilations/Sec


Tks
0
Comment
Question by:AXISHK
  • 2
  • 2
5 Comments
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 250 total points
ID: 39804543
Hi,

I've used this list quite successfully.
http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

Regards
  David
0
 

Author Comment

by:AXISHK
ID: 39805390
Thanks, but it does tell me the threshold for the counters that I mentioned above.

Tks
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 250 total points
ID: 39806227
see almost all sql perfmon counters collection
from the Quest poster
http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

•      SQL Server:Buffer Manager\Buffer Cache Hit Ratio --you want > 90 (for OLTP) or 80(for very large OLAP).
----------------------------------------------
•      SQL Server:Buffer Manager\Page Life Expectancy -- you want > 300
•      SQL Server:SQL Statistics\Batch Requests/Sec -> See
Description- used in combination
•      SQL Server:SQL Statistics\SQL Compilations/Sec -- > you want < 10% of the
number of Batch Requests/Sec
•      SQL Server:SQL Statistics\SQL Re-compilations/Sec --> you want < 10% of the
number of
SQL Compilations/
sec
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39807951
Hi,

I think that if you read the whole document, and then start looking at your results, the discussion will give you a fair idea of what good and bad numbers are.

Regards
  David
0
 

Author Closing Comment

by:AXISHK
ID: 39810977
Tks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now