 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Setup / July 2008Seems like only 1GB RAM being used even when higher MAX RAM is spe
Have a server with 4GB RAM, running Win2K server, SQL2000 SE. I have not implemented any /3GB, PQE or AWE switches because of the obvious OS and SQL version limitations. When I open Enterprise manager > SQL Server Properties > General tab, I see "OS memory: 3775(MB)", which seems to be close to the 4GB physically installed. I figure the difference is overhead, etc. I have tinkered with the Memory settings, including a "Dynamically configure..." range, and a fixed memory size. I've tried just about every permutation to try and cajole it into using more than the 1GB it seems to only want to use. There are probably better places to check how much memory it is using besides the Task Manager, however, the Task Manager shows 1069mb (just over 1GB) for sqlserver.exe. So, I figure that even if Task Manager isn't the most accurate, it probably isn't off by a factor of 2. How can I get SQL to use the 2GB it's supposed to be able to use? I can tell you that the performance of this server is really bad (and there are many other opportunities for improvement - the subject of another interesting discussion) but with such bad performance, wouldn't it try to use more memory - especially when serving a 30GB+ database? There has to be something I'm missing. Thanks in advance, Larry Task Manager is not a reliable tool to tell you the amount of memory SQL Server is using. Use performance monitor instead. In performance monitor, select counter SQLServer:Memory Manager \ Total Server Manager (KB). This counter shows the amount of memory used by the SQL Server buffer pool, which accounts for most of the memory used by SQL Server. Linchi > Have a server with 4GB RAM, running Win2K server, SQL2000 SE. I have not > implemented any /3GB, PQE or AWE switches because of the obvious OS and SQL [quoted text clipped - 17 lines] > Thanks in advance, > Larry Thanks. Did that. Answer is "1023032", still looks like about a GB. Server is currently very active. Thanks, Larry Hi Larry, Yes, your SQL Server was occupying 1GB memory. If you want to have your SQL Server use a fixed 2GB memory, you can set min server memory and max server memory to the same 2GB. You may refer to: Server Memory Options http://msdn.microsoft.com/en-us/library/aa196734(SQL.80).aspx I noticed that you said that the performance of the server was really bad. I would recommend that you test your server with your SQL Server database under an estimated workload to see if there are any performance issue. You can also refer to this article to troubleshoot performance issue when you encounter it: How to troubleshoot SQL Server performance issues http://support.microsoft.com/kb/298475/en-us If you have any other questions or concerns, please feel free to let me know. Have a nice day! Best regards, Charles Wang Microsoft Online Community Support =========================================================== Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. =========================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ============================================================  SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Thank you for your post. I had already also tried (as one of my many attempts at every permutation possible in the SQL Ent Mgr memory configuration applet) to do that, and it had no effect at raising the amount of CPU used. This is one of those problems we've had for a while and I've made various feeble attempts recently at finding a solution (e.g., research and forums like this)....I am thinking I'm going to have to get Micro$oft involved with a paid $upport call. I'm sure there will be a hidden configuration governor somewhere and I'll be at fault. Thanks for any additional insights, Larry The only governors are the ones that dictate how much memory a specific edition can use. It sounds like you do not in fact have standard edition but MSDE instead which will only use 1GB regardless of how much you have in total. If you do in fact have Standard edition then all of the defaults should allow you to use up to 2GB including the MemToLeave area which will make it look like you are only using 1.7GB if you look in Task Manager. Make sure you do not have the Max Memory set to anything other than the default. SQL Server can swap memory out if the OS is calling for more. Are there any apps running on that server other than SQL Server? Can you post the results of sp_configure? SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Thank you for your post. > I had already also tried (as one of my many attempts at every permutation [quoted text clipped - 10 lines] > Thanks for any additional insights, > Larry Interesting observation on it being MSDE, however if that were the case, I don't think it would allow a 30Gb+ database size. Pretty sure it's standard edition, but I was not the one who bought/installed it. As soon as I can log into that server, Is this the info you wanted? HOpefully there's something meaningful in there. affinity mask -2147483648 2147483647 0 0 allow updates 0 1 0 0 awe enabled 0 1 0 0 c2 audit mode 0 1 0 0 cost threshold for parallelism 0 32767 5 5 Cross DB Ownership Chaining 0 1 0 0 cursor threshold -1 2147483647 -1 -1 default full-text language 0 2147483647 1033 1033 default language 0 9999 0 0 fill factor (%) 0 100 0 0 index create memory (KB) 704 2147483647 0 0 lightweight pooling 0 1 0 0 locks 5000 2147483647 0 0 max degree of parallelism 0 32 0 0 max server memory (MB) 4 2147483647 3023 3023 max text repl size (B) 0 2147483647 15000000 15000000 max worker threads 32 32767 32767 32767 media retention 0 365 0 0 min memory per query (KB) 512 2147483647 1024 1024 min server memory (MB) 0 2147483647 0 0 nested triggers 0 1 1 1 network packet size (B) 512 32767 4096 4096 open objects 0 2147483647 0 0 priority boost 0 1 1 1 query governor cost limit 0 2147483647 0 0 query wait (s) -1 2147483647 -1 -1 recovery interval (min) 0 32767 0 0 remote access 0 1 1 1 remote login timeout (s) 0 2147483647 20 20 remote proc trans 0 1 0 0 remote query timeout (s) 0 2147483647 600 600 scan for startup procs 0 1 0 0 set working set size 0 1 0 1 show advanced options 0 1 1 1 two digit year cutoff 1753 9999 2049 2049 user connections 0 32767 0 0 user options 0 32767 0 0 Just run select @@version in a query window. It will give you the version and edition. Chris > Interesting observation on it being MSDE, however if that were the case, I > don't think it would allow a 30Gb+ database size. Pretty sure it's [quoted text clipped - 42 lines] > user connections 0 32767 0 0 > user options 0 32767 0 0 Thanks that's good to know. Here is the result: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Which means you may be failing into this trap http://support.microsoft.com/kb/899761/ Chris Since MS08-040 came out I would be looking at build 2050 anyway. > Thanks that's good to know. > Here is the result: > Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows > NT > 5.0 (Build 2195: Service Pack 4) Man, you had me going for a minute, unfortunately it does not apply because AWE is not configured on this server: name minimum maximum config_value run_value awe enabled 0 1 0 0 Also, this computer has 4Gb, and I am just trying to get it to use 2Gb instead of the 1Gb it is using, so even 50% of the available memory would be a "good thing" in my particular case. Right now it will only use 25%. Larry No you don't have AWE enabled but you do the the "set working set size" enabled and it should be off. Change that back to the default and you probably have to restart but I bet that will fix it. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Man, you had me going for a minute, unfortunately it does not apply > because [quoted text clipped - 8 lines] > > Larry Thanks, I made that change: sp_configure 'set working set size',0 reconfigure (restarted sqlserver.exe) confirmed current runtime setting = 0 Ran a bunch of huge queries, large transactions, beat up the server, etc... Memory usage still seems stuck at right at 1,062Mb. Anyone, anyone, anyone ? :) Larry > No you don't have AWE enabled but you do the the "set working set size" > enabled and it should be off. Change that back to the default and you [quoted text clipped - 12 lines] > > > > Larry Well something certainly isn't adding up here. What are the values of both the target and total server memory for the SQL Server memory counters? How much free memory does Perfmon show? If you really are querying more than 2GB worth of different data you should see the memory usage go to about 1.7GB with that configuration. I see the Max Memory was set to ~3GB which is more than what you have but just to rule it out you may want to rest that back to the default of the max as well. Are you sure there isn't some other app on the server that may be eating up memory? Are there any settings in the boot.ini that are not default? You should really think about upgrading the OS and SQL Server in the near future anyway. Std edition in 2005 & 2008 can use the max memory that the OS can access and if there was anything funky with the OS or SQL builds an upgrade should surely fix that. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Thanks, > I made that change: [quoted text clipped - 26 lines] >> > >> > Larry Yes, it does not add up. I just reconfirmed all of this. Here is boot.ini: -------------------------------------- [boot loader] timeout=30 default=multi(0)disk(0)rdisk(0)partition(2)\WINNT [operating systems] multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" /fastdetect -------------------------------------- Perfmon shows available MBytes = 2148 so 2Gb are just sitting there. I made the changes you suggested (set min = 0, max = 2Gb), and restarted sql service. I run a halacious join query and watch the memory increase, increase,increase, and then in task manager it gets stuck at its usual 1062Mb, which is consistent with the PerfMon shows. Then in PerfMon, the %Disk Time goes through the roof. This is what happens ALL the time, regardless of what settings are changed. Your suggestion to move to later versions is valid, but I don't see why I can't get THIS server to use at least 2Gb (or 1.7Gb who's counting?) !!! Does Microsoft have any ideas? Thanks, Larry > I made the changes you suggested (set min = 0, max = 2Gb), and restarted > sql Actually I said to put the max back at the default which is 2147483647. No tthat I think it is the problem but just wanted to eliminate it. If SQL is taking 1GB and there is 2GB left where is the other 1GB going? What does task manager say is using that other GB? The only other thing I can think of is that someone set the -g startup option to a non-default value as well. If you look at the server properties in EM you can see the startup options. See if there is a -g and if so what the value is. If it is more than 256 you might want to question why and that will be the problem. If not I suggest you contact MS PSS so they can log into your system and have a look. Other than the AWE issue with SP4 I have not seen any bugs that would cause this behavior. Please let us know what you come up with. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Yes, it does not add up. > I just reconfirmed all of this. [quoted text clipped - 25 lines] > Thanks, > Larry Startup Parameters: -d, -e, -l no -g. sp_configure max server memory (MG) = 2047 Now, that 2047 matches the setting I put into the Dynamic Memory, Max setting in Ent Mgr for SQL. Of course, this doesn't match your setting of 2147483647 which is some very large number (2Tb?), so I did this: sp_configure 'max server memory', 2147483647 reconfigure I will restart sqlservr.exe at the lunch break when I can kick everyone off. I will repost near end of day. Thanks > > I made the changes you suggested (set min = 0, max = 2Gb), and restarted > > sql [quoted text clipped - 42 lines] > > Thanks, > > Larry Did you look to see what Task manager shows the other 1GB being used for? Are their any other apps (other than normal windows services etc.) running on this server? SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > Startup Parameters: > -d, -e, -l [quoted text clipped - 73 lines] >> > Thanks, >> > Larry Hi Larry, I think that there are some misunderstandings among these posts. Though you set your SQL Server to use a fixed memory size, SQL Server will not initially allocate 2GB memory on startup or when your SQL Server does not really consume so much memory. Fixed memory means that your SQL Server can use the memory to the fixed size and will not release/increase the memory after the memory reaches to the fixed size. From SQL Server Books Online, http://msdn.microsoft.com/en-us/library/aa196734 (SQL.80).aspx, we can find the following description regarding "min server memory": Use min server memory to guarantee a minimum amount of memory to an instance of SQL Server. SQL Server will not immediately allocate the amount of memory specified in min server memory on startup. However, after memory usage has reached this value due to client load, SQL Server cannot free memory from the allocated buffer pool unless the value of min server memory is reduced. Then we look back to your issue, I noticed that you changed the your SQL Server to use min=0 and max=2GB, in this case, SQL Server will dynamically allocate memory and it is usual that SQL Server will sometimes release memory though it has not reached to the maximum memory size. Please still set your SQL Server's min and max memory to the same size 2 GB and wait to see the memory situation when there are many heavy workload. Hope this helps. If you have any other questions or concerns, please feel free to let me know. Have a nice day~! Best regards, Charles Wang Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ========================================================= SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Thanks Charles. I understand that I should not expect the memory to instantly reach the minimum. That is not the problem. The problem is that it never exceeds 1Gb even though the I/O to the F-drive (where the data files are stored) is totally maxxed-out. Now, this is a big database, and even 2Gb will not be enough for some of the queries in this system (which are also being evaluated for performance as a separate project), but having 2Gb will certainly be better than 1Gb. I can assure you that the problem is not that the memory is not needed. It is needed. I'm confident that this 30Gb+ database could use much more than even 2Gb to run at its best. (And as a separate project, we're evaluating new hardware, Win2008 and SQL2008, so no need to mention those options.) I'm trying to alleviate the pain in the short term. The problem is that no matter which settings I put in place (so far), I can't get SQLSERVR to use more than 1Gb. No matter which settings, it will never exceed 1Gb. No matter what I put in for min or max, etc, it will never exceed 1Gb RAM even though there are complex queries running throughout the day. I can run a single complex query on one of the bigger tables (250K rows) with lots of joins, etc, and I can literally watch the memory increase. It goes 100M, 200M, ......800M, 900M, 1000M, 1069M and then it just stays at 1069M even though the query is still running for several more minutes. And during this time when the RAM is stuck at 1069M, the disk utilization goes to 700%, which would indicate lots of paging from RAM to disk, so I'm sure that if SQL could get access to it, it would use more than 1GB, so I hope I have clearly shown that the problem is not that SQL does not need the RAM. Regards, Larry > Hi Larry, > I think that there are some misunderstandings among these posts. Though you set your SQL [quoted text clipped - 32 lines] > This posting is provided "AS IS" with no warranties, and confers no rights. > ========================================================= Hi Larry, Thank you for your detail response for clarifying the issue. So your real concern is that your SQL Server can only consume maximum 1069MB physical memory on your server that has actually 4GB memory, though you had configured your SQL Server to use minimum 2GB memory. Your expectation is to have your SQL Server use almost 2GB memory. If I have misunderstood, please let me know. By design, Windows OS will cover 2GB memory for system use and the left 2GB memory is for all Windows applications. SQL Server is one of those applications and it should be normal that it cannot utilize the maximum 2GB memory since other applications or services will also consume some memory. Could you please check how much memory is used other applications running on your server? If there are some heavy load T-SQL queries, I recommend that you enable /3GB switch in the Boot.ini file on your Windows Server, for example: [Operating Systems] multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows Server 2003" /fastdetect /3GB In this case, Windows OS will keep 1GB for OS and leave 3GB for user mode applications. After that configure your SQL Server minimum memory to 2GB. Hope this helps. Best regards, Charles Wang Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ========================================================= SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Yes, it appears you understand the situation perfectly. There are no other significant applications that run on this server. It is not a domain controller, nor an IIS Server, nor a DNS server, etc. The largest memory allocation in task manager is sqlservr.exe at 1,064Mb, with rtvscan.exe a distant second at 52Mb. Total Mem Usage (task manager): 1411372K / 5794148K (of course the amount above the 4Gb is the pagefile.) In this case, if all apps were to share the 2Gb, then I would expect sqlserver.exe to at least be using around 1.7Gb calculated as 2Gb (available for all Windows applications) - 1411372 (current total usage) + (1067400 current sql usage) = 1.66Gb for sqlservr.exe. (Yes, I know I've rounded badly and it is all to the advantage of reduced space for sqlservr.exe for sake of argument, so no need to clarify that a Gb is > 1000Mb, etc) So after all this, there is still no explanation that sqlservr.exe is not using all the memory it should. Next I will try the 3Gb option and see what happens....even though theoretically, I shouldn't need to do this - but I'm willing to try just about anything. Larry Everything you're saying about how much memory is being used, seems to be evidenced only from Task Manager. A couple of questions have asked about what performance monitor says about total server memory and target server memory, but I haven't seen your answer to those questions. I'd also be curious about what is reported under the MSSQL:Memory Manager/Buffer Manager counters. (I never trust Task Manager to really tell me how much memory is being used by SQL Server... it usually lumps the higher memory allocated to SQL Server into PF usage instead. Performance Monitor does not try to pull the wool over your eyes like this.) Also, are there any observable symptoms that make you believe that less RAM is being used? I didn't review every single message in the thread but I don't see anything about actual performance problems. I'm not always a fan of "it ain't broke, don't fix it" but if you have no actual symptoms of insufficient memory, and are chasing this just because you saw the number in task manager and didn't like it... On 7/22/08 10:35 PM, in article 9362B897-200B-4E44-8FF4-435928B82E0A@microsoft.com, "Larry Epn" <larryepn@noemail.noemail> wrote: > Yes, it appears you understand the situation perfectly. > There are no other significant applications that run on this server. It is [quoted text clipped - 15 lines] > about anything. > Larry Earlier in the thread, all memory usage shown in task manager has been verified by using the PerfMon to look at memory usage in addition to many other parameters (paging, disk %, etc, typically used to troubleshoot a SQL server) Because of the consistent correlation between what Task manager is showing and PerfMon is showing, I've just been referencing Task Manager since an earlier post in this thread because it is easier to pull up. If total usage in the system is 1.4Gb (including all OS, etc), then it is clear that SQL can't be using all 2Gb (or even 1.7Gb) RAM. But thanks for your input. The most difficult problems always turn out to be the most satisfying to solve. > Everything you're saying about how much memory is being used, seems to be > evidenced only from Task Manager. A couple of questions have asked about [quoted text clipped - 36 lines] > > about anything. > > Larry I was going to follow your suggestion and set the /3GB switch in boot.ini. I decided to read a bit more of what this would do...seeing as I am working on a server around 1300 miles from my house and it's a really long drive to fix a boot problem... I came across this article: http://technet.microsoft.com/en-us/library/bb124810.aspx In here it says to not use this switch on Windows 2000 Standard because it is not supported. Plus, this really doesn't get to the root of the problem anyway, which is why the server won't use the 1.65Gb clearly available to it according to everything I've read on MSDN and other forums or within this thread. Any last attempts before I call Microsoft for support? (Sorry for the attitude, but I am getting to the point of believing the conspiracy theory that Micro$oft burries little goodies like this to increase support revenue...) Larry Hi Larry, Thank you for your response. I am sorry that after long discussions under this thread, I omitted that your server was Windows 2000 Server. Indeed /3GB switch and PAE are not supported on Windows 2000 server. In this case, you can only enable AWE for your SQL Server to allow it to consume more than 2GB memory. I read through the posts again and noticed that Adrew had recommended that you enabled AWE, however it seemed that you did not do it but just configure "set working set size". Please try running the following command: sp_configure 'show advanced options', 1 RECONFIGURE GO sp_configure 'awe enabled', 1 RECONFIGURE GO sp_configure 'max server memory', 4000 RECONFIGURE GO sp_configure 'min server memory', 2000 RECONFIGURE GO If this still could not help you resolve the issue, please make a call to Microsoft Customer Support Services (CSS) for deeply troubleshooting this issue. Please feel free to let me know if you have any other questions or concerns. Have a nice day! Best regards, Charles Wang Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ========================================================= SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= I followed your directions. No difference. I'll post results from Microsoft tech support. Larry I'm following this thread and the problem is really interesting. However, in your following reply, I wish you'd tell us the "Total Server Memory" and "Target Server Memory" counters' results. I know, you gave us lots of proves about the memory consumption. But the results of these counters would definitely prove you had enough RAM but SQL Server can't take advantage of it. SignatureEkrem Önsoy > Yes, it does not add up. > I just reconfirmed all of this. [quoted text clipped - 25 lines] > Thanks, > Larry We also had similar situtaion where SqlServer 2000 was using the complete memeory installed in the server. We have dual core CPU with windows 2003 os. If your machine is 32 bit your system will never use more than 2 gb RAM per cpu. So server won't allow SQL to use more than 2gb per CPU. And if it is 64 bit machine than it will use more than 2 gb of RAM per CPU. As we have dual core machine and had installed 8 gb RAM but 4 gb is just lying and it is used at all. I may be out of synch but hope this helps to reduce your problem. > I'm following this thread and the problem is really interesting. > [quoted text clipped - 34 lines] > > Thanks, > > Larry You may want to check out /3GB, AWE and /PAE (32bit). http://support.microsoft.com/kb/274750 http://technet.microsoft.com/en-us/library/bb124810.aspx http://msdn.microsoft.com/en-us/library/ms810461.aspx http://www.microsoft.com/whdc/system/platform/server/PAE/default.mspx Setting min = max is "fixed memory" in SQL Server 2000. The OS takes ~300mb off the top for OS functions. Assuming you did not limit SQL Server memory... On a 3GB or 4GB system SQL Server will take ~1.7gb. If you apply the /3GB switch to the boot.ini on a 4gb system SQL will take ~2.7gb. If you have more than 4gb of physical memory the extended memory is non-paged pool. > We also had similar situtaion where SqlServer 2000 was using the complete > memeory installed in the server. We have dual core CPU with windows 2003 [quoted text clipped - 54 lines] >> > Thanks, >> > Larry First of all, it's not me who is experiencing this problem. The OP is someone else. Regarding to your scenario, you said your SQL Server 2000 instance is installed on a Windows Server 2003 OS. If your SQL Server 2000 is Enterprise Edition and your Windows Server 2003 is also Enterprise Edition then you can take advantage of your 8GB of RAM. How to configure SQL Server to use more than 2 GB of physical memory: http://support.microsoft.com/kb/274750/en-us Memory Support and Windows Operating Systems: http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx SignatureEkrem Önsoy > We also had similar situtaion where SqlServer 2000 was using the complete > memeory installed in the server. We have dual core CPU with windows 2003 [quoted text clipped - 54 lines] >> > Thanks, >> > Larry It is Sql 200 enterprise, 2003 Enterprise edition. And we have a cluster of dual core server(32 bit). Both machines are equipped with 8gb of RAM. We have tried all sort of option but still sql is unable to use more 1.5 - 1.7 gb per CPU. The userbase of our application is 1500+ and it is database oriented application so we see heavy traffic in the application. As such we don't have any issues with anything but I don't know why it does not go more than 2gb. I will try the URL sent by you. Hopefully it will solve the problem. --Hemant > First of all, it's not me who is experiencing this problem. The OP is > someone else. [quoted text clipped - 69 lines] > >> > Thanks, > >> > Larry Yes if you really do have Enterprise editions of both the OS and SQL Server you can use AWE and PAE to access more than 2GB as per the documentation listed. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > It is Sql 200 enterprise, 2003 Enterprise edition. And we have a cluster > of [quoted text clipped - 103 lines] >> >> > Thanks, >> >> > Larry After all the postings and several days of going back-and-forth with MS with a special SQL error monitor, the problem turns out to be a simple thing. First, let me reiterate the OS/SQL versions: Win2000 Server Standard SQL 2000 Standard 4GB RAM. No /3GB switch (isn't supported by Win2000 STD anyway) No AWE in SQL (wouldn't make a difference anyway due to OS and SQL version limitations) The whole problem was the setting for Max Worker Threads. The default is 255, and someone (I don't think I did it...) set it to 32767.... Here is more from the Microsoft Tech ------------------------------------------------------------------------------------------- During startup SQL server reserves memory for MTL(MeM To leave) which is default and calculated this way. -g is 256 + (number or worker threads default 255 ) which is equal to 384 MB and leaving behind 1.7 GB of address space for B pool. So from previous logs it indicated that the Max worker threads is set to maximum which is 32767 and each worker thread needs 1/2 MB of memory. if we do the math which would be 256+(32767x1/2mb) which is closer to 16 GB which is not possible. So I think what was happening is that was calculating something where the MTL is reserving more space may be like a 1 GB and SQL was not able to address more than 1 GB. so I requested you to change back the min to 0 and Max to close to 1.7 GB . ----------------------------------------------------------------------------------- And I did change it back and my SQL is now using 1.7GB according to PerfMon, etc. Simple problem, simple solution. Too bad I had to use a Microsoft support call to get to the bottom of this. But, as you can see, I wasn't alone in my ignorance of this setting :) Larry Ahh, I should have looked at that. I was sure it would have been the -g switch but when you said it was not set I spaced and didn't catch the setting was changed in sp_configure. I knew each worker thread uses .5MB in 2000 but I have to say I have never run across a server in which this was set so absurdly high so it didn't come to mind. Glad to see you got it and sorry you had to go to PSS. I used to had a spreadsheet that I plopped in the results of sp_configure and it would highlight any changed settings. I rarely do anything with 2000 anymore and seemed to have misplaced that. I have been asking MS for quite some time to add an extra column to the result set (in 2005 you use sys.configurations) which shows anything changed from the default but no luck so far. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > After all the postings and several days of going back-and-forth with MS > with [quoted text clipped - 42 lines] > ignorance of this setting :) > Larry Well, at least I got the answer, and I wanted to make sure I posted it here for the next poor soul who gets stuck on that one.... Thanks for everyone who tried to help. Larry |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.