Performance counter for SQL Server
Posted on 2014-01-22
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