SQL 2005 Server Performance and Maximum Memory property
-
A query was taking 20 seconds and consuming 70% CPU takes only 1 second after setting Maximum Memory property to 2048 MB - why?
Server:
OS Microsoft(R) Windows(R) Server 2003, Enterprise Edition
Version 5.2.3790 Service Pack 1 Build 3790
8 GB memory
Two Dual-core AMD Opteron 285 2.6GHz Processors
Server is not configured for AWE
Fiber channel connection to EMC Clarion - two LUNs - one for MDF, one for LDF
SQL 2005
SQL 2005 32 bit Standard Edition - SP1 (version 9.0.2047)
Three instances installed on server - only one instance in use
Binaries and system databases on local mirrored disk
Database file (MDF) on one EMC LUN - dedicated physical drives
Log file (LDF) on one EMC LUN - dedicated physical drives
Query in question:
SELECT TOP 10 Address.Address1, Address.Address2, Address.City, Address.County, Address.State, Address.ZIPCode, Address.Country, Client.Name,
Quote.Deleted, Client.PrimaryContact, Client.DBA, Client.Type, Quote.Status, Quote.LOB, Client.ClientID, Quote.QuoteID, Quote.PolicyNumber,
Quote.EffectiveDate, Quote.ExpirationDate, Quote.Description, Quote.Description2, Quote.DateModified, Quote.DateAccessed, Quote.CurrentPremium,
Quote.TransactionDate, Quote.CreationDate, Quote.Producer FROM ((Client INNER JOIN Address ON Client.ClientID = Address.ClientID) INNER JOIN Quote ON
Client.ClientID = Quote.ClientID) WHERE (Quote.Deleted = 0) AND ((Address.AddressType)='Mailing') ORDER BY Client.Name
Address table - 161,075 rows
Client table - 161,634 rows
Quote table - 59,145 rows
With default maximum memory setting (2,147,483,647 MB) - query runs in 20 seconds and consumes over 70 % of the CPU.
After changing maximum memory setting to 2048 MB, query runs in less than 1 second.
Question is:
What is the best practice for setting the minimum and maximum memory settings for SQL 2005?
What can be monitored to identify the cause of these type of issues - using profiler, PerfMon, other tool?
Thanks
-
You sure it was 2,147,483,647 MB? That's two billion mega bytes... no wonder the server was having a hard time swallowing that
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
This is the default value, set by the SQL Server after new installation. It really shows 2,147,483,647 MB in the properties. With this setting the SQL Server manages the use of memory dynamically, and it uses as much RAM as available if needed.
-
If you let SQL try to use more memory than is available on the machine, then it will try to do this. Your original query probably took so much time because SQL was fighting with Windows about memory. I am thinking lots of paging, etc.
By setting a max memory size that prevents memory contention, you also set the limits that SQL knows it needs to work in. Therefore the query can run faster.
If your 8 GB box is dedicated to SQL, then to maximise SQL performance you need to allocate more memory to SQL. First, make sure Windows has been booted with the /3GB and /PAW switches in boot.ini (you may need to get your Infrastructure prople to do this). Next, set a reasonable maximum memory limit for SQL. For us, we would set SQL memory to be 80% of total memory on a box this size, but if you run a lot of other work (including SQL Agent jobs) that need their own memory then you may need to set SQL to less than 80%. Finally make sure that AWE is enabled, then restart SQL.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
I have found that SQL 2005 manages memory dynamically quite well. I have found minimal performance gains by setting the minimum and maximum memory for a server. Now, I have only done testing on this in four environments, but they have ranged from 250gb databases to 30gb databases and on servers with 4gb to 16gb of memory so I think it has been a good cross-section. I give MS credit for this - they managed to get it to handle dynamic memory allocation far better than SQL 2000 did.
I would suggest you examine the execution plans before and after making your change. It is likely that by restricting the memory, you convinced SQL to build an execution plan that works better. You also need to be sure to look our for other variables. Make sure nothing is cached that could impact these results. A good strategy would be to set the memory configurations, restart the server, and run the query 10 times. Then make the cnfiguration change, reboot the server again, and run it 10 times again with the new settings. This will help to eliminate any system variables.
-
I only had the chance to work with 2k5 once, so far, but I did notice other strange (different) default settings... for example, "Concatenate Null Yields Null" was turned off instead of on.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
EdVassie - Thanks for your response
We are planning to enable AWE to get this system to utilize the memory we have installed.
Question - are there any Microsoft documents that discuss how SQL and the OS manage the server memory?
-
BOL has some good information. If you are installing SP2, get the latest available BOL update. Otherwise Google can find you things.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Hi,
Maybe there's bug in Management studio, but 2,147,483,647 (2^31-1) means "All available physical memory at the moment of Instance creation", and should not be interpreted as megabytes.
-
there seems to be just so much confusion over memory settings on sql server :-
to see exactly what memory is being used run this query
select
* from sys.dm_os_performance_counters where object_name like '%memory%'
now as far as I know 32 bit sql server 2005 is no different to sql 2000 and unless you enable awe it uses about 1.7gb of ram, certainly all the 32 bit sql 2005 servers I use have awe set to on to enable upper memory.
If you enable awe then you must set max memory otherwise you'll find sql server starves the o/s of memory. NB. for 64bit versions you must set max memory as sql server takes all the memory by default. Can't say about running 32 bit on 64 bit o/s.
Best practice says you should fix all the memory settings on a sql server install, assuming you have a dedicated server who or what would you want to allocate memory to by using dynamic memory or is there a belief that if you free up memory the chips will last longer?????? You fix your memory settings, dynamic memory is just bad news and when you move to 64bit you will get burned!
By default w2k3 should have the /PAE switch enabled so you just need to enable awe on 32 bit.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
This is for 32bit SQL 2005 (as 64 bit SQL 2005 is a bit different):
If you enable the /PAE switch and the AWE option, you will want to set the MAX memory for your server/instance. Also, you will want to lock pages in memory (see http://msdn2.microsoft.com/en-US/library/ms190730.aspx).
Make sure to reindex and update your statistics.
Someone asked about books/articles on memory management in SQL. I enjoyed reading "Inside Microsoft SQL Server 2005: The Storage Engine" by Kalen Delaney.
Accepting all invitesViewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply