dbcc memorystatus results - main columns / results to check

SQL instance memstatsSQL instance memstatsHi Experts,
 
I have an SQL server that is a VM (running in Vsphere) and has 8GB allocated to a database instance. We sometimes encounter performance issues in an application that has databases withing this instance and we want to find out if there are any memory bottlenecks / if we need to look at increasing the memory allocated to the DB instance.

I have run the DBCC memory status command and have attached a screenshot of the results (Main results at the top, there are more figures when I scroll down so I can see if there is a way to export the data if need be) but i'm not sure what the results determine. Please can you point me in the right direction in terms of which column names? & figures to check?  

Thanks
sherlock1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
screenshot ?
it's an invisible one at the moment ...
0
sherlock1Author Commented:
Screenshot now attached (apologies thought id attached before)
0
sherlock1Author Commented:
just to add in case this info is of use - At the server level we have 48GB RAM. There are multiple database Instances, its only the one instance in question that we want to review the memory / check if 8GB is enough

The server O/S is Windows Server 2012 R2 Standard
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Brian CroweDatabase AdministratorCommented:
Instead of regurgitating I thought I would just send you to the source...

https://www.brentozar.com/archive/2013/09/how-to-prove-your-sql-server-needs-more-memory-video/

One of the key indicators you want to look at is your page life expectancy and how it is trending.  If this value is low then your instance is constantly having to get what it needs from I/O instead of from the buffer cache.  If this value is ever-increasing then you have more memory than you need because you essentially have pulled your entire "utilized" database into memory.
0
ZberteocCommented:
MS-SQL server works on the principle that it runs everything in memory if possible. In an ideal world the memory is big enough to store all the databases data in it. However that is not the case, yet! In this respect you will have to assign as much memory to the SQL server is possible but allocation all that the host has available doesn't make much sense because the OS nees some too for its background processes. If no other applications or services are running on the host machine, be it a stand alone one or a VM then, you can go and allocate about 80-85% of the host available memory. So if the server has 32GB of memory then you can assign to the SQL server 25-28 GB of memory. That should be enough.

Details of how you do that you can find here:

https://www.mssqltips.com/sqlservertip/4182/setting-a-fixed-amount-of-memory-for-sql-server/
0
sherlock1Author Commented:
Thanks for the feedback. The SQL server has multiple DB instances so I wouldn't wish to just go ahead and assign more memory from the existing 48GB assigned to the VM since different amounts of memory are assigned to difference instances.

Thats why id firstly like to establish if the specific instance in question (this is the one in which the database sit on in which users have seen performance issues) has enough memory and I thought the results provided in the screenshot of the dbcc memorystatus could help determine this but I do not know what columns and what figures i should focus on from the results? and whether the current 8GB allocation for the instance in question is enough. Any guidance / clarification on which columns / figures I need to look for / if the figures look ok is what im asking on this question

If the results show that 8GB is enough for the instance then that is good to know and we can look for potential issues elsewhere
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You have 16GB RAM in the server and only using half (8GB) for the SQL Server instance. Why's that?
And any special reason to have Min Server Memory = Max Server Memory?

My recommendation is to you to use the following configuration to see if it helps:
Min Server Memory = 0 MB
Max Server Memory = 12288 MB

Let it running with these configurations for some hours with regular activity and then run DBCC MEMORYSTATUS again and compare the new results with old one.
0
sherlock1Author Commented:
Where have you got the 16gb figure from? As I said in my earlier message the server has 48gb are you saying you see 16gb somewhere?

The server has multiple instances that’s why 8GB has been allocated to this specific instance

I’m not sure why the min memory figure has been set as the max that did seem strange to me

I can of course re run the command at different times fir comparison but my question is what are the results of the command showing now?

What’s are the key columns to looo for / showing?

Anything indicate more memory needed or do results show current memenory allocation fine?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, you've a VM and I was seeing the Physical Memory information, that is even more than 16GB.
The VM Reserve states the 64GB of RAM.
How many SQL Server instances do you have in that server? They might be fighting for the same resources and that's why you're having the issue. It will be better if you can have a VM for each of those SQL Server instances. For example a 16GB RAM VM per SQL Server instance.
0
ZberteocCommented:
In that case you would have to determine or appreciate how to distribute the memory between the instances but one think you have to make sure that the total memory they will grab will NOT max out the host memory of 48GB. Firts step I would do is to use the 85% of the 48GB, which is 40GB, and divide it by the number of instances you have on that machine and use that as the max limit on each instance! So if you have 5 instances make sure the max value is 8GB on each of them!

However, no DBCC run on memory will show you what is the actual reason for the bottleneck, that is not what is for! The best way to determine the bottlenecks are the waits statistics and it very well may be the case that the memory is not even an issue. Most of the cases the performance problems come from poorely written queries, bad design on objects, missing indexes or even disk or network latency. All these kind of resources have their own wait types allocated and the time SQL engine is spending on them is constantly summarized since the last start of the SQL service. Those will tell you exactly what are the resources the SQL engine spends the most time on. If it is on the memory or the I/O waits means that increasing the MAX memory limit on that particular instance could help but at the same time you should decrease it accordingly on other instance that doesn't show any kind or at least significantly less of memory pressure. This is a tricky process, is not quite straight forward and needs quite a bit of experience. You should look for materials on the net that talk about the wait statistics on SQL and learn how  to use them.

One place to start learning about wait stats is here:

https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

but then you can learn more:

https://www.brentozar.com/sql/wait-stats/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sherlock1Author Commented:
Many thanks all for you input on this, some really great feedback & weblinks on this. lots to think about and lots of further reading to do it seems
certainly as a minimum points me in the right direction now on what to do next

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.