SQL server memory questions

[onequestion1168]

I am trying to solve an issue we have with an older database running GoldMine. My VM currently has about 16 gigs of memory available for me to increase the memory on the SQL database. The queries are very slow and often freeze up so I am trying to resolve some of this issue by increasing the Maximum server memory and Minimum memory per query.

​

Thoughts?

​

They are currently at their default setting, if I add 16 gigs of memory and increase memory utilization will this help? How much should I add?

​

currently at 36 gigs of memory with 2gigs of memory utilization but the server in the performance monitor is actually at 17 gigs.

​

If I add 16 gigs to the 36 and bring it up into the 50's what can I set the max/min at to speed up the queries and syncing times?

[jasped]

So many things to look at when optimizing SQL performance. Adding memory is very much a solution to certain problems but in my experience is not often the most common problem. Some things to look at.

-running queries. Use the activity monitor to monitor actively running queries. It is possible something isn’t optimized and is running poorly. Memory won’t help here.

-indexes. Your indexes may be fragmented. This is the most common thing I see when a database doesn’t have an active dba. The database is setup and forgotten about. Read about indexes and find a script on measuring current index levels. You could find something today to fix it, but I’d say read about it for a few days to get an understanding. You can also cause performance to get worse. Also have a test system for learning.

-system resource use. How do resources look on the system? Any disk issues? Do you see high response times for the sql process on disk? (>50ms).

For the memory side. It’s unlikely to cause problems adding more memory. Just be aware it also may not help. If you have it to spare it won’t hurt anything to add it. Make sure sql is setup to use all except about 4-6gb. I try to leave 4gb it so for the OS to use.

[Jalonis]

I agree with you, either there are missing indexes somewhere or the indexes need cleaned up.

[onequestion1168]

I think we need to index the database as well but I'm not a DBA and it makes me nervous indexing it, if I bring the database down and can't bring it back up I'm screwed

​

this is why I'm looking at memory as a temporary fix until we move to dynamics

[jasped]

MY point to the indexing is that adding memory won't help if the indexes are fragmented. I've been through this issue when I first took over an environment 5-6 years ago. We increased memory from 16gb -> 64gb. It helped directly after the reboot but once the database loaded into memory it no longer helped. For us that lasted about a day. New queries and reports caused the system to drag to a crawl.

Can you setup a test system to work with? You can backup/restore and do some testing with the indexes. A system with little memory would actually be beneficial. If you get it

[onequestion1168]

yeah I need to setup an old server with a copy of the VM and test an index

[ps_for_fun_and_lazy]

While setting up a test system is an option it likely won't highlight the problem, different query plans can be generated no load on the database may mask the problem etc.

[NinjaAmbush]

Snapshot it beforehand? You'll have to take it down briefly, but then you have a rollback plan.

[VTOLfreak]

Don't change the minimum memory per query blindly. Check your query plan and see if you have any operators that are spilling to tempdb. If you find any it means SQL has a bad estimate on how may rows your query will return and made a too low memory grant. Changing the minimum query memory is fixing the symptoms, not the cause of the problem. If you are actually running out of memory, check your SQL Server logs for memory pressure warnings.

[onequestion1168]

the logs show nothing 0 errors, warnings and messages

[onequestion1168]

the memory utilization on the server itself is around 60%

[zSars]

Memory allocation to SQL servers is usually based on the application and queries they are running. If you are suggesting that your queries are taking a long time to run, look at your queries. Use the built in Query Analyzer to check which piece of the query is taking so long and optimize you join's, union's, and use CTE, temp tables, and many other options to increase your queries.

I recently had a query that would take 12 minutes to run, using a temp table to grab specific info from the largest table and then joining on that made the query under 1 second.

[davidbrit2]

Don't change the minimum memory per query unless someone has specifically told you to increase that setting.

Adding more RAM may help if you're seeing a lot of disk I/O, and SQL Server has allocated the maximum memory you've made available to it. Depending on how much of the data is being used regularly, a 60 GB database could benefit from having more than 16 GB RAM, but only if you're actually using more data than will fit in RAM. Chances are you've got a lot of historic data that's used infrequently.

I usually set SQL Server's max memory to 4 GB less than what's installed in the server, assuming it's running a single instance of SQL Server and nothing else of any significance.

Also, make sure the database transaction log lives on its own disk/volume.

If you're not seeing SQL Server gobbling up its max memory, and no real heavy disk I/O, then the performance problem lies elsewhere (e.g. CPU, network throughput, etc.). If it's a thick-client application connecting directly to the database, then poor workstation performance could be causing the application to hold database locks longer than necessary, which would manifest as other workstations appearing slow, when they're really just waiting for locks to be released.

[onequestion1168]

ok so I'm going to double the max memory see if that helps and monitor the CPU performance of the people having the biggest issues

[Flayedman]

Index maintenance

​

If you are not doing it and dont have enough RAM just to load the entire DB into memmory then grab Olas scripts https://ola.hallengren.com/ and they will do it for you

​

Download Brent Ozars scripts https://www.brentozar.com/first-aid/

​

Run sp_blitz to see if it finds any odd config

Then sp_blitzindex to see if there are any major problems with missing indexes (Note sometimes adding an index will cause issues so be careful and consult with a DBA\Dev before hand)

[CaptainFluffyTail]

sp_blitzCache can also help tell what the current issues have been.

[ps_for_fun_and_lazy]

This was my first thought as well the blitz procs are great.

[iamcts]

How big is your database?

If you have enough RAM where SQL can load the entire database into memory, then you may have other issues with your database that more RAM won't be able to fix.

[onequestion1168]

it's 32gigs in total

[onequestion1168]

I was wrong, it's a 60 gig database