question

Vishu-2318 avatar image
0 Votes"
Vishu-2318 asked AmeliaGu-msft commented

Performance counters

Please advise if the below counters obtained from sys.dm_os_performance_counters, specifies a performance issue :


Target Server Memory (KB) 150993928
Total Server Memory (KB) 149331248
BatchRequestsPerSecond 1225
BufferCacheHitRatio 99.99992088
PageLifeExpectency 5155
LazyWritesPerSecond 0
SQLCompilationsPerSecond 97
MemoryGrantsPending 0
PageLookupsPerSecond 1282128
PageReadsPerSec 2
PageWritesPerSecond 261

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Vishu-2318,

Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi Vishu-2318,
In addition, Total Server Memory stays significantly less than Target Server Memory, this could indicate:
1) You may have allocated much more memory than SQL can use – SQL could cache the entire databases into memory, and if the databases are smaller than the amount of memory on the machine, the data won’t take up all the space allocated to SQL. So in this case Total Server Memory (actually memory being used by SQL) will never reach Target Server Memory (amount allocated to SQL). Or,
2) SQL cannot grow the buffer pool because of memory pressure from outside of SQL. If this is the case, you need to either increase the Max Server Memory, or add more RAM to boost performance.

For Buffer Cache Hit Ratio, the recommended value for its value is over 90. 99.99992088 is better.

For Page Life Expectency, if your page stays longer in the buffer pool (area of the memory cache) your PLE is higher, leading to higher performance as every time request comes there are chances it may find its data in the cache itself instead of going to the hard drive to read the data. 5155 is fine.

Lazy Writes Per Second value should be close to zero ideally.

SQL Compilations Per Second value should be at 10% or less than total Batch Requests/sec.

Memory Grants Pending value should be zero ideally.

The recommended Page reads/sec value should be under 90. Higher values indicate insufficient memory and indexing issues.

The same as Page reads/sec, the recommended value for Page writes/sec is below 90.

Please refer to this article and SQL Server Memory In Task Manager which might help.

Best Regards,
Amelia


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

They don't of the simple reason that what defines a performance issue are users that are kicking and screaming because they don't get a response in what they think is a reasonable amount of time.

Of the numbers you post, BatchRequestsPerSecond stands out as high to me. Then again, if SQL Server is able to serve that many batch requests it seems to be doing fine. I'm kind of wondering what the application may be up to though....

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.