Are there any *negative* consequences to SQL Server 2000 Standard
paging to disk more often if one reduces the amount of available RAM
from 2.0 GB to 1.5 GB to give the OS and other apps enough RAM, other
than things possibly being "slower" and whatever wear and tear could
happen to the disk drives??
Or is it better to add in more RAM per se to bring the server up to 4
GB (Win2k3 Svr SP1)?? (it has a 4GB pagefile)
Is any information available about judging how much RAM the OS (Windows
2003 Server) should have available?? Does IIS require a lot of RAM??
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
I have windows 2003 with ms sql 2000. The machine has 3.6 gigs of ram and only runs ms sql, nothing else. Whenever the first query is made sql will allocate as much as 1.5 gigs of ram which is just killing the system. This system reboots nightly so this first query happens every morning. We have tried setting the min and max memory of sql, as well as the reserver memory setting to see if it will preallocate all that memory but we've had no luck.
Is there a way to make SQL allocate all that memory ahead of time? maybe make it cache some tables or something?
I know very little about MS SQL so please be very descriptive about possible solutions or troubleshooting steps.
How to find how much memory allocated to SQLCLR by sqlserver. Also is there any way to determine how much memory my code needs to run? Thanks in advance.
I'm log shipping to AWS. Currently, we are shipping from a server with 64 GB RAM to one at AWS with 30. It's not a massive server but does have periods of high usage when certain jobs/tasks run. It's been running fine for a year but the server size is large and is barely used. My understanding is that for simply log shipping, we don't need a lot of memory and I was hoping to drop the instance size down and use an instance with 16 GB Ram instead. It would save thousands per year.
I would like to ask regarding the memory allocation fo SQL Server 2000. For example if my Data Server have 8GB physical memory installed how much memory can SQL Server 2000 utilize? Based on my research and understing SQL 2000 Server can only utilize 3GB memory? But using the AWE you can set the memory to a maximum server memory?
I’m setting up a server for development that will have one instance of SQL Server 2000 Standard Edition and one of SQL Server 2005 Standard Edition installed.
The server has a total of 3.5 GB of physical memory, so I was wondering about the best way to allocate the memory. Should I just let both instances have the default allocation and let them fight it out for memory as needed, or allocate a memory limit for each instance?
I am setting up a server under Windows Server 2003 Enterprise Edition (SP1) and SQL Server 2000 Enterprise Edition (SP4). The new server is a AMD opteron with 32 GB of memory. I noticed that PAE is enabled automatically by Windows 2003. Should I enable AWE for SQL Server 2000 and specify Max and Min amount of memory for SQL Server ?? Is there a limit on how much I could specify in MAX memory for SQL??
This is the first time I have a server with so much memory so I want to make sure that I do the right thing. In the past I only have servers with 8 GB of memory and we just enable AWE and specify Max memory to 6 GB or so.
I have gotten mixed comments on this topic. I have a 64 bit machine running 64 windows 2003 standard and 64 SQL 2005 standard with 8 GB of RAM. We want to upgrade it to 32 GB. What is the best approach to do this? Dynamic or Stattic giving min and max server memory a value ? and if static what value should I use for 32 GB knowing that this box is only being used for SQL.
Hi,From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3instances. It is a nice machine with 4 Gb of memory but the memory allocationis very weird:Instance A: 1400MbInstance B: 1000MbInstance C: 80Mb (!)Instance C is performing badly under a bit of pressure which seems not strangeconsidering these allocations.With that in mind, is there a way to check and re-allocate memory? I'd like tosee if the instances really need these amounts of memory and if not, to movesome over to other instances.Thanks!Dries Bessels
I 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.
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.)
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.
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.
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.
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.
Good day to all, I'm new here, so I don't know if this is the right forum to post my problem. I have a web application written using C# .net 2005 (W/ajax). The application has a module that uploads data from excel file to the sql server 2005 database. w/c is by the way, i'm using SQL 2005 Express Edition, the app can upload up to more than 10,000 records from an excel file. Everything is ok until it was deployed in a test environment, while having a run through with the system, the application encounter an error after which, we cannot log in to the system anymore. I restarted the server (web and sql server in 1 machine running winxp) then I can log-in again in the system. When I'm tracing where the problem came from, I noticed that the memory usage of sqlservr.exe increases everytime the app connects to the server. I already fix some code to close some objects that might have caused the high memory usage, then I run sp_who in the management studio and there are still connections used by the app AWAITING COMMAND. Then I manually kill (using kill spid) connection that are left opened by the application. But the mem usage of sqlservr did no decrease. Is there a way to release the memory usage of sqlservr.exe? In ASP.Net ? I have a hint that this has been causing the error. Thanks a lot.
Hi, it seems that every day SQL Server 2000 has some kind of memory leak, the memory usage creeps above 150000 approximately 3 times per day. Is this normal? It starts at about 13000.
Is there any way that I can monitor what is causing the memory usage to be so high and maybe rectify it?
I was on my friend's server earlier and I checked his system stats. Between sqlservr.exe and msftesql.exe he was using 2.9gb of ram. This is not a high traffic server so I felt that was just a wee bit high. What are some usage experiences you have seen? I have not worked with 2005 so I don't know what the usage should be like for a low to medium usage server. The guy that admins the db is kinda a moron anyway. He said he has said to the server admin it has been optimized, but I dont believe it one bit at 2.9gb of usage. Any comments would help.
I need to know which of the following two methods do need less RAM.
There are 2 big tables, each about 9 M rows, and 6 small dimension tables with each about 10 to 100 Rows. The dimension tables are joined by their id's with one of the big table.
The Structure of a dimension Table looks like
CarID (tinyint), Description (varchar(20)) 1 BMW 2 Porsche
I want to join the 2 Big Tables in a materialized view. Later i will run queries like select * into #temp from dbo.vw_materialized_view where Car = 'BMW'
So, back to my question, will such a query take less memory (ram) when i joined all 8 tables before I created the mat. view or will it take less when I only join the 2 big tables in a mat.view and later join the mat.view with the 6 dimension tables?
Can some one tell me where can I get the CPU and Memory Usage of my server on the Sql Tables or a script or store proc that i could run to get the above. I need that and to store its results in history to show how are our servers perfoming
I have setup 2 x64 SQL boxes now that are having problems with memory. Both boxes were pretty much setup with the defaults on windows 2003 R2 64 bit server, only changes were file locations etc.
On both servers the memory usage continues to climb over the course of a few days until all physical and virtual memory is used and the server comes to it's knees. A reboot will stabilize it for a few days while the memory usage ramps up again.
The sqlservr.exe process does not report much memory but the commit charge in the performance tab of task manager shows the maxed out value. I thought the sqlservr.exe process only didn't report the correct memory figure when AWE is enabled which it isn't.
I thought as a default, SQL server would dynamically allocate memory as needed and had internal mechanisms that would not use more than the physical ram available. The one server has 16GB and I even set the max SQL memory usage to 12GB but still my total memory usage would get out of hand. SQL Server is the only application on these boxes.
Am I making some incorrect assumptions and need to change the way SQL is setup? Any suggestions?
I'm trying to get the machine free ram value using either T-SQL orSQL-DMO.I found .Registry.PhysicalMemory which takes care of the total ram butI still need eithrt the free or the used.Any ideas?Thanks!
