Extreme Paging Rate Reduced By Setting Maximum Server Memory To 6 Gig?
Dec 3, 2007
We have several 2005 servers with "Maximum server memory" set to 214 gig, which I believe is the default at installation time. I am told that this means "use all the memory there is including paging." Well, this is nuts but the servers seem to work fine with this setting no matter how much physical memory they have.
One of our 2005 servers recently started paging like crazy, so I reduced "Maximum server memory" to 6000 and the paging disappeared (server has 8 gig of physical memory) and the server appears happy.
I can not explain why only this one server has this paging issue and the others do not. Should I be setting "Maximum server memory" on all my servers? Are there other considerations which might cause the server to eat-up all the memory? As far as I know no other applications run on this box.
Thanks,
Michael
View 6 Replies
ADVERTISEMENT
Mar 30, 2015
Our monitoring tool shows that our production system periodically experiencing large rate - up to 800 memory pages/sec. How to find out which particular queries, S.P., processes that initiate this?
View 3 Replies
View Related
May 21, 2015
I am curious about maximum memory setting .
Should we set maximum memory setting for each SQL server? For example a server has 6 GB memory then should we set maximum memory setting = 3.5 GB ?
View 9 Replies
View Related
Nov 4, 2007
I need to up the Maximum Memory setting.
Out of the box it is set to 2147483647 MB.
I assume this is ~2GB. I have been instructed
to set it to 1GB less than total server memory
which is 16GB. When I change the value
it just changes back.
How do I change it and how are these Megabyte
numbers calculated?
OS: Windows 2003 R2 Enterprise
SQL: SQL Server 2005 Standard
Thanks!
View 2 Replies
View Related
Nov 23, 2015
Is there any easy way to detect SQL Server memory is getting paged out? Any DMV query or tips to confirm it is paging out.
My SQL Server Version is: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
View 5 Replies
View Related
Sep 7, 2006
Hi,
Anyone knows what the size should the Paging File be for a 8 P 16 Core Opteron server with 128GB memory installed? The server will be running SQL 05 Ent and IIS, on top of Win2k3 R2 x64 Ent.
Please also give the reason for that size.
Regards,
dong
View 1 Replies
View Related
Apr 22, 2015
I am monitoring our production server, and noticed that periodically we have spikes of Memory Paging Rate (pages/sec).
How to find particular queries/stored procedures that causing this?
View 5 Replies
View Related
Aug 13, 2007
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
Version5.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
View 2 Replies
View Related
Apr 28, 2015
How to set maximum server memory by using sql scripts in sql server 2014?
View 5 Replies
View Related
Feb 22, 2015
I have an issue with my production server regarding memory usage (Memory Utilization is above 95%.). Memory is : 12 GB and the service that is consuming the majority of memory 88%/10.5GB is sqlserver.exe. So it would appear that MSSQL is not set to restrict the amount of memory it uses ? How much should I set for min and max memory ? the defauld is min memory : 0 and max : 2 TB
View 5 Replies
View Related
Dec 21, 2007
I am running SQL 2005 Standard under Windows 2003 Standard with 8 GB of RAM in one of our 64 bit servers; I noticed min server memory was set for some reason to 128 MB and max server memory to 6114 MB by the previous DBA. We plan to upgrade it to 32 GB. What is the best approach when changing those values ? Should I change those values to the defaults (0 - 2147483647) or create min and max boundaries? . This box is only utilzed by SQL.
Also I know that those changes are reconfigured dynamically so no need to reboot, but would this place any performance hit in the server and should I use Management studio or sp_configure ? (for some reason I don't trust Management studio for these type of changes :))
Thanks,
Carlos
View 1 Replies
View Related
May 21, 2007
This morning msmdsrv.exe looked like the following in Task Manager:
Memory Usage: 600,000 K
VM Size: 2,500,000 K
If I understand correctly, that means that a good deal of SSAS memory has been swapped to disk by the OS. Is there any way to prevent this? (I have read about using the Lock Pages In Memory privilege at http://msdn2.microsoft.com/en-us/library/ms179301.aspx which lets SQL prevent the OS from paging sqlservr.exe memory. I don't suppose there's an equivalent SSAS setting.)
In particular, the symptom I'm seeing is that after we finish processing, all that paged memory has to be loaded back into memory before the transaction can be committed. Committing the transaction is usually very quick. But when most of the SSAS process memory is pages, it takes quite a while, during which I can see the Memory Usage number in Task Manager growing. (I don't think it's a blocked transaction commit.)
View 2 Replies
View Related
Jun 30, 2006
Are there any *negative* consequences to SQL Server 2000 Standardpaging to disk more often if one reduces the amount of available RAMfrom 2.0 GB to 1.5 GB to give the OS and other apps enough RAM, otherthan things possibly being "slower" and whatever wear and tear couldhappen to the disk drives??Or is it better to add in more RAM per se to bring the server up to 4GB (Win2k3 Svr SP1)?? (it has a 4GB pagefile)Is any information available about judging how much RAM the OS (Windows2003 Server) should have available?? Does IIS require a lot of RAM??Thank you, Tom
View 1 Replies
View Related
Mar 28, 2007
Currently, we have the following setup...
SQL 2005 Std, 8gb ram, boot ini switches for /PAE and /3GB, AWE enabled and the min/max memory settings for a single instance respectively set to 1024 and 7168. Performance-wise the system is a bit slower than before (with SQL2000) and there seems to be alot of paging. The SQL service is also running under the "LocalSystem". In this case, do I need to specify the local "SYSTEM" to have rights to "Lock pages in memory"? I have a feeling there is a misconfiguration somewhere. Can someone point me in the right direction? Thanks.
View 4 Replies
View Related
Apr 29, 2015
After I have set the maximum sql server memory in sql server 2014 using sp_configure, do I need to reboot whole server or restart sql service?
View 4 Replies
View Related
Jan 29, 2007
Summary: We host reports off of one server that are accessed and refreshed 24/7/365. On a daily basis, I receive calls about reports taking several minutes to run IF they run at all. (normally these reports take seconds). There has yet to be an actual "error message" associated with this behavior. When I check the server at these times, I will find that w3wp.exe process is taking up about 4G and is causing paging. It seems that once it gets to that point, it will kill itself and all goes back to normal. However, it's a slow death and is impacting business.
We're using MSRS 2005. Hosting the reports is the job of this server--no other applications are hitting it. There is a development SQL db on the box, but not in use at this time. The datasources in most cases are stored procedures housed on another box. The web interface (for end users running the report) is hosted on a third server.
Any thoughts?
View 5 Replies
View Related
Aug 3, 2007
Hi,
I am testing SQL 2005 Standard (32 bit) on a Windows 2003 Server 64 bit with 8GB of RAM? Should I enable the AWP Setting or not and should I change the maximum server memory (currently saying 2GB)?
Thanks!
Tom
View 1 Replies
View Related
Nov 28, 2007
Hello, everyone:
I have SQL Server 2000 Standard Edition running on Windows 2000 Server. There are always memory full alter. I am going to configure that.
I hope a clearation about a concept. In my enviroment, does SQL Server take maximum memroy upto 2GB, no matter if QWE is enabled?
BOL said AWE is available only in the SQL Server 2000 Enterprise and Developer editions. Does it mean AWE cannot be configured on SQL Server 2000 Standard Edition?
Thanks
ZYT
View 4 Replies
View Related
Sep 15, 1998
I am trying to find a suitable size for TempDB in RAM .
Is it possible to find the instantaneous maximum usage of the TempDB in RAM?
In other words how can I find the maximum amount of memory used* by TempDB ?
bim@sifas.com.tr
mikiz@turk.net
View 3 Replies
View Related
Feb 26, 2004
If I change the Maximum Memory option (sp_configure or vai EM), do I need to restart the MSSQL service?
Clive
View 2 Replies
View Related
Jul 20, 2005
I have a situation with a 6 server setup whereby merge agentswill not run if other merge agents are alreay running. Basicallywe have a 5 laptop, one tower config with the tower being thepublisher. In theory we want to be able to hook a laptop upand have it sync within, say, 5 mins. Trouble is, if agents forthe other laptops are running (trying and failing to connect becausethose laptops aren't connected) then the agents for the laptop that*is* connected won't run. It seems to be a 'maximum number of runningagents' issue. Is this is user defined setting, and if so where can I set it?Many thanks, sorry if this has been answered many many times before.Jim--Find me at http://www.ursaMinorBeta.co.uk"Brace yourself, this might make your eyes water."
View 3 Replies
View Related
Feb 20, 2002
Hello,
I have been trying to find out what the maximum memory we can have on SQL7 standard edition server. Have not been able to find it anywhere...
Anuy ideas would be greately appreciated.
TIA
B.
View 1 Replies
View Related
Feb 20, 2006
Hello sql and .net gurus :-)I have a problem with my website www.eventguide.it. It's completly developed under .NET 2 and SQL Server 2005 Express. My problem is the folowing:The server is a Intel 3Ghz HT processor with 1GB Ram. No other page on the running system is a CPU consuming site. We optimized the SQL statements, the code, the caching and many other parts of the website (pooling on SQL access), but the SQL Server uses about 50% to100% of the CPU and about 400MB RAM all the time. The whole site seems to be very, very slow. In fact there are many of SQL operations on every page request, but we cache a lot of them in different ways (page output caching, application caching). So I don't understand we have so much performance problems. Any suggestions for optimised code in general? I read nearly all of the MS .NET performance papers - but real world experience is the missing part :-) It is better to cast the values of a SQL reader like thisDim String1 as String = Ctype(DataReader.item(0), String)Dim Integer1 as Intger = Ctype(DataReader.item(1), Integer)or like thisDim String1 as String = DataReader.item(0)
Dim Integer1 as Intger = DataReader.item(1)Thanks a lot for your help!FOX
View 6 Replies
View Related
Sep 12, 2006
I have a few questions about memory setting inheritedWindows 2003, SQL Server SP4, both Enterprise Edition 8 GB RAM1. How to know how much Memory SQL can address ( SQL script is betteras there is no access to Perfmonitor or EM ) . ie what is maximummemory SQL can use ?2. In this server awe is set to 0 . Do I have to turn it to 1 so thatSQL can use all memory .3. If awe is enabled, what is the minimum and maximum memory size needto be set ?4. Should I set max worker set also ?If someone can provide examples of /PAE, /AWE. /3GB ,/USERVA etc , I amgratefulThanksSrinivas
View 3 Replies
View Related
Jul 20, 2005
I am using SQL 8 Personal edition with sp2 applied. I set the maxserver memory to 32MB and leave the min server memory at 0. When myapplication starts hitting the database hard the memory usage reportedthrough task manager peaks between 41-42MB. I've stopped and restartedthe MSSQLserver service and checked that the running values are what Iset them to be. Does anybody have any ideas as to why the sqlservr.exewould be utilizing more memory than the configured value?This message was posted some years ago but nobody answered. Now I havethe same problem! Does anybody have an explanation?Thanks a lot
View 4 Replies
View Related
Nov 3, 2006
I have a client server that has win2000 on it with sql 2000 enterprise edition. The box has 4 gig of memory on it. I noticed today that the sql server was set to use all 4 gig (even though I know sql can't really access that memory because sql can't really utilize the 2-4 gig range). Is there overhead or a downside to leaving it at this, or should I set it to 2 gig
View 3 Replies
View Related
Oct 27, 1998
hi, here is the situation, my system has the following specks
hard drive = 45GB
memory = 1152 mb
Opsystem = win NT 4.0
application on the NT is ftp server and SQL server 6.5
I am having a tough time retrieving a simple query from a 11 million records.some of the feed back from the newsgroup is that I do not have enought memory. Is there a formula to use to figure out how much memory should allocate to the sql server? what if I allocated too much memory, does that affect the sql performance in a negative way?
Please help......
I can be reach at a.alhussein@mci.com
thanks alot
regards
Ali
View 4 Replies
View Related
May 4, 2007
We have a SQL 2005 installed in Windows 2003 64 bit system, which has 16 CPU and 32GB RAM, but the performance is poor. SQL server is AWE enable and the sql start account with "Lock Pages In Memory". I checked the task manager and it looks that the SQL server used only about 300 MB memory. Here is what I found:
http://blogs.msdn.com/psssql/archive/2006/11/28/sql-server-becomes-sluggish-or-appears-to-stall-on-64-bit-installations.aspx?CommentPosted=true#commentmessage
What I need to do?
Thanks
View 5 Replies
View Related
Jan 12, 2007
My sql query analyzer my windows xp machine is very slow. I run a query in my collegues computer, it takes about 2 mins to finish. This might means the sql server has no problems. On my computer, the same query took about 3 hours to finish.
Can someone give me some suggestion how to fix this problem.
THX
Jeff
View 10 Replies
View Related
Apr 20, 2006
I am wondering why deployed packages are so much smaller than built packages. What is removed and why isn't it removed when the packages are built?
I have a package that goes from 3Mb to 1.5Mb
View 5 Replies
View Related
Aug 9, 2007
I would like to know what happens when a very large reference data set for a lookup transform with full caching enabled is getting loaded during package execution and the computer memory runs out or is very low.
Does SSIS
a) give an out of memory error of some sort
b) resort to a no caching or partial caching mode
c) maintain the full caching mode but will switch to using the paging file(virtual memory).
I think it will resort to using the page file in which case the benefits of in memory lookups are lost and performance would suffer. If I cannot upgrade the memory or shrink the reference set somehow, i should switch that lookup task to use partial caching or no caching with an indexed lookup table. Would this make sense?
View 1 Replies
View Related
Aug 24, 2015
I have a large fact table spread across tens of partitions (appx. 1TB each). I found that the business does not need much of the columns in the table. So, as an optimization action, I decided to get rid of these un-needed columns.What is the efficient way to achieve this? Can I simply drop these columns from the table, or use a new table with the reduced structure?
View 2 Replies
View Related
Aug 25, 2015
I am only DBA in my company and client want to know the growth rate of his SQL server DataBase which is in production. How can I get the growth rate per day?
View 3 Replies
View Related
