Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Performance counter for SQL Server

Posted on 2014-01-22
5
Medium Priority
?
677 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 1000 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 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
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…
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…

877 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