SQL Server 2005 DTS/Memory Usage
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?
Thanks for any help.
View Complete Forum Thread with Replies
Related Forum Messages:
Memory Usage Of SQL Server 2005 Mobile Edition
Hi forum readers, 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. Best regards, Tobias
View Replies !
MS SQL 2005 && Memory Usage Question
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? Thanks for your help.
View Replies !
SQL 2005 (32-bit) Cluster Memory Usage
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. Chris
View Replies !
SQL 2005 Memory Usage Just Keeps Rising
Hi, 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? Thanks, Paul G
View Replies !
Memory Usage By SQL 2005 Enterprise Edition
Hi Guys, I have a question on my SQL server 2005 EE memory usage. It got 24 GB and when I see on Task manager it always shows less than 3gb usage. AWE enabled on this machine and here are the DBCC MEMORYSTATUS output. Pls let me know or educate me how to understand these stats. Thanks in advance. Memory Manager KB ------------------------------ -------------------- VM Reserved 1679452 VM Committed 254756 AWE Allocated 20840448 Reserved Memory 1024 Reserved Memory In Use 0 (5 row(s) affected) Memory node Id = 0 KB ------------------------------ -------------------- VM Reserved 1675292 VM Committed 250748 AWE Allocated 20840448 MultiPage Allocator 27232 SinglePage Allocator 761896 (5 row(s) affected) MEMORYCLERK_SQLGENERAL (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 20024 MultiPage Allocator 1544 (7 row(s) affected) MEMORYCLERK_SQLBUFFERPOOL (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 1632404 VM Committed 211092 AWE Allocated 20840448 SM Reserved 0 SM Commited 0 SinglePage Allocator 0 MultiPage Allocator 5352 (7 row(s) affected) MEMORYCLERK_SQLQUERYEXEC (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 9440 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLOPTIMIZER (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1096 MultiPage Allocator 88 (7 row(s) affected) MEMORYCLERK_SQLUTILITIES (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 1480 VM Committed 1480 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 264 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLSTORENG (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 6528 VM Committed 6528 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 4544 MultiPage Allocator 12288 (7 row(s) affected) MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 2816 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLCLR (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLSERVICEBROKER (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 88 MultiPage Allocator 192 (7 row(s) affected) MEMORYCLERK_SQLHTTP (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SNI (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 360 MultiPage Allocator 16 (7 row(s) affected) MEMORYCLERK_FULLTEXT (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLXP (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_BHF (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1920 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_SQLQERESERVATIONS (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 157400 MultiPage Allocator 0 (7 row(s) affected) MEMORYCLERK_HOST (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 88 MultiPage Allocator 80 (7 row(s) affected) MEMORYCLERK_SOSNODE (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 10592 MultiPage Allocator 6672 (7 row(s) affected) MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 24 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_OBJCP (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 85480 MultiPage Allocator 192 (7 row(s) affected) CACHESTORE_SQLCP (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 601672 MultiPage Allocator 616 (7 row(s) affected) CACHESTORE_PHDR (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1568 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XPROC (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 88 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_TEMPTABLES (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 128 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_NOTIF (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_VIEWDEFINITIONS (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBTYPE (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBELEMENT (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_XMLDBATTRIBUTE (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_STACKFRAMES (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 0 MultiPage Allocator 8 (7 row(s) affected) CACHESTORE_BROKERTBLACS (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 136 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERKEK (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERDSH (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERRSB (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERREADONLY (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 32 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_BROKERTO (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 8 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_EVENTS (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 16 MultiPage Allocator 0 (7 row(s) affected) CACHESTORE_SYSTEMROWSET (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 952 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SCHEMAMGR (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 6064 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_DBMETADATA (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 2352 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_TOKENPERM (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 3616 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_OBJPERM (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 368 MultiPage Allocator 0 (7 row(s) affected) USERSTORE_SXC (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 88 MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LBSS (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 256 MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_SNI_PACKET (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 5744 MultiPage Allocator 48 (7 row(s) affected) OBJECTSTORE_SERVICE_BROKER (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 256 MultiPage Allocator 0 (7 row(s) affected) OBJECTSTORE_LOCK_MANAGER (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 4096 VM Committed 4096 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 1640 MultiPage Allocator 0 (7 row(s) affected) Buffer Distribution Buffers ------------------------------ ----------- Stolen 8240 Free 16100 Cached 88818 Database (clean) 2119366 Database (dirty) 371998 I/O 0 Latched 9 (7 row(s) affected) Buffer Counts Buffers ------------------------------ -------------------- Committed 2604531 Target 2604531 Hashed 2491373 Stolen Potential 53825 External Reservation 17724 Min Free 304 Visible 177664 Available Paging File 1380845 (8 row(s) affected) Procedure Cache Value ------------------------------ ----------- TotalProcs 11877 TotalPages 86207 InUsePages 863 (3 row(s) affected) Global Memory Objects Buffers ------------------------------ -------------------- Resource 300 Locks 208 XDES 162 SETLS 8 SE Dataset Allocators 16 SubpDesc Allocators 8 SE SchemaManager 757 SQLCache 2069 Replication 143 ServerGlobal 27 XP Global 2 SortTables 1523 (12 row(s) affected) Query Memory Objects Value ------------------------------ ----------- Grants 1 Waiting 0 Available (Buffers) 45776 Maximum (Buffers) 65451 Limit 65451 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Last Target 68895 (11 row(s) affected) Small Query Memory Objects Value ------------------------------ ----------- Grants 0 Waiting 0 Available (Buffers) 3506 Maximum (Buffers) 3506 Limit 3506 (5 row(s) affected) Optimization Queue Value ------------------------------ -------------------- Overall Memory 1166524416 Target Memory 398794752 Last Notification 1 Timeout 6 Early Termination Factor 5 (5 row(s) affected) Small Gateway Value ------------------------------ -------------------- Configured Units 32 Available Units 32 Acquires 0 Waiters 0 Threshold Factor 250000 Threshold 250000 (6 row(s) affected) Medium Gateway Value ------------------------------ -------------------- Configured Units 8 Available Units 8 Acquires 0 Waiters 0 Threshold Factor 12 (5 row(s) affected) Big Gateway Value ------------------------------ -------------------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 (5 row(s) affected) MEMORYBROKER_FOR_CACHE Value -------------------------------- -------------------- Allocations 88647 Rate -250 Target Allocations 130221 Future Allocations 0 Last Notification 1 (5 row(s) affected) MEMORYBROKER_FOR_STEAL Value -------------------------------- -------------------- Allocations 6637 Rate 220 Target Allocations 48681 Future Allocations 0 Last Notification 1 (5 row(s) affected) MEMORYBROKER_FOR_RESERVE Value -------------------------------- -------------------- Allocations 19803 Rate 2302 Target Allocations 73496 Future Allocations 31702 Last Notification 1
View Replies !
SQL 2005 Express - Sqlserv.exe Memory Usage Always Increasing.
Hi, 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? Any feedback would be greatly appreciated.
View Replies !
Memory Usage In SQL Server
Hello all,How can I tell how much memory SQL Server is using on a server. OnWindows 2000, whenever I go to Task manager/processes/memory usage SQLServer seems to be showing 1,744,124K. On all of my servers withvarious size, usage of databases, all of them show SQL Server to beusing about the same amount of memory. Can someone explain this to me?Shouldn't it use more for larger databases, heavy hitting databases?Also, I normally check Dynamically configure SQL Server memory and putthe maximum threshold to a little bit less than the max on the server.The minimum query memory is set to 1024. Is that 1024 a subset of thememory used by SQL Server, or is this additional that can be used?Thanks,Raziq.*** Sent via Developersdex http://www.developersdex.com ***
View Replies !
SQL Server Memory Usage
Hi All, 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? Any input would be generously appreciated Cheers!
View Replies !
SQL Server - Memory Usage
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? Thanks.
View Replies !
Memory Usage For Sql Server
Hi, 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? Thanks in advance for your opinions, Shaili
View Replies !
Memory Usage Of SQL Server Ce
Hi, I'm wondering about the memory usage of SQL Server Ce and the Compact Framework. I've heard I can use only 32 MB per process. SQL Server Ce runs in process and my database file is about 60 MB big. 1. Does that means, I can only use for my application: 32 MB - CLR - SQL-Server Runtime - parts of my database file hold in memory 2. Is SQL Server Ce swapping parts of my database file into my process memory? If so, is there a chance to group some tables, that they are swapped together to increase performance? 3. Means big databases big memory usage in my process memory? Thanks, Stefan
View Replies !
SQL Server Process And Memory Usage
Hi AllSome my SQL Server are experience high memory usage.1. How can I detect which process which process cause the big memoryusage and not released?2. Which sql server components in this memory, and what are their usagedistribution?Any help will be appreciated.ThanksWillie
View Replies !
Increasing Memory Usage On SQL Server
Hi all, Our Production server has 4GB RAM and is running SQL Server 7.0. By default since SQL Server 7.0 Standard Edition can take up only less than 2GB, our SQL Server is now using only 1.8GB (leaving the rest for the OS, Windows 2000 Server). Inorder for SQL Server to take advantage of more than 2GB of RAM it is suggested that boot.ini be modified to include the switch, /3GB Has anyone seen any issues with doing this? Is it safe to do so on the Standard Edition of SQL Server 7.0? Thanks in advance, Praveena
View Replies !
Sql Server Performance And Memory Usage
Hi All, I'm new at SQL Server, I decided to use it in order to get all the advantages of using Vb.net and Sql. My server is a SQL standard version. I'm using a relational DB most of the time for complex select queries, everytime the server is used it performs 30 or 40 queries at the time, and I have recently realized that server consumes a lot of memory after one or two days of beign up. Let's say that if I restart the SQL Server memory usage is about 650 Mb, but after two days memory is 1.4Mb, I have used Sql Profiler and Tunning Assistant where it recommended me to create some indexes, which indeed I created them, but that did not solved the memory problem, although some queries run faster. My questions are: is this memory usage is normal ? if not, what should I check out to reduce memory usage? Thanks in advance George
View Replies !
Memory Usage For SQL SERVER 2000
Good day to all of you I am faced SQL SERVER 2000 Memory usage problem. I am using Windows 2000 Server SP4 and SQL SERVER 2000 SP4. When User running some in-house application software, the memory for sqlservr.exe was increased . But, when user logout from the software. The sqlservr.exe did not decrease the memory. I have around 100 Users in my company. SQL Memory will countinues increase till max memory usage in CPU. May i know how to order SQL SERVER need to purge memory when USER was log off from the Program? Or my SQL SERVER was corrupted /missing file? Please advice Many thanks! Melvin
View Replies !
SQL Server Memory Usage Continue To Increase
Hi, On the last Sunday 26/12/04 on 20:00 I got on one of our applications in the customer site this error: "There is insufficient system memory to run this query." Details: Computer: IBM XSERIES_345, dual CPU 2.8 Intel xeo, 1,047,952 KB ram, windows 2000 server sp4. Disk space: 4395 MB, Virtual memory: initial size 1536 MB, Max size 3072 MB. Registry size: current 14 MB, Max 90 MB. SQL Server 2000 SP 3, two production databases (1) Data size 100 MB, log size 15 MB (2) Data size 500 MB, log size 125 MB All the memory options are configured as default i.e. dynamically configure for memory and not changes in any sp_configure options. SQL server is the only major application running on the machine. Facts: On that evening the I've checked the Task Manager and found that SQL Server use 500 MB in Mem Usage and 1200 MB in VM size. In the query analyzer I execute the select query which results the "…insufficient system memory…" error on both databases and on the one with more data it failed with this error, but on the other database it completed successfully. I restart SQL Server service to solve it, and as for today 28/12/04 at 08:00 it use 590 MB in Mem Usage and 590 MB in VM size. I have checked the system with Performance monitor and didn't find any problems I use the Memory: Pages/sec, Memory: Available Bytes, Physical Disk: % Disk Time, Processor: % Processor Time, SQL Server Buffer: Buffer Cache Hit Ratio. We have 8 user connections for one of the database and 16 for the other one. Our system runs with this configuration for 2 months, I backup the databases FULL, DIFF and LOG backup. It's not real time system, and the load on the SQL is medium, but we use bad queries as update for all fields in one query (please don't ask…) I know that SQL server is greedy with the memory and released memory only if other process needs it. Any ideas why it happens? Thanks, Yaniv
View Replies !
Memory Leak...How To Get Memory Usage For Each Connections?
Hi Guys,I find there is memory leak in our SQL 2000+SP3 databass. We have aclient-server system, and the there are Jrun4.0 running JSP and someotherJava application talks to the database.However, for only less than 40 users, the SQL 2000 memory usage willincrease consistantly until it use all the memory it can get ,about 1.7Gmemory. Then eventually the SQL server process will hang in couple days.I don't know whether JRun JSP stuff or Java application is the culprit, andI am wondering if there is a way to monitor the memory usage of everydatabase connection.I would love to know what you guys think!Thanks a lot!
View Replies !
Too Much Memory Usage
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.
View Replies !
Getting Memory Usage Through T-SQL
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!
View Replies !
Memory Usage
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. Pete
View Replies !
Memory Usage
Hello, 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? Hope you got that ;-) Thank you
View Replies !
CPU And MEMORY Usage
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
View Replies !
Memory Usage.
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? Thanks for the help. Steve
View Replies !
CLR Memory Usage
Hi All - 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? Thanks, Cameron
View Replies !
Memory Usage
Not sure if this is the right forum, but here goes. We have a multistep SQL agent job that executes various data load / conversion SSIS packages. Most of the steps consist of simple script tasks. The agent job processes the Analysis Services database at the end. All cubes are MOLAP type. Our hardware is as follows: DEV €“ Single 3 Ghz Xeon with 2 GB RAM. Windows Server 2003 Standard SP1. SQL Server 2005 Standard Database, Analysis Services, and SSIS. PROD €“ Dual 3.6 Ghz Xeon with 4 GB RAM. Windows Server 2003 Standard R2. SQL Server 2005 Standard Database, Analysis Services, and SSIS. Both servers have the same SQL Server and Analysis Services Databases. Both servers use the same input files when running the agent job. The input files are located on another server and the biggest file is a 75 MB txt file. The agent job failed yesterday on the DEV Server while processing one of the SSIS data load steps. I checked the logs, but could not find any meaningful error. The only thing I noticed was that the server€™s memory was up to 2 GB and the server was responding sluggishly. I tried rerunning the job two more times, but they also failed during the data load step. I ran the same agent job on the PROD Server and it completed as it normally does in about 15 minutes. I reran the agent job on the DEV Server this morning without a problem. I know that the DEV Server was rebooted last night because they applied the July Microsoft patches to the development servers. When the agent job is run on the PROD Server, the SQL Server task take up about 1,700,000K of memory and barely budges during the duration of the job. Analysis Servers usually starts out around 290,000K and gets as high as 1,000,000K. But it always retreats back to about 700,000K when it reaches 1 GB. When the agent job is run on the DEV server, the SQL Server task gives up memory to Analysis services and the total memory allocation does not seem to exceed the physical amount available. Does anyone have any insight on what I am experiencing (memory wise)? Any suggestions on what I could've done yesterday to determine why the job failed? Why doesn€™t Analysis Services use more that 1 GB memory on the PROD Server? Is there an automated process that we can run prior to running the agent job to reduce the allocated memory? Right now, the agent job only fails on the DEV Server but we don€™t want this to become an issue on the PROD Server. Thanks!
View Replies !
How Much Available Memory And PF Usage?
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. thanks
View Replies !
SQL Memory Usage
Hello, I have SQL Server 2005 64 bit on Windows Server 2003 Enterprise 64 bit on a server with 8GB of RAM Task Manager reports the process sqlserver.exe consuming 7GB of memory Is this normal ?
View Replies !
X64 Memory Usage
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? Thanks Scott
View Replies !
Memory Usage
how to check the memory usage other than performance monitor for the server? When I execute the SP_WHO2 on the server, it showing each user have 5 connections there and the status is sleep. what is that means? Does it takes up memory? why have so many connection or entries for the same user, they all are using the same db with same application. Thanks!
View Replies !
Sqlservr.exe Memory Usage
Hello All,On all of my SQL servers (2000 with SP3), when I go to task manager andlook at memory usage, sqlservr.exe is always at 1.7gigs. If I reducethe maximum to let's say 1 gig it will go down to 1 gig. But if it iseven at 2gigs or 3 gigs it will be showing 1.7 gigs. Why? Is is ok toreduce the memory usage of sqlservr.exe?Raziq.*** Sent via Developersdex http://www.developersdex.com ***
View Replies !
Monitoring Memory Usage
Hi guys,I am considering upgrading memory in my MS SQL server cluster from 8GBto 16GB on each cluster node. I was wondering, whether you could suggesthow to measure the overall performance gain after the upgrade.Basically, I would like to estimate performance/cost ratio in 8GB, 16GBand possibly 24GB configuration. The cluster consists of 5 Windows 2003enterprise servers R2 with SQL Server 2005 (the 64 bit edition) on each.Thanks
View Replies !
Memory Usage - Max Setting
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 Replies !
Session Memory Usage
Hi,does someone know how much memory a user session takes on a SQLServerdatabase server? How many concurrent user sessions can a normal server(4 GB primary memory) handle, before connection pooling is needed?/Fredrik
View Replies !
SQL 2000 Memory Usage
Our production server has 3.3GB of memory and is running Windows 2000 server(service pack 2) and SQL 2000 Standard Edition(service pack 1). We have configured SQL to use the maximum memory for standard edition (2GB) In use, however, task manager shows SQL is only using 1.75GB maximum memory Our database size is 11Gb and we wouldexpect to see SQL using the full 2GB I have heard a similiar story from a colleague at another installation.. Does anyone have any insight into why this would be the case?
View Replies !
SQL Memory Usage High
hi all 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
View Replies !
Huge Memory Usage With BCP?
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. Ian Dundas DBA Assante Asset Management
View Replies !
Memory Usage SQL2k5
So I was on my friends server this afternoon, and I pulled up the task manager, 3270 out of 3963mb was used in ram. 1.6gb was physical/virtual for sqlservr.exe and another 1.3 for msftesql.exe. The guy that maintains them is kinda an idiot so my thought are that he does not have SQL Server configured right. He said he had done all he could to optimize memory, but if both of those applications are 2k5, there is no way. Can someone tell me what kind of memory usage they have experienced with a 2k5 instance running on medium usage sites.
View Replies !
SQL Memory Usage Problem
Hi to all, 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. Roy
View Replies !
Excessive Memory Usage
Hi there group. 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. Please assist.
View Replies !
Sqlserv.exe Memory Usage
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. Thanks in advance for replies
View Replies !
Process 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. Should I take any action? How?
View Replies !
|
TRACKER
