Allocating Memory To SQL Server
Nov 5, 1998Hey there.
I have a raid 5 server with 128 mb of ram.
How can i allocate 100 mb to sql server and 28 to NT4.00.
THANK YOU IN ADVANCE.
ADVERTISEMENT
Allocating Memory
Oct 11, 2007Hello 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),
UseCounts
From sys.dm_exec_cached_plans P
Outer Apply sys.dm_exec_sql_text (P.plan_handle) As SQL
Inner Join sys.all_Objects As Obj On
Obj.Object_ID = Sql.ObjectID
Order By Size_In_Bytes Desc, UseCounts Desc
ObjTypeObjectNameSizeInMegsUseCounts
ProcParentNOAFieldList6272
ProcReportCD96006151
ProcAttendanceFormStatus5184
ProcApplicationJobAccrueVacation4286
ProcProviderMinWageCalculate43
ProcFamilyStatusChangeReminder31698
ProcReportReferralWaitForm215
ProcReportCD9608LetterHead27
ProcFamilyInfo2366
ProcEmployeeBalanceSet2279
Allocating Rows In A Table To Multiple Users
Jul 19, 2006Dear all
In our organization there is a call center where operators entry call details and custormer qruery in a table using front end application. For these queries there are multiple no of executive who will reply the query using a front end application. using the application the exectives retrive specific no of records from call center table(if there are unreplied call exists) and reply them. When a excutive retrives specfic no of rows, i want to update a flag in that rows, so that others do not get them.
I use the following procedure which is creating dead lock when multiple users execute it simultaneously
create procedure get_from_maintab @cnt int
as
declare @slno int,@curcnt int
select @curcnt=1
set rowcount 1
while @curcnt<=@cnt
begin
begin tran
select @slno=serial_no from maintab where getflag=''
if @@rowcount=0
begin
rollback
return
end
update maintab set getflag='Y' where serial_no=@slno and getflag=''
if @@rowcount = 0
begin
rollback
return
end
insert into detab select fld1,fld2,fld3 from maintab where serial_no=@slno
if @@rowcount = 0
begin
rollback
return
end
commit tran
select @curcnt=@curcnt+1
end
set rowcount 0
return
Temp Buffer Error Allocating ######## Bytes
Jan 11, 2008Hi Guys,
i've found many threds with SSIS buffer errors but non of them seems to work, or have a good solution. i'l explain the story short.
i've got a DB server running on Windows 2003 R2 Enterprise with 10GB RAM 20GB Virtual memory with same spec another machine for the web server.
both machines have "Lock Memory in memory" group policy enabled for
Network Service
System
DomainSQLServiceAccount (in DB server)
and in the DB memory "awe allocation" is also enabled.
both servers have /PAE /3GB switches enabled in boot.ini file
problem: i run all my SSIS packages in the web server through IIS. so the processes are devided between DB and web server. i.e SSIS service is also running in web server.
when i run packages under load (transforming - 200,000 records) i get buffer allocation errors.
A buffer failed while allocating 10485760 bytes.
Error Code = -1073450990
all packages i run i have set the default buffer temp to my web servers e: emp (got 260GB space left). and defaultbuffer size is 10mb with 10,000 defaultbuffermaxrows.
funny this is when it hit 8.33GB (approx 875 files) i get the above error message. it always seems to give me errors after 8.33GB.
Note: all packages run in IIS (w3wp.exe). i'm configuring my new production boxes. the old production with similar envirnment (less speed) work with the same data and packages fine under load.
new production machine got more memory than the old machine but i get memory error (buffer). it doesn't even use up all available physical memory, only about 3gb (max), then start buffering to disk.
any help would be greately appreciated.
i also got
The system reports 31 percent memory load. There are 10734981120 bytes of physical memory with 7326126080 bytes free. There are 3221094400 bytes of virtual memory with 283840512 bytes free. The paging file has 12661686272 bytes with 9633767424 bytes free.Error Code = -1073450991
this was before i increased virtual memory to 20gb(from 4gb).
Any ideas i can try out?
Performance Issues Total Server Memory Vs Target Server Memory
Aug 2, 2006Hi
I did a load testing and found the following observations:
1. The Memory:Pages/sec was crossing the limit beyond 20.
2. The Target Server Memory was always greater than Total Server Memory
Seeing the above data it seems to be memory pressure. But I found that AvailableMemory was always above 200 MB. Also Buffer Cache HitRatio was close to 99.99. What could be the reason for the above behavior?
Sql Server 2000 Using Less Memory After More Memory Added
Aug 22, 2007sql server 2000 is running on windows server 2003 ... 4gb of memory on server .... 2003 was allocated 2.3gb nd sql server was allocated (and using all of it) 1.6gb for total of approx 4gb based on idera monitor software ... all memory allocated betweeen the OS and sql server .... then 4 more gb of memory added for total now of 8g ... now idera monitor shows 1.7gb for OS and 1.0 gb for sql server ..... 'system' info shows 8gb memory with PAE ... so I assume that the full 8gb can now be addressed .... why are less resources being used now with more total memory .... especially sql server ..... i thought about specifying a minimum memmry for sql server but i amnot convinced that would even work since it seems that this 1gb limit is artificial .... it it used 1.6 gb before why would it not use at least that much now ??
thank you
SQL In-Memory :: Table Memory Optimization Advisor Validation Passed But Cannot Migrate
Jul 13, 2015I am looking to test this feature - and the "Transaction Performance Collector" has recommended me a table to port to In-Memory OLTP.Â
I have now tried the "Table Memory Optimization Advisor" tool.
After a couple of tweaks to the table design - the tool is now passing validation but the tool is not allowing to progress to the next step:
Could it be down to not having enough memory? But would this not show in the advisor?
Attempted To Read Or Write Protected Memory. This Is Often An Indication That Other Memory Is Corrupt. (Microsoft Visual Studio)
Sep 28, 2007Hello. I have received the follwoing error upon an attempt to Browse the Cube. All other tabs are functional, including the Calculations tab. We are running Windows Server 2003 SP2 and SQL Server 2005 SP2. Any suggestions would be greatly appreciated!
**EDIT** - Have confirmed SP1 for VS2005 is installed both locally and on server, also.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.Office.Interop.Owc11.PivotView.get_FieldSets()
at Microsoft.AnalysisServices.Controls.PivotTableFontAdjustor.TransformFonts(Font font)
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdatePivotTable(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdateAll(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.InitialUpdate()
at Microsoft.AnalysisServices.Browse.CubeBrowser.SupportFunctionWhichCanFail(FunctionWhichCanFail function)
AWE/Lock Page In Memory Drawbacks? Min/Max Settings, Memory Issues
Oct 11, 2007I've been researching AWE to determine if we should enable this for our environment.
Currently we have a quad core box with 4 gb of RAM (VMware). OS: Windows 2003 std, SQL Server 2005 std. 3GB is not set but will be as soon as we can perform maintenance on the server.
I have read mixed feedback on AWE, either it works great or grinds you to a hault. I would assume that the grinding to a hault is due to not setting the min/max values correctly or not enabling the lock page in memory setting.
We only have one instance of SQL on the server and this box won't be used for anything else aside from hosting SQL services. We do plan on running SSRS off of this server as well.
1. Will running SSRS and enabling AWE cause me problems? Will I have to reduce the max setting by the SSRS memory usage or will it share and play nice?
2. How do I go about setting the Max value? Should it be less than the physical RAM in the box? Right now its set to the default of 214748364, even if I don't enable AWE should this default value be changed?
3. It seems that even at idle the SQL server holds a lot of memory and the page file grows. If I restart the process in the morning, memory usage in taskmon is at 600mb or so. By the end of the day, its up around 2gb. How can I track down whats causing this, should this even concern me?
4. The lock Page in memory setting worries me. Everything I've read on this seems to give a warning about serious OS and other program support degradation. In some cases to the point where they have to restore the settings on the server before they can bring it back up. What are your thoughts on this.
SQL In-Memory :: How To Reduce Memory Usage Without Killing Any Process
Aug 28, 2015I have a Windows sever 2012 with sql server 2012 enterprise. Ram size is 22GB. Sometimes SQL sever takes 95% memory.My question, How to reduce memory size without killing any process because it's production server.So there are many background process is running. And,Is there any guides to learn why Memory is raise d so high and how to reduce it.
View 10 Replies View RelatedWould Max Memory Including SSIS And SSAS Memory
Mar 27, 2008Hello, I understand that we should use SSMS -> Server Properties -> Memory to put a cap on the SQL server memory usage, therefore it gives some space memory for OS, this is based on the fact if the max memory is not specified, SQL will use whatever available memory and eventually crash the system.
My question is that when a server has SSIS and SSAS services installed along with the SQL service. Would the max memory setting covers the SSIS and SSAS memory usage, or the SSIS and SSAS has to shared the memory with OS?
Thanks,
fshguo.
Memory Issues, SSIS Package Out Of Memory Help
Dec 6, 2006I am running Visual Studio 2005. I have an SSIS Package which is consuming a huge amount of memory. During the execution of the package the memory keeps increasing. Until finally i get an Out of Memory exception. I have run this package using dtexec, and in the BIDS. No difference. I do have some script components and have added some code to get the assemblies in the current appdomain. I do see that one particular assembly is increasing on every loop. VBAssembly every time it hits the script component is increasing by 6, and along with it the memory is climbing. What is this VBAssembly being used for is there an update to SQL Server Integration Services that I need?
Thanks! Aaron B.
SQL In-Memory :: How To Find Memory Usage By Index
Oct 4, 2015i want to create a lot of index for my database for performance.but i need find memory usage by indexes.
How to find memory usage by index in sql server?
SQL In-Memory :: Remove Memory Optimized Filegroup
Jun 15, 2015I've a database with a memory optimized filegroup on it. How can I remove it?I have removed the memory optimized table I had on it, but when I try to remove the filegroup I receive an error.
View 12 Replies View RelatedMemory Configurations For SQL Server 2005 EE X64 With SQL Server 2000 EE X32 On Windows Server EE X64 Server
Apr 20, 2007My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.
I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?
Memory Management (fixed Memory, AWE)
Jul 17, 2006Hi,
I am going to install SQL Server 2000 (then SQL 2K5) on a Win Server 2K3 with 8 GB of ram, but it will be 16 GB in the near future.
I would like to reserve a fixed memory (for momemt less than 3-4 GB) for SQL Server and the rest for application (virtualization).
Without AWE enabled, max memory for SQL Server 2K5 is 4GB as for SQL Server 2000?
How can I manage and optimize memory keeping in mind AWE. (any doc, website available?)
Thank
ODBC Server Driver Shared Memory / Server Does Not Exist Or Access Denied
Nov 8, 2015I am receiving the following error when starting a program called ShelbySystems that is supposed to connect to a local database. I don't think this is a security issue but I don't know much about SQL server either so...
  DIAG [08001] [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied. (17)
  DIAG [01000] [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()). (2)
System Info:
Windows 10Â Home -Â upgrade from 8 64 bit
SQL server 2012 Express
SQL Backwards compatibility 2005 64 bit
ShelbySystems software v5.4
I am including the trace log in case it is useful.
DBInstall 130c-728ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00000000
SQLHANDLE * 0x02EC58F4
[code]....
SQL 2012 :: Configuring Memory Per Query Option And Index Create Memory Option
Feb 10, 2015So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
How Do You Troubleshoot Memory Leak Issues? What Tools Can Use To Diagnose Memory Leak Problems? In MS SQL ?
Mar 20, 2008Hello frnds Can Anybody explai what does it mean by question itself and how to resole it ?
View 2 Replies View RelatedSQL Server Memory
Nov 14, 2007
Dear All,
I have hosted ASP.Net site with Sql server 2005
I have also configured State server for my site and started ASPNET State Server
I have gone one problem hat is SQL serve memory varies form 800MB to 1GB
I have been closing all the db objects in my code also installed SQL serve SP 2 still the problem exist
Can any one help
SQL Server Out Of Memory
Mar 24, 2004Hi all,
I am using StringBuilder to build a huge insert string which clubs around 12,000 insert strings into a single string. But whle executing this string the SQL server does no insert and the profiler shows that SQL server was out of memory. Any ideas?
Is there a limit to the maximum number of inserts I can do?
SQL Server Memory
May 2, 2002We have an issue where every month or so one of our servers that we upgraded from SQL 7 to SQL 2000 says that its memory is low. We get this error through our SQLProbe application. If we reboot it will go away. Any suggestions?
View 3 Replies View RelatedMin Server Memory
Aug 9, 2001Question:
Exchange & SQL 7.0 are running on the same server. You notice the performance in exchange is degraded. The min server memory,Maximum server memory & set working area are set as they were automatically in the installation. What you do to free memory for exchange.
Answer: Increase Min server memory.
why ?
Server Memory
Feb 22, 1999I just got a new server I need to install SQL Server 6.5 on and am
wondering if someone could give me a little advice as to how much memory
to configure for it. It will be dedicated to SQL Server. It has 1GB of
memory presently with 2- 450Mhz Xion processors. I don't know if you
need any other information to make this decision or not. All the SQL
Server memory charts I see only go up to 512MB of memory.
Does anyone have any suggestions for me? I do realize that this will
have to be fine-tuned depending on the specifics of our server, but any
range to start with will be beneficial.
Thanks!!
Toni E.
SQL Server Memory
Jul 25, 2000I have two NT 4.0 Boxes with SQL 6.5 on both. Server A transfers data to Server B once a week. Server B is connected to the net via a cable modem. Sometime during the day Server B has its CPU become 100% utilized. SQL Server memory goes from roughly 7 meg to 31 meg. When I do an sp_who at this time, it appears there are 4 spids running that shouldn’t in the master db. Somehow, Sever B also connects to Server A and connects some spids that do not appear to do anything. I am stumped on how to narrow down what is causing this behavior. It should also be noted, that currently no one is connecting to Server B. There are no scheduled tasks to be run on either machine.
Thanks
E
ekaufman@kpmg.com
Sql Server Memory
May 4, 2006Hi All,
Sql Server 7
In sql server Logs i see the Message.
Insufficient memory available..
Error: 17803, Severity: 17, State: 17
Please suggest How to Solve this.
Thanks in Advance
Adil
Use More Memory On 4GB Server
Jun 23, 2007Hello,
I have 2 processor server with 4GB memory. It is dedicated SQL server. Currently I have Windows 2003 standard edition and SQL 2005 standard edition. It used only about 1,7 GB for the SQL server and 300 MB for other application, task manager showed 2,0 GB of use.
Then I added /3GB switch to boot.ini. After that, system shows use of about 3GB, but SQL stayed on 1,7 GB max (even I set more in properties), the only advantages was, that I could use another 1,3 GB for other applications.
But I would like to give more memory to SQL server. I searched web, but I am still not sure about if it is
-possible on current software or if not,
-what is the cheapest way how to do that (upgrade to Windows Enterprise is of course much cheaper than to SQL 2005 Enterprise, will AWE memory help me?)
P.S. I can use only 32-bit software.
Sql Server Is Using All The Memory
Mar 4, 2008sql server seems to be using a huge amount of ram & virtual memory - how can I get to the bottom to what is causing this?
View 20 Replies View RelatedServer Not Using All Available Memory.
Jul 23, 2005I have a SQL Server 2000 (EE) machine dedicated to running only SQLServer that is not using all of the memoery on the machine. I have readand tried setting all of the memory setting available in the BOL withno luck. The database is about 10 Gb in Size. I have four 2.7 Xeon MP's16Gb of ram. The SQL server is only grabbing about 2 Gb of Memory.Since I am running Enterprise Edition I am not sure why It will notload more of the db into memory. I have run all of the profiles and theresults would indicate that it wants more memory but the box shouldhave plenty. Any suggestions would be greatly appreciated.Thanks in Advance,rhorner
View 5 Replies View RelatedMemory Mgt In SQL SERVER
Jul 23, 2005I have a performance and memory management issues with SQL SERVER. Ifeel SQL server does not releases the unused memory back to the OS. Ihave been monitoring that at the end of the day the SQL SERVERperformance gets really bad. And it this point it has acquired all theCache it could. I have abt 4 GB of RAM on my server and out of it SQLSERVER uses almost 2.7 GB whether there is load on the machine or not.If there any way or tool to monitor this. Also is there any way to Freememory from SQL server back to OS.
View 3 Replies View RelatedMax Server Memory And OS
Sep 9, 2015From everything I read setting the max server memory to a reasonable capped limit is a best practice to support effective performance and reliability of your SQL Server. However, I have seen a few discussions saying that althrough SQL will use lots of memory, it can easily release it to the OS if the OS requires it for OS operations. If that is genuinely the case, why do you need to cap the amount of memory SQL can use, if when the OS needs memory resources SQL will release them?
View 3 Replies View RelatedRegarding SQL Server Memory
Nov 19, 2007In my production environment, I am having one Quad processor with 260GB of HDD, with Database Size 21GB and 3GB of RAM.
After tuning all server-level objects (indexing and statistics, procedure tuning etc.) , server is still responding bit slow. It has been found that RAM (Server Memory) could be the blockage for the same.
Can anyone help me to identify the proper size of RAM for the same configuration.
I seems it should be around 8GB atleast.
Thanks in advance.
Regards,
Nikunj Naik
How Is Sql-server Using The Memory?
Oct 29, 2007I often read about that the best is that the whole database could fit in RAM.
Lets say I have a dabase that is 1000gb. I have 20gb RAM.
Its quite obvious that it wont fit in the memory, but is this always the case?
Say for example that in the database there is one table that is 990gb, and another table that is 10gb.
Nothing is written or read to the 990gb table, all the activity is in the 10gb table.
If I delete the 990gb table from the database, will sql-server need to perform less IO then, or doesnt it matter if I delete it?
Another scenario could be that we have two databases in the system. One 990gb and one 10gb, the system still has 20gb RAM. There is no activity in the 990gb database, but there is alot of activity in the 10gb database.
If I delete the 990gb database, will sql-server perform better, or doesnt it matter?