SQL performance checklist

If you have a production SQL Server that has low memory, where do you start when trying to find out the root cause? I.e. 4GB of ram but only a few hundred MB left available during peak times. Do you have a specific checklist of common issues/or perhaps even common misconfigurations you would look for to see if you can track down the root cause? These are typically for dedicated SQL Servers not running any other apps but a couple are dual purpose servers, i.e. also an application server.
LVL 4
pma111Asked:
Who is Participating?
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
First of all check the memory configuration of the SQL Server. Nowadays 4GB may be not enough for a SQL Server instance.
Also provide more information.  OS version, MSSQL version, machine architecture, ....
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
PadawanDBAOperational DBACommented:
One thing I would stress, is you want to make sure you are optimally using what memory you do have available.  Check the plan cache to make sure it isn't bloated with single use plans.  There is an instance level configuration (optimize for ad hoc workloads) that you can enable to help with that if you find that being the case:

select
	sum( size_in_bytes ) / 1048576 as Size_in_MB
from
	sys.dm_exec_cached_plans
where
	usecounts < 2;

Open in new window


Also, you may want to specify maximum memory thresholds for your SQL Server instances to ensure that the buffer pool isn't being paged out because the OS is starved for memory.  You can also start going through the plan cache for the most commonly executed queries and start tuning those from the perspective of reducing logical reads (which loosely correlates to overall query performance).  The other, and possibly more flippant, response is to add more memory.  4 GB is pretty low for a SQL Server...  And honestly your time is probably better spent elsewhere, so the most cost effective solution may not be huge amounts of tuning - it takes time and as the saying goes, time is money.
0
HainKurtSr. System AnalystCommented:
4GB! we have 32GB for our everyday / gaming machines... 4GB for a server, today is not acceptable :)
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

pma111Author Commented:
>>First of all check the memory configuration of the SQL Server.

How do you mean "check the memory configuration"? Do you mean check the spec of the server, or something else?
0
pma111Author Commented:
Do MS themselves have any specific guidelines on how much memory is typically required for the various versions of their SQL product (appreciate not every SQL Server will be under as much stress as each other)...
0
Scott PletcherSenior DBACommented:
>> but only a few hundred MB left available during peak times <<

If SQL is leaving hundreds of MB unallocated during peak times, it doesn't seem like it's a memory issue.  SQL will grab all the memory it can -- unless it's stopped by the max memory setting -- so it doesn't need it if it didn't take it but was allowed to.  Verify the value in the max memory setting.

There's no way to know if 4GB is enough or not without knowing about the db usage.  4GB could be plenty, it could be way too low, can't tell without more info.
0
Scott PletcherSenior DBACommented:
Is this for SQL2005 or later than SQL2005?  The way to check to see if SQL flagged memory as being low is different between the two versions.
0
HainKurtSr. System AnalystCommented:
it does not matter what version he has, 4GB memory for a server is not enough! more ram more performance!
unless your server is XP or 32 bit OS :)
0
Scott PletcherSenior DBACommented:
What if that SQL only uses 2GB of data a day!

It is impossible to know whether that server needs more RAM or not based on what we know so far.

Is the RAM currently paging/thrashing?
Then, how much RAM does it need?  How do you know?
Are there low RAM conditions in SQL?  Have you checked to see if SQL indicates that?  Wouldn't SQL itself know better than us with zero knowledge of the dbs and app(s) if it needs more RAM?!

From the little we know so far, it could be an I/O or network/communication issue, or even badly fragmented (or corrupted) indexes.
0
HainKurtSr. System AnalystCommented:
4GB is just ridiculous nowadays, even for personal use... even my $400 lappies have 8GB min...

Discussing this is really waste of time... maybe 4GB of ram is ok if your DB size is 1-2GB max, and your server does not have ANYTHING on it, and just a few users are connecting to it :)

more memory more performance, up to a point... dont discuss anything here and go & upgrade it to 16GB min... then check the performance...
0
HainKurtSr. System AnalystCommented:
also, how much are rams? $10 per gig...

if you cannot spend 16 x 10 = $160 for ram on a production server, it means you are out of business already :)
0
Scott PletcherSenior DBACommented:
Extremely simplistic "hard" rules work best for some people.  Don't think just assume the defaults are always best!
0
PadawanDBAOperational DBACommented:
Poor architecture scales poorly, regardless of the cost =)

In addition to investigating the efficiency of SQL Server's use of it's allocated memory, I think it would be wise for you to setup a collector set in perfmon to look at some other metrics around memory.  Especially around indicators of external memory pressure, so take a look at your page writes/sec as well as your faults (especially hard faults).
0
Scott PletcherSenior DBACommented:
I know plenty of companies that use less than 16GB on quite a few of their virtual servers, some of which are db servers.  I suspect this might be a virtual server, just based on the RAM.

What happens to your credibility when you demand 16GB of RAM and it does (almost) nothing to improve performance?
0
HainKurtSr. System AnalystCommented:
if you have more ram, all operations will be much quicker... all data can be read from ram, all sort operations uses ram...
if you dont have ram, hdd will be used and your operations will be hundreds of times slower...
0
Scott PletcherSenior DBACommented:
Yes, we all know how ram works.

But that is ONLY if RAM is actually an issue on the server.  If SQL doesn't need more RAM, adding it won't necessarily improve performance at all.  If, for example, there's a bad disk controller (quite common, and it destroys performance ... and eventually your db), a bad network card, etc..  You've just delayed solving the actual problem.

[In theory, they might even be using SQL 2008 Workgroup Edition, which has a limit of 4GB anyway.]

Btw, if you do add RAM, you need to be sure to check the 'max memory' setting in SQL too, otherwise SQL may not be able to use the extra RAM.

If they don't currently have a RAM limit set in SQL, that will cause a problem as well, since SQL could easily use all 4GB causing everything on the box to become extremely slow.  Maybe that's a (part of) the problem??
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
How do you mean "check the memory configuration"? Do you mean check the spec of the server, or something else?
No. I mean what values are configured for min and max server memory?
0
pma111Author Commented:
>No. I mean what values are configured for min and max server memory?

the defaults.
0
pma111Author Commented:
>Is this for SQL2005 or later than SQL2005?

They are all SQL 2008 R2 and one version of 2012.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
only a few hundred MB left available during peak times
If you are working with 64bits versions of Windows Server, those few hundreds aren't enough. How easily can you provide more RAM to the machines?
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
Microsoft SQL Server

From novice to tech pro — start learning today.