Solved

Performance counter for SQL Server

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
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…

685 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