| Author |
Topic |
|
juancabrera
Starting Member
30 Posts |
 Posted - 2007-10-11 : 14:05:29
|
| Hello world!I have read conflicting articles, forums on the web about allocating memory to SQL Server. Is it true that the sqlsrvr.exe shouldn't be using more than 25% of the Total Server's memory?I have created 2 instances on my server. One is the production instance, and the other one is training instance. The production instance has about 7 databases and the training instance about 4 of them. The biggest one on the production instance is about 7.8 gigs, and the biggest one on the training instance is about 6.5 gigs. The combined space used by the production is about 20 gigs, and the training environment 15 gigs.The server currently has 8 gigs of physical ram. This is a 64-bit server with 4 processors. The operating system(OS) is recommending max allocation of memory to be around 12 gigs. AWE is not turned on on either instance.I have allocated 3 gigs of RAM to the production instance and 2 gigs of RAM to the training server. I also went with the OS recommendation and upped the Max Ram to 12gigs. So now I have 12gigs of virtual RAM. The commit charge on the server(Task Manager) is now below 5 gigs of RAM.My question is, how can I know that I am running with optimal performance as far RAM goes.Also, I noticed that some of the execution plans on my sql objects are pretty big, how can I manipulate them to make them smaller? Here is a breakdown:Select Top 70 ObjType, ObjectName = Obj.Name, SizeInMegs = ((P.Size_In_Bytes/1024)/ 1024), Text = LEFT([SQL].[Text], 100), UseCountsFrom sys.dm_exec_cached_plans POuter Apply sys.dm_exec_sql_text (P.plan_handle) As SQLInner Join sys.all_Objects As Obj On Obj.Object_ID = Sql.ObjectIDOrder By Size_In_Bytes Desc, UseCounts DescObjType ObjectName SizeInMegs UseCountsProc ParentNOAFieldList 6 272Proc ReportCD9600 6 151Proc AttendanceFormStatus 5 184Proc ApplicationJobAccrueVacation 4 286Proc ProviderMinWageCalculate 4 3Proc FamilyStatusChangeReminder 3 1698Proc ReportReferralWaitForm 2 15Proc ReportCD9608LetterHead 2 7Proc FamilyInfo 2 366Proc EmployeeBalanceSet 2 279 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 14:54:22
|
| "Is it true that the sqlsrvr.exe shouldn't be using more than 25% of the Total Server's memory?"No"AWE is not turned on on either instance"I'm not familiar with 64-bit. Does this mean that "unlimited" memory (e.g. >8GB) CAN or CAN NOT be allocated to individual SQL Server Instances?"I have allocated 3 gigs of RAM to the production instance and 2 gigs of RAM to the training server1) The server currently has 8 gigs of physical ram2) I also went with the OS recommendation and upped the Max Ram to 12gigs"I have no idea whether you mean you have 8GB or physical memory, or 12GB.Also I don't know how you have allocated that between Production and Training.Personally I wouldn't put separate instances for Production and "Training" on the same server, they will either have very different performance characteristics, or Training will be used infrequently such that it was better not to devote dedicate production-server-RAM to it.Kristen |
 |
|
|
juancabrera
Starting Member
30 Posts |
Posted - 2007-10-11 : 15:45:02
|
| I have 12 GB of virtual memory, and 8 GB of physical memory on the machineI allocated 3 GB to production and 2 GB to training under the server's maximum server memory in SQL Manager.We don't have the money to purchase another server for a training environment, so I created another instance.What should the maximum memory be for each instance? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-11 : 23:16:59
|
| I'll give 4gb to prod instance and 3gb to training instance, that leaves 1gb for os. Virtual memory doesn't count. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 02:35:58
|
| If the training instance used frequently?If not I would give more memory to Production (and reduce it back in favour of Training when there are large training session to be done AND if the Training users notice poor performanceBut I'm making a whole raft of assumptions, which may be borne out by fact ...Kristen |
|
|
|
juancabrera
Starting Member
30 Posts |
Posted - 2007-10-12 : 11:20:02
|
| I know virtual memory doesn't count for sql server but it counts for the operating system. Yes, it is slower memory, but I am sure it will help the OS.So maybe I "can" do 5GB for the production and 2GB for the training, leaving me with 1GB + 4GB(virtual RAM) for the OS. The training instance use to have 1GB allocated to it before, which is probably why it was soooo slow, and the production instance use to have 7GB of RAM allocated to it. Before I reallocated the memory the commit charge was around 8GBs and the SQLSvr.exe was using almost 8Gigs, which made the entire machine slow, and in turn made both the production and training environments slow.Right now I have production has 3Gigs allocated to it and training has 2Gigs allocated to it, and so far we are running pretty smooth. I am just going by trial and error, but there has to be a rule of thumb? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 11:24:39
|
| Don't you need to prevent anything getting swapped out to pagefile at all costs? But hardware is not something I know much about ...Kristen |
|
|
|
juancabrera
Starting Member
30 Posts |
Posted - 2007-10-12 : 11:33:01
|
| I don't know about that. Can anyone give me an insight on that? |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-10-12 : 11:44:55
|
| You want to allocate memory on the database server so that there is no paging of memory allocated to SQL Server at all. Ideally there's shouldn't be any paging on the server at all.=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
mse-ron
Starting Member
34 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-12 : 13:54:03
|
quote:
Originally posted by juancabrera
Right now I have production has 3Gigs allocated to it and training has 2Gigs allocated to it, and so far we are running pretty smooth. I am just going by trial and error, but there has to be a rule of thumb?
No such rule of thumb exists. It is done by trial and error just like you how you did it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-10-13 : 16:43:20
|
| Take the amount of *physical* memory on your serverLeave 0.5-1Gb of memory to Windows and may be some small processes (having a second instance or big memory consuming processes on the same server is not recommended)Allocate all other memory to SQL server (SQL server uses bits FILE_FLAG_WRITE_THROUGH and FILE_FLAG_NO_BUFFERING and does not use a Windows file cache)On 32bit systems, use /#gb and AWE if you need to use >2Gb |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-13 : 17:09:36
|
quote:
Originally posted by evilDBA
Take the amount of *physical* memory on your serverLeave 0.5-1Gb of memory to Windows and may be some small processes (having a second instance or big memory consuming processes on the same server is not recommended)Allocate all other memory to SQL server (SQL server uses bits FILE_FLAG_WRITE_THROUGH and FILE_FLAG_NO_BUFFERING and does not use a Windows file cache)On 32bit systems, use /#gb and AWE if you need to use >2Gb
That would be the rule of thumb for one instance, but no such rule exists for multiple instances. You just have to figure out how much each needs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Allocating Memory