We recently did an in-place upgrade of our cluster from SQL 2000 Enterprise to 2005 Enterprise. We are seeing memory utilization on the server that is not expected and wanted to get an idea from others if this is normal. Here is our setup:
32-bit Windows Server 2003 SP1
SQL 2005 SP2, 2-node cluster
16 GB memory on each node
/3GB /PAE switches in the boot.ini file
AWE Enabled, min memory=0, max memory=14000
The strange bit is that in the Task Manager the SQL service is showing as only using 205 MB of memory and the pagefile usage is at 13 GB. This is troublesome since it looks like SQL is using virtual memory instead of physical memory. We recently upgraded the memory from 4 GB to 16 GB, the sql cluster service account was added to the Lock Pages in Memory policy, the boot.ini switches were set and the AWE enabled/max_server_memory script was run before upgrading to 2005. Can anyone confirm either 1) this is normal or 2) how to correct the memory usage?
I have also questioned using the /3GB switch; would it be better to remove that and set the max memory for SQL to 12000?
Any help would be greatly appreciated. If I left off any information that would be helpful, please let me know.
Dear all,i am planing to implement a Windows 2003 Cluster with MS SQl 2000Enterprise Edition. I have 2 Nodes (4 * XEON MP Processors) with 8 GBRAM per Node.I have the need for 6 SQL instances and would like to implement aactive-active cluster. What do you think it the best way to configurethe memory for the Servers ?I would like to run 3 instanced per Node but can i allocate 7 GB perNode for SQL (and 1 for the Operating System) ?What is happening then i one server fails ?Should i plan to allocate only 3 GB per Server for SQL to make surethat one server can handle the load for all instances if one serverfails ?Should i use the /AWE switch only in the boot.ini to allow more than 4GB Memory ?or should i use the /3 GB switch as well ?Maybe somebody can give me a hint.Best regards,Walter
OVERVIEW I run a MS SQL DB and have 7GB of RAM allocated to it to use. My DB size is around 30GB. I have about 30-40 users at any given time accessing this DB, and on any given moment we may have 2-3 queries being processed at the exact same time, but not really a high rate of usage.
The SQL is running on a 2 - Dual Core Xeon 2.8Ghz processor server in RAID 1+0. The average CPU usage is around 8-15% at most times, spikes to 60-70% sometimes then drops back down.
QUESTION Why or what would cause SQL to show in my Task Manager as using 7GB of RAM constantly? When I restart SQL, system RAM drops to 1.7GB and holds. As traffic increases, as queries begin to be processed in SQL the RAM rises till it reaches the 7GB limit I set. Before it reaches this limit, the ASPX pages run smooth as silk. Once it reaches this 7GB limit they begin to crawl. A process that would take 2-6 seconds, now takes 30-60 seconds.
I know a couple years back I ran into this and it was because processes were not being closed and SQL was holding them in RAM, but I have been assured this is not the case now. I was told that because my DB is 30GB total (MANY tables, most are hardly ever accessed, and the bulk of the data is hardly accessed) that it is common for SQL to use and hold on to 7GB of data - is this true?
What would cause SQL to hold 7GB and slow way down? IS there a KB article that could help me understand this?
I have a three-tier app written in C#, which takes information from a third party source (typically an array of double precision floats) and commits it to a SQL 2005 Db.
The Server then notifies the client that the information is available and the client queries the Db (through the server) in order to display it in "real-time". It all seems to work fine except that the memory usage by SQL 2005 just keeps rising. I have run a memory profiler on the server and client apps and they do not have a leak.
The test I am running has all three apps on the same machine and is reading in about 250k of data a second. It typically runs for just under two days on a machine with 2Gb RAM before falling over with System.OutOfMemoryException.
I have tried setting max memory usage but it seems to make no difference... anyone seen anything similar or know what my problem might be?
We have an application that we currently run on SQL Server 2000 that works by creating a DTS package that it then executes.
Due to performance reasons, we have been considering switching to 2005, for a few reasons. Can anyone confirm clarify the following?
1) SQL Server 2000 caps RAM usage at 2GB, whereas SQL Server 2005 is only limited by the OS - RAM usage is a big current issue for us, so if upgrading to 2005 would solve this it would help a lot. Can anyone confirm my understanding of this? 2) Would using the legacy DTS in SQL Server 2005 take advantage of this RAM difference, or is it running on the old 2000 engine and only able to use the 2GB?
we are working on a release 2.0 mobile solution right now. In our version 1.0 we did not have to worry about memory issues as our application was the only application running on our target devices (e.g. T-Mobile MDA Compact II Pocket PCs, WM2005). Now we need to share the available memory with others. As our application relies on its SQL Server 2005 Mobile Edition database we are wondering about memory usage of that server.
We know that a Pocket PC divides its memory into Storage and Program. If our application uses a 5 MB database and 1.5 MB for DLLs and it's exe-file. These files reside in the storage space when not loaded. When the application starts up it is loaded in the program memory. What happens to the 5 MB database file? Is is loaded into Program memory as well? Are only portions of that file loaded? Or is nothing loaded at all?
Does anyone have a deeper insight into that server an can answer my questions.
I'm hoping that some might have an answer for me after much net searching.
I have a server (200GB disk space, Dual 3.8 GHZ processors, 4GB memory) that hosts 6, very small, SharePoint sites (WSS 2.0) and SQL 2005 express handling 1 config DB and 7 content DB's. The biggest DB at the moment is only 300 MB and the sites are not actively being used yet, they are only open to a select number of users (+ - 25 in total) who are using them as reference "areas" at the moment. Each site is running in it's own application pool as well.
I find that the sqlserv.exe process increases in memory usage and does not seem to decrease. It gets to the point of 960 MB usage and then databases cannot be used (SQL 2005 Express max memory is 1GB). SQL seems to "close" them down and site errors being received are "site is not in configuration database". When the SQL service is restarted the memory usage idles around 70 - 80 MB, the sites are 100% again but after an hour or 2 the memory usage is sitting at 560 MB again and doesn't seem to decrease.
Is there a way I can bring this memory usage down?
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 .
So I'm in the middle of building SQL Server 2005 on my new cluster hardware. I've got all the goodies this time around -4x PowerEdge 68004x dual core x64 procs in each box32GB ram in each boxWindows Server 2k3 R2 Enterprise x64 SP2SQL 2005 Enterprise x64 SP2Active Active Active Passive cluster (4 boxes, 3 instances)dedicated data, log, and tempDB 4Gb 15k fiber channel SAN Volumes for each instanceNow, this is the first x64 box I've configured, so I'm looking for some optimization tips for a couple things.First one's memory. On our 32bit systems, I always added the /PAE switch to the boot.ini file and enable AWE in SQL Server. From what I'm reading, that's no longer needed with the enhanced memory addressing. Is that the case? Is there an MS best practices KB doc in regards to configuration? I can't find one.Also, should I set anything special pagefile-wise? I know the old mantra is 1.5x system memory, but that would create a 48GB page file. Our current cluster has 6GB of ram dedicated to each instance (2k) and Perfmon tells me it's barely touching the pagefile. I'm thinking of just leaving it default, but I want to know if there is a best practice that I'm missing. Again, I can't find an MS doc that addresses this particular scenario.
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.
Could someone give me an ideal of what it takes to upgrade the memory in my cluster. Is it as easy has upgrading the passive node, switching the nodes, then upgrading the other server. And do I need to re-configure the windows cluster. And will both server need to be down at the same time at any point.
Or is their more to the process? Thanks for any assistance.
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!
We have some CLR sprocs and tvf's we run in a batch job that recently have been getting the out of memory issue. I want to increase the amount of memory allocated to the CLR using the -g startup switch but i want to make an intelligent decision on how much to allocate. What are some of the best ways that you have found to estimate how much to give the CLR?
How much available memory is best practice for SQL servers? We have some alerts set up by our system admin, they go off if the available memory goes below 500mb. Which is fine for other servers but I feel for SQL server it isn't quite correct. Currently we stand below at about 475mb and the PF usage is around 7gb.
It is SQL 2005 standard SP1 x64 dual intel xeon 5160s 8gb physical memory buffer hit cache is > 99%
I am just not confident enough to make a solid decision here that this is acceptable. In the beginning of this month I ran some counters for 24hrs to get a feel for the baseline. The average for available memory was about 850mb at that time. So I am wondering if it is going to keep declining and turn into a problem. The server/instance has not been restarted since maybe last November.
Does anybody know why BCP on v6.5 grabs so much memory for SQL Server? I have a few table imports where the BCP process will consume over 460MB of RAM during the imports.
The BCP cmd file is executed via an xp_cmdshell call. The server has 2+GB of RAM, but the BCP process effectively flushes large amounts of data from the buffer. It takes quite along time for the cache to recover from this, and after this, the rest of the nightly processes run much slower, as they end up having to hit the drives to retrieve information that should already be in cache.
If anyone can shed some light on this it would be much appreciated.
One of the production box running only sql server application, is showing 80% memory usage on the task manager-memory usage history right now.
We are running sql server 2000 standard version-sp3 with 2GB memory on this box. Server is not on the scheduled reboot at this point.
We have seen this behavior for this box last month that after task manager showing 90% memory usage contantly for several days, when server was manually rebooted, memory usage dropped to 35%. Now it's back to 80%.
Our DBA thinks that server should be rebooted on a regular schedule regardless of memory problem. Our network admin doesn't seem to agree with this. He is not ready to reboot the machine even with this high memory usage.
There is no noticable difference performancewise yet.
My questions are: Is it bad that memory usage reaches from 35% constant to 80-90% or is it common? Should sql server be rebooted immediately to take care of it? Should sql server 2000 rebooted on regular basis regardless of any problems? Shouldn't sql server be releasing memory back to the OS even without rebooting? How do I find out whether server actually is going through memory problems and what is causing it?
I got a small MSSQL server.. total database file size less than 7GB. with 2G rams installed 2 cpus. but for some reason when i check the task manager process mem usage is over 1.7G. the sql server memory setting in on Dynamically not fixed. and maximum 2G i believe is default. anyway. my question is over 1.7 memory usage is too high? because i dont have alot of transaction going. and cpu usage is very low. wondering if this's normal or not. and if is not normal . what cause the memory usage so high...... and how can i adjust back to normal. ? can anyone help me out? or any suggestion? thanks
Currently I have an application that uses SQL 2000. The SQL server service tends to take up as much of the physical memory as possible. The problem is I also have other services relating to this application running that are very important.
What tends to happen after a period of time is SQL takes up all of the physical memory, so that the other services are using the paging file (virtual memory). This causes extremely slow response time over the network as these other services are having to parse the paging file.
Upgrading the memory is currently not an option :(
I know there is an option to set memory usage for SQL but I am unsure how this would respond in a production environment. What would happen if SQL would require more memory than what was allocated to it?
Can SQL release the memory and still act as normal?
I am using SQL Server 7.0 on a Windows NT machine. We have been having problems with SQL Server not releasing the memory after it has utilized it.
Currently it is configured to allow a max of 511MB memory (1024 is total on the machine). I had some advice that my best solution would be to reduce the Max Memory to a lower value (say 400 MB) to help reduce the problem.
Is this not counter-intuitive? Or is this the correct solution?
With SQL Server 2005, I have the problem that sqlserv.exe keeps using more and more memory. Now I did some research and found the following anwers in different places:
*It seems to be the way that SQL Server works. It just uses all the memory which it can get. I have two remarks about this: 1) I that is true, then why is a fresh installation of SQL Server not consuming all the memory it can get? 2) After a few weeks, it consumes so much memory that the whole system (even SQL Server itself) start running very slow. I don't think that's the intended behavior?
So I thought about restricting the maximum memory that SQL Server uses, but people keep saying that's not the way it should be done: SQL Server should be allowed to allocate memory dynamically.
Can anyone help me with my questions?
One other thing that may be of relevance: This instance is a disitributor for replication and the distribution.MDF file also seems to be increasing over time. It became much larger than the application database.
Could some please point me in the right direction?
We have a database and it's about 28GB in size, recently the SQL server process that runs uses approximately 1.6GB of Memory.
I have tried running SQL profiler to find out which Stored Procedure is causing this but came up unsuccessful. When restarting SQL the process it run's at about 50MB for about 20sec and then starts climbing up to 1.6GB of memory usage.
I am using a tool to monitor SQL Server and Windows. It is warning me that:
Process 1004:services has a virtual address space of 1,846.20 MB. This is close to the Windows two gigabyte address space limit.
When locate the process 1004, it shows 15 threads that Elapsed time for all of them is 1d, 3hrs. The Thread state is Waiting and the Thread Wait Reason is "Waiting for an Execution Delay to be resolved".
I think that 1d, 3hrs is from the time I rebooted my server.
I front a series problem with my SQL Server. I Use SQL Server 2000 standard on Windows 2000 Server machine. The problem: after certain actions such as DTS (that takes table contents and insert to a a file) the Memory usage of SQLservr.exe is grown significantly and after it finshes with success it doesn't free the used memory for the action - in this case the DTS. maybe this explains why I have Memeory Usage of 1700MB of Sqlservr.exe on windows task manager.
Is it a bug? why SQL doesn't free the memory after finishing the action. Any Solutions ? Please send me a short feedback.
