SQL memory Performance counters review and help

Question

SQL memory Performance counters review and help

Database admin(DBA)

SSCertifiable

Points: 5373
More actions
October 19, 2017 at 1:09 pm

#340893

Hi All,
Need your help to understand that my DB server is require more memory or not as per the below counters which I have observed some time apart from top 10 memory & long running queries.
As per my understanding below target and total server memory values are different and how should I understand that my DB server is optimal ? and PLE value also different?
Memory:
Available Mbytes     421.002
Page Faults/sec        2.434.009
Pages Input/sec     40.002
Page output/Sec     22.987
SQL server:Buffer Manager
Buffer Cache hit ratio   99.92
Lazy writes/sec       0.00
Page life expectancy 9.333.458
Page reads/sec     3.851
Pages writes/sec 5.072
SQL server :Memory Manager
Memory Grants pending 0.00000
Target server memory (KB) 11.688,920.297
Total server memory(KB)   11,674,150.049
Please help me to understand more efficient way as per above perform counets?
Any help would be greatly appreciated.
Thanks in advance!!
Best Regards,
DBA
SQL server DBA

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Database admin(DBA) SSCertifiable Points: 5373 More actions · Answer 1

Database admin(DBA) - Thursday, October 19, 2017 1:09 PM
Hi All,
Need your help to understand that my DB server is require more memory or not as per the below counters which I have observed some time apart from top 10 memory & long running queries.
As per my understanding below target and total server memory values are different and how should I understand that my DB server is optimal ? and PLE value also different?
Memory:
Available Mbytes     421.002
Page Faults/sec        2.434.009
Pages Input/sec     40.002
Page output/Sec     22.987
SQL server:Buffer Manager
Buffer Cache hit ratio   99.92
Lazy writes/sec       0.00
Page life expectancy 9.333.458
Page reads/sec     3.851
Pages writes/sec 5.072
SQL server :Memory Manager
Memory Grants pending 0.00000
Target server memory (KB) 11.688,920.297
Total server memory(KB)   11,674,150.049
Please help me to understand more efficient way as per above perform counets?
Any help would be greatly appreciated.
Thanks in advance!!
Best Regards,
DBA

Waiting for experts suggestions on this.

Thanks in advance!!

SQL server DBA

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Database admin(DBA) - Thursday, October 19, 2017 1:09 PM
Hi All,
Need your help to understand that my DB server is require more memory or not as per the below counters which I have observed some time apart from top 10 memory & long running queries.
As per my understanding below target and total server memory values are different and how should I understand that my DB server is optimal ? and PLE value also different?
Memory:
Available Mbytes     421.002
Page Faults/sec        2.434.009
Pages Input/sec     40.002
Page output/Sec     22.987
SQL server:Buffer Manager
Buffer Cache hit ratio   99.92
Lazy writes/sec       0.00
Page life expectancy 9.333.458
Page reads/sec     3.851
Pages writes/sec 5.072
SQL server :Memory Manager
Memory Grants pending 0.00000
Target server memory (KB) 11.688,920.297
Total server memory(KB)   11,674,150.049
Please help me to understand more efficient way as per above perform counets?
Any help would be greatly appreciated.
Thanks in advance!!
Best Regards,
DBA

For a lot of these numbers, there appears to be a mix of commas and dots in terms of representing the numbers. If you round the numbers to whole numbers, I would guess your PLE isn't really 9 and your target server memory isn't 12.
If target server memory is 11,688,920,297 and total server memory is 11,674,150,049 the ratio is fine. You won't see them be exactly the same.
Not sure what you mean by PLE being different. The formula often seen for PLE (if you ignore the 300 which you should) is GB Max memory / 4 * 300
You can find some of these referenced in the following article -
Top SQL Server Memory Pressure Counters

Sue

Database admin(DBA) SSCertifiable Points: 5373 More actions · Answer 3

Sue_H - Saturday, October 21, 2017 8:35 AM
Database admin(DBA) - Thursday, October 19, 2017 1:09 PM
Hi All,
Need your help to understand that my DB server is require more memory or not as per the below counters which I have observed some time apart from top 10 memory & long running queries.
As per my understanding below target and total server memory values are different and how should I understand that my DB server is optimal ? and PLE value also different?
Memory:
Available Mbytes     421.002
Page Faults/sec        2.434.009
Pages Input/sec     40.002
Page output/Sec     22.987
SQL server:Buffer Manager
Buffer Cache hit ratio   99.92
Lazy writes/sec       0.00
Page life expectancy 9.333.458
Page reads/sec     3.851
Pages writes/sec 5.072
SQL server :Memory Manager
Memory Grants pending 0.00000
Target server memory (KB) 11.688,920.297
Total server memory(KB)   11,674,150.049
Please help me to understand more efficient way as per above perform counets?
Any help would be greatly appreciated.
Thanks in advance!!
Best Regards,
DBA
For a lot of these numbers, there appears to be a mix of commas and dots in terms of representing the numbers. If you round the numbers to whole numbers, I would guess your PLE isn't really 9 and your target server memory isn't 12.
If target server memory is 11,688,920,297 and total server memory is 11,674,150,049 the ratio is fine. You won't see them be exactly the same.
Not sure what you mean by PLE being different. The formula often seen for PLE (if you ignore the 300 which you should) is GB Max memory / 4 * 300
You can find some of these referenced in the following article -
Top SQL Server Memory Pressure Counters
Sue

Thank you sue for your quick response.

Can you please provide the tsql script for top 10 memory query and also,which query occupying more memory on the server ? I tried to find out but no luck.

SQL server DBA

Jeff Moden SSC Guru Points: 1004701 More actions · Answer 4

Database admin(DBA) - Thursday, October 19, 2017 1:09 PM
Hi All,
Need your help to understand that my DB server is require more memory or not as per the below counters which I have observed some time apart from top 10 memory & long running queries.
As per my understanding below target and total server memory values are different and how should I understand that my DB server is optimal ? and PLE value also different?
Memory:
Available Mbytes     421.002
Page Faults/sec        2.434.009
Pages Input/sec     40.002
Page output/Sec     22.987
SQL server:Buffer Manager
Buffer Cache hit ratio   99.92
Lazy writes/sec       0.00
Page life expectancy 9.333.458
Page reads/sec     3.851
Pages writes/sec 5.072
SQL server :Memory Manager
Memory Grants pending 0.00000
Target server memory (KB) 11.688,920.297
Total server memory(KB)   11,674,150.049
Please help me to understand more efficient way as per above perform counets?
Any help would be greatly appreciated.
Thanks in advance!!
Best Regards,
DBA

You should do a Yabingooglehoo for some of the counters you have listed above. For example.

https://serverfault.com/questions/273201/what-is-a-lot-of-page-faults
https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-memory-usage
https://technet.microsoft.com/en-us/library/cc958290.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004701 More actions · Answer 5

p.s. Also... in about 99% of the cases I've run across, performance issues usually don't have much to do with hardware, per se`. Usually, it's just really poor code unnecessarily beating the hell out of resources.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Database admin(DBA) - Sunday, October 22, 2017 4:30 AM
Thank you sue for your quick response.
Can you please provide the tsql script for top 10 memory query and also,which query occupying more memory on the server ? I tried to find out but no luck.

I'm not sure under what context you mean memory usage.
Top 10 memory query could be based on memory grants but when you say occupying more memory that sounds more like buffer pool usage. You could also look at query stats and get an estimate of memory usage based on the logical reads. But there are ways to check all of those.
For memory grants, check this article which explains memory grants and has some queries towards the end to check the memory grants in the cache:
Understanding SQL server memory grant

Glenn Berry also has some memory queries and includes a query to check the logical reads of cached stored procedures. There is also a query to check the buffers used in a particular database:
SQL Server Memory Related Queries

In terms of checking the buffers used by database, you can check which databases are using the most buffers and then look at the buffers used by that database. To find the databases using the most cache, you can query sys.dm_os_buffer_descriptors which has a row for every page in cache. Something like:
SELECT COALESCE((db_name(database_id)), 'SystemResource') as DBName, COUNT(*) as Pages, COUNT(*) * 8 / 1024 as MBUsed FROM sys.dm_os_buffer_descriptors GROUP BY database_id ORDER BY COUNT(*)desc

Sue