Solved

SQL performance checklist

Posted on 2014-12-19
20
101 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 47

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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:Scott Pletcher
Scott Pletcher 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:Scott Pletcher
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:Scott Pletcher
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
 
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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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 47

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 47

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Addition to SQL for dynamic fields 6 37
string fuctions 4 25
SQL Server 2012 r2 - Varible Table 3 23
[SQL server / powershell] bulk delete table from CSV 8 30
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

786 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