Solved

Performance counter for SQL Server

Posted on 2014-01-22
5
636 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 43

Accepted Solution

by:
Eugene Z 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…

739 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