[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL performance checklist

Posted on 2014-12-19
20
Medium Priority
?
106 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +2
20 Comments
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 668 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 668 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 60

Expert Comment

by:HainKurt
ID: 40509377
4GB! we have 32GB for our everyday / gaming machines... 4GB for a server, today is not acceptable :)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 664 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 70

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 60

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 70

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 60

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 60

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 70

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 70

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 60

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 70

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 52

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 52

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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