Solved

Performance counter for SQL Server

Posted on 2014-01-22
5
630 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Master DB with Masterkey 1 34
User Being Logged Out of AD 6 65
Job - date manual 1 35
get_systemdrive info from tsql? 1 18
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

839 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