Solved

SQL performance checklist

Posted on 2014-12-19
20
98 Views
Last Modified: 2015-01-06
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.
0
Comment
Question by:pma111
  • 6
  • 5
  • 4
  • +2
20 Comments
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 167 total points
ID: 40509309
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
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 167 total points
ID: 40509334
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 40509377
4GB! we have 32GB for our everyday / gaming machines... 4GB for a server, today is not acceptable :)
0
 
LVL 3

Author Comment

by:pma111
ID: 40509493
>>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
 
LVL 3

Author Comment

by:pma111
ID: 40509504
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 166 total points
ID: 40509516
>> 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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40509849
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 40510133
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40510140
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 40510197
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 51

Expert Comment

by:HainKurt
ID: 40510201
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40510202
Extremely simplistic "hard" rules work best for some people.  Don't think just assume the defaults are always best!
0
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 40510203
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40510204
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 40510205
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40510207
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40512492
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
 
LVL 3

Author Comment

by:pma111
ID: 40512549
>No. I mean what values are configured for min and max server memory?

the defaults.
0
 
LVL 3

Author Comment

by:pma111
ID: 40512558
>Is this for SQL2005 or later than SQL2005?

They are all SQL 2008 R2 and one version of 2012.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40512562
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now