|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Max server memory being ignored
memory setting. The server has 16 GB of memory, and we set the max server memory at 14 GB. Ops Manager keeps warning of low memory and task manager shows all memory being used. This is a deticated 64-bit Server 2003 box, so no non-SQL applications are utilizing resources. Due to a new initative we need to figure this out. For the application we are implimented there is the need to set-up a second instance of SQL on the server for security seporation. The new application will be more resource intensive, however the current instance hosts more critical appliations. In order to ensure one does not consume too many resources my goal is to set a max for reach instance (also leaving a few GB free for the opperating system). As a first step we have installed an additional 16 GB of memory, bring the box up to 32 GB. I have not yet installed the second instance of SQL Server, however all the memory on the box is being utilized. Any assistance as to what I am missing in order to limit the memory usage in SQL Server would be appreciated. Installed Memory = 32 GB Max Server Memory = 14 GB Current values: Physical Memory: Total = 33553396 Physical Memory: Avaliable = 243700 Physical Memory: System Cache = 444752 From sys.dm_os_performance_counters (where object_name like %memory%) SQLServer:Memory Manager: Connection Memory (KB) = 2776 SQLServer:Memory Manager: Granted Workspace Memory (KB) = 0 SQLServer:Memory Manager: Lock Memory (KB) = 8160 SQLServer:Memory Manager: Lock Blocks Allocated = 8036 SQLServer:Memory Manager: Lock Owner Blocks Allocated = 9176 SQLServer:Memory Manager: Lock Blocks = 27 SQLServer:Memory Manager: Lock Owner Blocks = 135 SQLServer:Memory Manager: Maximum Workspace Memory (KB) = 22287680 SQLServer:Memory Manager: Memory Grants Outstanding = 0 SQLServer:Memory Manager: Memory Grants Pending = 0 SQLServer:Memory Manager: Optimizer Memory (KB) = 4272 SQLServer:Memory Manager: SQL Cache Memory (KB) = 65728 SQLServer:Memory Manager: Target Server Memory (KB) = 30736248 SQLServer:Memory Manager: Total Server Memory (KB) = 30736248 Why would max workspace memory and target server memory be so high? I thought total server memory was adjusted by the max server memory setting. Can you confirm the max memory setting by running sp_configure and posting
here? -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Jason" <jsho***@gmail.com> wrote in message Our SQL Server 2005 database appears to be ignoring the max servernews:41ce1c04-a001-4fe4-8367-214cd719f09e@z1g2000yqn.googlegroups.com... memory setting. The server has 16 GB of memory, and we set the max server memory at 14 GB. Ops Manager keeps warning of low memory and task manager shows all memory being used. This is a deticated 64-bit Server 2003 box, so no non-SQL applications are utilizing resources. Due to a new initative we need to figure this out. For the application we are implimented there is the need to set-up a second instance of SQL on the server for security seporation. The new application will be more resource intensive, however the current instance hosts more critical appliations. In order to ensure one does not consume too many resources my goal is to set a max for reach instance (also leaving a few GB free for the opperating system). As a first step we have installed an additional 16 GB of memory, bring the box up to 32 GB. I have not yet installed the second instance of SQL Server, however all the memory on the box is being utilized. Any assistance as to what I am missing in order to limit the memory usage in SQL Server would be appreciated. Installed Memory = 32 GB Max Server Memory = 14 GB Current values: Physical Memory: Total = 33553396 Physical Memory: Avaliable = 243700 Physical Memory: System Cache = 444752 From sys.dm_os_performance_counters (where object_name like %memory%) SQLServer:Memory Manager: Connection Memory (KB) = 2776 SQLServer:Memory Manager: Granted Workspace Memory (KB) = 0 SQLServer:Memory Manager: Lock Memory (KB) = 8160 SQLServer:Memory Manager: Lock Blocks Allocated = 8036 SQLServer:Memory Manager: Lock Owner Blocks Allocated = 9176 SQLServer:Memory Manager: Lock Blocks = 27 SQLServer:Memory Manager: Lock Owner Blocks = 135 SQLServer:Memory Manager: Maximum Workspace Memory (KB) = 22287680 SQLServer:Memory Manager: Memory Grants Outstanding = 0 SQLServer:Memory Manager: Memory Grants Pending = 0 SQLServer:Memory Manager: Optimizer Memory (KB) = 4272 SQLServer:Memory Manager: SQL Cache Memory (KB) = 65728 SQLServer:Memory Manager: Target Server Memory (KB) = 30736248 SQLServer:Memory Manager: Total Server Memory (KB) = 30736248 Why would max workspace memory and target server memory be so high? I thought total server memory was adjusted by the max server memory setting. The max server values (using sp_configure) show as the following:
Name: max server memory (MB) Minimum: 16 Maximum: 2147483647 Config Value: 14680064 Run Value: 14680064 Below are the rest of the results, incase any of them may shine other light. name minimum maximum config_value run_value Ad Hoc Distributed Queries 0 1 0 0 affinity I/O mask -2147483648 2147483647 0 0 affinity mask -2147483648 2147483647 0 0 affinity64 I/O mask -2147483648 2147483647 0 0 affinity64 mask -2147483648 2147483647 0 0 Agent XPs 0 1 1 1 allow updates 0 1 0 0 awe enabled 0 1 0 0 blocked process threshold 0 86400 0 0 c2 audit mode 0 1 0 0 clr enabled 0 1 0 0 common criteria compliance enabled 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 Database Mail XPs 0 1 1 1 default full-text language 0 2147483647 1033 1033 default language 0 9999 0 0 default trace enabled 0 1 1 1 disallow results from triggers 0 1 0 0 fill factor (%) 0 100 90 90 ft crawl bandwidth (max) 0 32767 100 100 ft crawl bandwidth (min) 0 32767 0 0 ft notify bandwidth (max) 0 32767 100 100 ft notify bandwidth (min) 0 32767 0 0 index create memory (KB) 704 2147483647 0 0 in-doubt xact resolution 0 2 0 0 lightweight pooling 0 1 0 0 locks 5000 2147483647 0 0 max degree of parallelism 0 64 0 0 max full-text crawl range 0 256 4 4 max server memory (MB) 16 2147483647 14680064 14680064 max text repl size (B) 0 2147483647 65536 65536 max worker threads 128 32767 0 0 media retention 0 365 2 2 min memory per query (KB) 512 2147483647 1024 1024 min server memory (MB) 0 2147483647 128 128 nested triggers 0 1 1 1 network packet size (B) 512 32767 4096 4096 Ole Automation Procedures 0 1 0 0 open objects 0 2147483647 0 0 PH timeout (s) 1 3600 60 60 precompute rank 0 1 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 admin connections 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 Replication XPs 0 1 0 0 scan for startup procs 0 1 1 0 server trigger recursion 0 1 1 1 set working set size 0 1 0 0 show advanced options 0 1 1 1 SMO and DMO XPs 0 1 1 1 SQL Mail XPs 0 1 1 1 transform noise words 0 1 0 0 two digit year cutoff 1753 9999 2049 2049 user connections 0 32767 0 0 user options 0 32767 0 0 Web Assistant Procedures 0 1 0 0 xp_cmdshell 0 1 0 0 Show quoteHide quote On Dec 2, 10:06 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote: > Can you confirm the max memory setting by running sp_configure and posting > here? > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau > > "Jason" <jsho***@gmail.com> wrote in message > > news:41ce1c04-a001-4fe4-8367-214cd719f09e@z1g2000yqn.googlegroups.com... > Our SQL Server 2005 database appears to be ignoring the max server > memory setting. The server has 16 GB of memory, and we set the max > server memory at 14 GB. Ops Manager keeps warning of low memory and > task manager shows all memory being used. This is a deticated 64-bit > Server 2003 box, so no non-SQL applications are utilizing resources. > > Due to a new initative we need to figure this out. For the application > we are implimented there is the need to set-up a second instance of > SQL on the server for security seporation. The new application will be > more resource intensive, however the current instance hosts more > critical appliations. In order to ensure one does not consume too many > resources my goal is to set a max for reach instance (also leaving a > few GB free for the opperating system). > > As a first step we have installed an additional 16 GB of memory, bring > the box up to 32 GB. I have not yet installed the second instance of > SQL Server, however all the memory on the box is being utilized. > > Any assistance as to what I am missing in order to limit the memory > usage in SQL Server would be appreciated. > > Installed Memory = 32 GB > Max Server Memory = 14 GB > > Current values: > Physical Memory: Total = 33553396 > Physical Memory: Avaliable = 243700 > Physical Memory: System Cache = 444752 > > From sys.dm_os_performance_counters (where object_name like %memory%) > SQLServer:Memory Manager: Connection Memory (KB) = 2776 > SQLServer:Memory Manager: Granted Workspace Memory (KB) = 0 > SQLServer:Memory Manager: Lock Memory (KB) = 8160 > SQLServer:Memory Manager: Lock Blocks Allocated = 8036 > SQLServer:Memory Manager: Lock Owner Blocks Allocated = 9176 > SQLServer:Memory Manager: Lock Blocks = 27 > SQLServer:Memory Manager: Lock Owner Blocks = 135 > SQLServer:Memory Manager: Maximum Workspace Memory (KB) = 22287680 > SQLServer:Memory Manager: Memory Grants Outstanding = 0 > SQLServer:Memory Manager: Memory Grants Pending = 0 > SQLServer:Memory Manager: Optimizer Memory (KB) = 4272 > SQLServer:Memory Manager: SQL Cache Memory (KB) = 65728 > SQLServer:Memory Manager: Target Server Memory (KB) = 30736248 > SQLServer:Memory Manager: Total Server Memory (KB) = 30736248 > > Why would max workspace memory and target server memory be so high? I > thought total server memory was adjusted by the max server memory > setting. That is odd. It really should be capping the memory at 14GB. What do you
get from running: xp_msver -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Jason" <jsho***@gmail.com> wrote in message The max server values (using sp_configure) show as the following:news:81aa7c01-1e83-4623-81fc-475fa9807041@t2g2000yqm.googlegroups.com... Name: max server memory (MB) Minimum: 16 Maximum: 2147483647 Config Value: 14680064 Run Value: 14680064 Below are the rest of the results, incase any of them may shine other light. name minimum maximum config_value run_value Ad Hoc Distributed Queries 0 1 0 0 affinity I/O mask -2147483648 2147483647 0 0 affinity mask -2147483648 2147483647 0 0 affinity64 I/O mask -2147483648 2147483647 0 0 affinity64 mask -2147483648 2147483647 0 0 Agent XPs 0 1 1 1 allow updates 0 1 0 0 awe enabled 0 1 0 0 blocked process threshold 0 86400 0 0 c2 audit mode 0 1 0 0 clr enabled 0 1 0 0 common criteria compliance enabled 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 Database Mail XPs 0 1 1 1 default full-text language 0 2147483647 1033 1033 default language 0 9999 0 0 default trace enabled 0 1 1 1 disallow results from triggers 0 1 0 0 fill factor (%) 0 100 90 90 ft crawl bandwidth (max) 0 32767 100 100 ft crawl bandwidth (min) 0 32767 0 0 ft notify bandwidth (max) 0 32767 100 100 ft notify bandwidth (min) 0 32767 0 0 index create memory (KB) 704 2147483647 0 0 in-doubt xact resolution 0 2 0 0 lightweight pooling 0 1 0 0 locks 5000 2147483647 0 0 max degree of parallelism 0 64 0 0 max full-text crawl range 0 256 4 4 max server memory (MB) 16 2147483647 14680064 14680064 max text repl size (B) 0 2147483647 65536 65536 max worker threads 128 32767 0 0 media retention 0 365 2 2 min memory per query (KB) 512 2147483647 1024 1024 min server memory (MB) 0 2147483647 128 128 nested triggers 0 1 1 1 network packet size (B) 512 32767 4096 4096 Ole Automation Procedures 0 1 0 0 open objects 0 2147483647 0 0 PH timeout (s) 1 3600 60 60 precompute rank 0 1 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 admin connections 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 Replication XPs 0 1 0 0 scan for startup procs 0 1 1 0 server trigger recursion 0 1 1 1 set working set size 0 1 0 0 show advanced options 0 1 1 1 SMO and DMO XPs 0 1 1 1 SQL Mail XPs 0 1 1 1 transform noise words 0 1 0 0 two digit year cutoff 1753 9999 2049 2049 user connections 0 32767 0 0 user options 0 32767 0 0 Web Assistant Procedures 0 1 0 0 xp_cmdshell 0 1 0 0 Show quoteHide quote On Dec 2, 10:06 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote: > Can you confirm the max memory setting by running sp_configure and posting > here? > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau > > "Jason" <jsho***@gmail.com> wrote in message > > news:41ce1c04-a001-4fe4-8367-214cd719f09e@z1g2000yqn.googlegroups.com... > Our SQL Server 2005 database appears to be ignoring the max server > memory setting. The server has 16 GB of memory, and we set the max > server memory at 14 GB. Ops Manager keeps warning of low memory and > task manager shows all memory being used. This is a deticated 64-bit > Server 2003 box, so no non-SQL applications are utilizing resources. > > Due to a new initative we need to figure this out. For the application > we are implimented there is the need to set-up a second instance of > SQL on the server for security seporation. The new application will be > more resource intensive, however the current instance hosts more > critical appliations. In order to ensure one does not consume too many > resources my goal is to set a max for reach instance (also leaving a > few GB free for the opperating system). > > As a first step we have installed an additional 16 GB of memory, bring > the box up to 32 GB. I have not yet installed the second instance of > SQL Server, however all the memory on the box is being utilized. > > Any assistance as to what I am missing in order to limit the memory > usage in SQL Server would be appreciated. > > Installed Memory = 32 GB > Max Server Memory = 14 GB > > Current values: > Physical Memory: Total = 33553396 > Physical Memory: Avaliable = 243700 > Physical Memory: System Cache = 444752 > > From sys.dm_os_performance_counters (where object_name like %memory%) > SQLServer:Memory Manager: Connection Memory (KB) = 2776 > SQLServer:Memory Manager: Granted Workspace Memory (KB) = 0 > SQLServer:Memory Manager: Lock Memory (KB) = 8160 > SQLServer:Memory Manager: Lock Blocks Allocated = 8036 > SQLServer:Memory Manager: Lock Owner Blocks Allocated = 9176 > SQLServer:Memory Manager: Lock Blocks = 27 > SQLServer:Memory Manager: Lock Owner Blocks = 135 > SQLServer:Memory Manager: Maximum Workspace Memory (KB) = 22287680 > SQLServer:Memory Manager: Memory Grants Outstanding = 0 > SQLServer:Memory Manager: Memory Grants Pending = 0 > SQLServer:Memory Manager: Optimizer Memory (KB) = 4272 > SQLServer:Memory Manager: SQL Cache Memory (KB) = 65728 > SQLServer:Memory Manager: Target Server Memory (KB) = 30736248 > SQLServer:Memory Manager: Total Server Memory (KB) = 30736248 > > Why would max workspace memory and target server memory be so high? I > thought total server memory was adjusted by the max server memory > setting. Yes, it has had us stumped for some time how. Here are the results
from xp_msver: Index Name Internal_Value Character_Value 1 ProductName NULL Microsoft SQL Server 2 ProductVersion 589824 9.00.3073.00 3 Language 1033 English (United States) 4 Platform NULL NT AMD64 5 Comments NULL NT AMD64 6 CompanyName NULL Microsoft Corporation 7 FileDescription NULL SQL Server Windows NT - 64 Bit 8 FileVersion NULL 2005.090.3073.00 9 InternalName NULL SQLSERVR 10 LegalCopyright NULL © Microsoft Corp. All rights reserved. 11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation 12 OriginalFilename NULL SQLSERVR.EXE 13 PrivateBuild NULL NULL 14 SpecialBuild 201392128 NULL 15 WindowsVersion 248381957 5.2 (3790) 16 ProcessorCount 4 4 17 ProcessorActiveMask 4 f 18 ProcessorType 8664 NULL 19 PhysicalMemory 32767 32767 (34358677504) 20 Product ID NULL NULL Show quoteHide quote On Dec 2, 11:00 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote: > That is odd. It really should be capping the memory at 14GB. What do you > get from running: > > xp_msver > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau > > "Jason" <jsho***@gmail.com> wrote in message > > news:81aa7c01-1e83-4623-81fc-475fa9807041@t2g2000yqm.googlegroups.com... > The max server values (using sp_configure) show as the following: > > Name: max server memory (MB) > Minimum: 16 > Maximum: 2147483647 > Config Value: 14680064 > Run Value: 14680064 > > Below are the rest of the results, incase any of them may shine other > light. > > name minimum maximum config_value run_value > Ad Hoc Distributed Queries 0 1 0 0 > affinity I/O mask -2147483648 2147483647 0 0 > affinity mask -2147483648 2147483647 0 0 > affinity64 I/O mask -2147483648 2147483647 0 0 > affinity64 mask -2147483648 2147483647 0 0 > Agent XPs 0 1 1 1 > allow updates 0 1 0 0 > awe enabled 0 1 0 0 > blocked process threshold 0 86400 0 0 > c2 audit mode 0 1 0 0 > clr enabled 0 1 0 0 > common criteria compliance enabled 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 > Database Mail XPs 0 1 1 1 > default full-text language 0 2147483647 1033 1033 > default language 0 9999 0 0 > default trace enabled 0 1 1 1 > disallow results from triggers 0 1 0 0 > fill factor (%) 0 100 90 90 > ft crawl bandwidth (max) 0 32767 100 100 > ft crawl bandwidth (min) 0 32767 0 0 > ft notify bandwidth (max) 0 32767 100 100 > ft notify bandwidth (min) 0 32767 0 0 > index create memory (KB) 704 2147483647 0 0 > in-doubt xact resolution 0 2 0 0 > lightweight pooling 0 1 0 0 > locks 5000 2147483647 0 0 > max degree of parallelism 0 64 0 0 > max full-text crawl range 0 256 4 4 > max server memory (MB) 16 2147483647 14680064 14680064 > max text repl size (B) 0 2147483647 65536 65536 > max worker threads 128 32767 0 0 > media retention 0 365 2 2 > min memory per query (KB) 512 2147483647 1024 1024 > min server memory (MB) 0 2147483647 128 128 > nested triggers 0 1 1 1 > network packet size (B) 512 32767 4096 4096 > Ole Automation Procedures 0 1 0 0 > open objects 0 2147483647 0 0 > PH timeout (s) 1 3600 60 60 > precompute rank 0 1 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 admin connections 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 > Replication XPs 0 1 0 0 > scan for startup procs 0 1 1 0 > server trigger recursion 0 1 1 1 > set working set size 0 1 0 0 > show advanced options 0 1 1 1 > SMO and DMO XPs 0 1 1 1 > SQL Mail XPs 0 1 1 1 > transform noise words 0 1 0 0 > two digit year cutoff 1753 9999 2049 2049 > user connections 0 32767 0 0 > user options 0 32767 0 0 > Web Assistant Procedures 0 1 0 0 > xp_cmdshell 0 1 0 0 > > On Dec 2, 10:06 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote: > > > > > Can you confirm the max memory setting by running sp_configure and posting > > here? > > > -- > > Tom > > > ---------------------------------------------------- > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > > SQL Server MVP > > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau > > > "Jason" <jsho***@gmail.com> wrote in message > > >news:41ce1c04-a001-4fe4-8367-214cd719f09e@z1g2000yqn.googlegroups.com... > > Our SQL Server 2005 database appears to be ignoring the max server > > memory setting. The server has 16 GB of memory, and we set the max > > server memory at 14 GB. Ops Manager keeps warning of low memory and > > task manager shows all memory being used. This is a deticated 64-bit > > Server 2003 box, so no non-SQL applications are utilizing resources. > > > Due to a new initative we need to figure this out. For the application > > we are implimented there is the need to set-up a second instance of > > SQL on the server for security seporation. The new application will be > > more resource intensive, however the current instance hosts more > > critical appliations. In order to ensure one does not consume too many > > resources my goal is to set a max for reach instance (also leaving a > > few GB free for the opperating system). > > > As a first step we have installed an additional 16 GB of memory, bring > > the box up to 32 GB. I have not yet installed the second instance of > > SQL Server, however all the memory on the box is being utilized. > > > Any assistance as to what I am missing in order to limit the memory > > usage in SQL Server would be appreciated. > > > Installed Memory = 32 GB > > Max Server Memory = 14 GB > > > Current values: > > Physical Memory: Total = 33553396 > > Physical Memory: Avaliable = 243700 > > Physical Memory: System Cache = 444752 > > > From sys.dm_os_performance_counters (where object_name like %memory%) > > SQLServer:Memory Manager: Connection Memory (KB) = 2776 > > SQLServer:Memory Manager: Granted Workspace Memory (KB) = 0 > > SQLServer:Memory Manager: Lock Memory (KB) = 8160 > > SQLServer:Memory Manager: Lock Blocks Allocated = 8036 > > SQLServer:Memory Manager: Lock Owner Blocks Allocated = 9176 > > SQLServer:Memory Manager: Lock Blocks = 27 > > SQLServer:Memory Manager: Lock Owner Blocks = 135 > > SQLServer:Memory Manager: Maximum Workspace Memory (KB) = 22287680 > > SQLServer:Memory Manager: Memory Grants Outstanding = 0 > > SQLServer:Memory Manager: Memory Grants Pending = 0 > > SQLServer:Memory Manager: Optimizer Memory (KB) = 4272 > > SQLServer:Memory Manager: SQL Cache Memory (KB) = 65728 > > SQLServer:Memory Manager: Target Server Memory (KB) = 30736248 > > SQLServer:Memory Manager: Total Server Memory (KB) = 30736248 > > > Why would max workspace memory and target server memory be so high? I > > thought total server memory was adjusted by the max server memory > > setting.- Hide quoted text - > > - Show quoted text - I'm not sure but perhaps this can help:
http://support.microsoft.com/kb/933564 -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Jason" <jsho***@gmail.com> wrote in message Yes, it has had us stumped for some time how. Here are the resultsnews:6cf811cd-c540-4bef-946c-1795326d64ff@y18g2000yqn.googlegroups.com... from xp_msver: Index Name Internal_Value Character_Value 1 ProductName NULL Microsoft SQL Server 2 ProductVersion 589824 9.00.3073.00 3 Language 1033 English (United States) 4 Platform NULL NT AMD64 5 Comments NULL NT AMD64 6 CompanyName NULL Microsoft Corporation 7 FileDescription NULL SQL Server Windows NT - 64 Bit 8 FileVersion NULL 2005.090.3073.00 9 InternalName NULL SQLSERVR 10 LegalCopyright NULL © Microsoft Corp. All rights reserved. 11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation 12 OriginalFilename NULL SQLSERVR.EXE 13 PrivateBuild NULL NULL 14 SpecialBuild 201392128 NULL 15 WindowsVersion 248381957 5.2 (3790) 16 ProcessorCount 4 4 17 ProcessorActiveMask 4 f 18 ProcessorType 8664 NULL 19 PhysicalMemory 32767 32767 (34358677504) 20 Product ID NULL NULL Show quoteHide quote On Dec 2, 11:00 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote: > That is odd. It really should be capping the memory at 14GB. What do you > get from running: > > xp_msver > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau > > "Jason" <jsho***@gmail.com> wrote in message > > news:81aa7c01-1e83-4623-81fc-475fa9807041@t2g2000yqm.googlegroups.com... > The max server values (using sp_configure) show as the following: > > Name: max server memory (MB) > Minimum: 16 > Maximum: 2147483647 > Config Value: 14680064 > Run Value: 14680064 > > Below are the rest of the results, incase any of them may shine other > light. > > name minimum maximum config_value run_value > Ad Hoc Distributed Queries 0 1 0 0 > affinity I/O mask -2147483648 2147483647 0 0 > affinity mask -2147483648 2147483647 0 0 > affinity64 I/O mask -2147483648 2147483647 0 0 > affinity64 mask -2147483648 2147483647 0 0 > Agent XPs 0 1 1 1 > allow updates 0 1 0 0 > awe enabled 0 1 0 0 > blocked process threshold 0 86400 0 0 > c2 audit mode 0 1 0 0 > clr enabled 0 1 0 0 > common criteria compliance enabled 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 > Database Mail XPs 0 1 1 1 > default full-text language 0 2147483647 1033 1033 > default language 0 9999 0 0 > default trace enabled 0 1 1 1 > disallow results from triggers 0 1 0 0 > fill factor (%) 0 100 90 90 > ft crawl bandwidth (max) 0 32767 100 100 > ft crawl bandwidth (min) 0 32767 0 0 > ft notify bandwidth (max) 0 32767 100 100 > ft notify bandwidth (min) 0 32767 0 0 > index create memory (KB) 704 2147483647 0 0 > in-doubt xact resolution 0 2 0 0 > lightweight pooling 0 1 0 0 > locks 5000 2147483647 0 0 > max degree of parallelism 0 64 0 0 > max full-text crawl range 0 256 4 4 > max server memory (MB) 16 2147483647 14680064 14680064 > max text repl size (B) 0 2147483647 65536 65536 > max worker threads 128 32767 0 0 > media retention 0 365 2 2 > min memory per query (KB) 512 2147483647 1024 1024 > min server memory (MB) 0 2147483647 128 128 > nested triggers 0 1 1 1 > network packet size (B) 512 32767 4096 4096 > Ole Automation Procedures 0 1 0 0 > open objects 0 2147483647 0 0 > PH timeout (s) 1 3600 60 60 > precompute rank 0 1 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 admin connections 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 > Replication XPs 0 1 0 0 > scan for startup procs 0 1 1 0 > server trigger recursion 0 1 1 1 > set working set size 0 1 0 0 > show advanced options 0 1 1 1 > SMO and DMO XPs 0 1 1 1 > SQL Mail XPs 0 1 1 1 > transform noise words 0 1 0 0 > two digit year cutoff 1753 9999 2049 2049 > user connections 0 32767 0 0 > user options 0 32767 0 0 > Web Assistant Procedures 0 1 0 0 > xp_cmdshell 0 1 0 0 > > On Dec 2, 10:06 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote: > > > > > Can you confirm the max memory setting by running sp_configure and > > posting > > here? > > > -- > > Tom > > > ---------------------------------------------------- > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > > SQL Server MVP > > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau > > > "Jason" <jsho***@gmail.com> wrote in message > > >news:41ce1c04-a001-4fe4-8367-214cd719f09e@z1g2000yqn.googlegroups.com... > > Our SQL Server 2005 database appears to be ignoring the max server > > memory setting. The server has 16 GB of memory, and we set the max > > server memory at 14 GB. Ops Manager keeps warning of low memory and > > task manager shows all memory being used. This is a deticated 64-bit > > Server 2003 box, so no non-SQL applications are utilizing resources. > > > Due to a new initative we need to figure this out. For the application > > we are implimented there is the need to set-up a second instance of > > SQL on the server for security seporation. The new application will be > > more resource intensive, however the current instance hosts more > > critical appliations. In order to ensure one does not consume too many > > resources my goal is to set a max for reach instance (also leaving a > > few GB free for the opperating system). > > > As a first step we have installed an additional 16 GB of memory, bring > > the box up to 32 GB. I have not yet installed the second instance of > > SQL Server, however all the memory on the box is being utilized. > > > Any assistance as to what I am missing in order to limit the memory > > usage in SQL Server would be appreciated. > > > Installed Memory = 32 GB > > Max Server Memory = 14 GB > > > Current values: > > Physical Memory: Total = 33553396 > > Physical Memory: Avaliable = 243700 > > Physical Memory: System Cache = 444752 > > > From sys.dm_os_performance_counters (where object_name like %memory%) > > SQLServer:Memory Manager: Connection Memory (KB) = 2776 > > SQLServer:Memory Manager: Granted Workspace Memory (KB) = 0 > > SQLServer:Memory Manager: Lock Memory (KB) = 8160 > > SQLServer:Memory Manager: Lock Blocks Allocated = 8036 > > SQLServer:Memory Manager: Lock Owner Blocks Allocated = 9176 > > SQLServer:Memory Manager: Lock Blocks = 27 > > SQLServer:Memory Manager: Lock Owner Blocks = 135 > > SQLServer:Memory Manager: Maximum Workspace Memory (KB) = 22287680 > > SQLServer:Memory Manager: Memory Grants Outstanding = 0 > > SQLServer:Memory Manager: Memory Grants Pending = 0 > > SQLServer:Memory Manager: Optimizer Memory (KB) = 4272 > > SQLServer:Memory Manager: SQL Cache Memory (KB) = 65728 > > SQLServer:Memory Manager: Target Server Memory (KB) = 30736248 > > SQLServer:Memory Manager: Total Server Memory (KB) = 30736248 > > > Why would max workspace memory and target server memory be so high? I > > thought total server memory was adjusted by the max server memory > > setting.- Hide quoted text - > > - Show quoted text - I think those numbers are in megabytes. So 14,680,064 is 14,680,064
megabytes, or about 14,680,064,000,000 bytes which is 14 terrabytes. And that's why it's trying to use all the memory on your server. Tom "Jason" <jsho***@gmail.com> wrote in message The max server values (using sp_configure) show as the following:news:81aa7c01-1e83-4623-81fc-475fa9807041@t2g2000yqm.googlegroups.com... Name: max server memory (MB) Minimum: 16 Maximum: 2147483647 Config Value: 14680064 Run Value: 14680064 Below are the rest of the results, incase any of them may shine other light. name minimum maximum config_value run_value Ad Hoc Distributed Queries 0 1 0 0 affinity I/O mask -2147483648 2147483647 0 0 affinity mask -2147483648 2147483647 0 0 affinity64 I/O mask -2147483648 2147483647 0 0 affinity64 mask -2147483648 2147483647 0 0 Agent XPs 0 1 1 1 allow updates 0 1 0 0 awe enabled 0 1 0 0 blocked process threshold 0 86400 0 0 c2 audit mode 0 1 0 0 clr enabled 0 1 0 0 common criteria compliance enabled 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 Database Mail XPs 0 1 1 1 default full-text language 0 2147483647 1033 1033 default language 0 9999 0 0 default trace enabled 0 1 1 1 disallow results from triggers 0 1 0 0 fill factor (%) 0 100 90 90 ft crawl bandwidth (max) 0 32767 100 100 ft crawl bandwidth (min) 0 32767 0 0 ft notify bandwidth (max) 0 32767 100 100 ft notify bandwidth (min) 0 32767 0 0 index create memory (KB) 704 2147483647 0 0 in-doubt xact resolution 0 2 0 0 lightweight pooling 0 1 0 0 locks 5000 2147483647 0 0 max degree of parallelism 0 64 0 0 max full-text crawl range 0 256 4 4 max server memory (MB) 16 2147483647 14680064 14680064 max text repl size (B) 0 2147483647 65536 65536 max worker threads 128 32767 0 0 media retention 0 365 2 2 min memory per query (KB) 512 2147483647 1024 1024 min server memory (MB) 0 2147483647 128 128 nested triggers 0 1 1 1 network packet size (B) 512 32767 4096 4096 Ole Automation Procedures 0 1 0 0 open objects 0 2147483647 0 0 PH timeout (s) 1 3600 60 60 precompute rank 0 1 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 admin connections 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 Replication XPs 0 1 0 0 scan for startup procs 0 1 1 0 server trigger recursion 0 1 1 1 set working set size 0 1 0 0 show advanced options 0 1 1 1 SMO and DMO XPs 0 1 1 1 SQL Mail XPs 0 1 1 1 transform noise words 0 1 0 0 two digit year cutoff 1753 9999 2049 2049 user connections 0 32767 0 0 user options 0 32767 0 0 Web Assistant Procedures 0 1 0 0 xp_cmdshell 0 1 0 0 Show quoteHide quote On Dec 2, 10:06 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote: > Can you confirm the max memory setting by running sp_configure and posting > here? > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau > > "Jason" <jsho***@gmail.com> wrote in message > > news:41ce1c04-a001-4fe4-8367-214cd719f09e@z1g2000yqn.googlegroups.com... > Our SQL Server 2005 database appears to be ignoring the max server > memory setting. The server has 16 GB of memory, and we set the max > server memory at 14 GB. Ops Manager keeps warning of low memory and > task manager shows all memory being used. This is a deticated 64-bit > Server 2003 box, so no non-SQL applications are utilizing resources. > > Due to a new initative we need to figure this out. For the application > we are implimented there is the need to set-up a second instance of > SQL on the server for security seporation. The new application will be > more resource intensive, however the current instance hosts more > critical appliations. In order to ensure one does not consume too many > resources my goal is to set a max for reach instance (also leaving a > few GB free for the opperating system). > > As a first step we have installed an additional 16 GB of memory, bring > the box up to 32 GB. I have not yet installed the second instance of > SQL Server, however all the memory on the box is being utilized. > > Any assistance as to what I am missing in order to limit the memory > usage in SQL Server would be appreciated. > > Installed Memory = 32 GB > Max Server Memory = 14 GB > > Current values: > Physical Memory: Total = 33553396 > Physical Memory: Avaliable = 243700 > Physical Memory: System Cache = 444752 > > From sys.dm_os_performance_counters (where object_name like %memory%) > SQLServer:Memory Manager: Connection Memory (KB) = 2776 > SQLServer:Memory Manager: Granted Workspace Memory (KB) = 0 > SQLServer:Memory Manager: Lock Memory (KB) = 8160 > SQLServer:Memory Manager: Lock Blocks Allocated = 8036 > SQLServer:Memory Manager: Lock Owner Blocks Allocated = 9176 > SQLServer:Memory Manager: Lock Blocks = 27 > SQLServer:Memory Manager: Lock Owner Blocks = 135 > SQLServer:Memory Manager: Maximum Workspace Memory (KB) = 22287680 > SQLServer:Memory Manager: Memory Grants Outstanding = 0 > SQLServer:Memory Manager: Memory Grants Pending = 0 > SQLServer:Memory Manager: Optimizer Memory (KB) = 4272 > SQLServer:Memory Manager: SQL Cache Memory (KB) = 65728 > SQLServer:Memory Manager: Target Server Memory (KB) = 30736248 > SQLServer:Memory Manager: Total Server Memory (KB) = 30736248 > > Why would max workspace memory and target server memory be so high? I > thought total server memory was adjusted by the max server memory > setting. Good catch!
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message I think those numbers are in megabytes. So 14,680,064 is 14,680,064news:%233M9GLKVJHA.6116@TK2MSFTNGP04.phx.gbl... megabytes, or about 14,680,064,000,000 bytes which is 14 terrabytes. And that's why it's trying to use all the memory on your server. Tom "Jason" <jsho***@gmail.com> wrote in message The max server values (using sp_configure) show as the following:news:81aa7c01-1e83-4623-81fc-475fa9807041@t2g2000yqm.googlegroups.com... Name: max server memory (MB) Minimum: 16 Maximum: 2147483647 Config Value: 14680064 Run Value: 14680064 Below are the rest of the results, incase any of them may shine other light. name minimum maximum config_value run_value Ad Hoc Distributed Queries 0 1 0 0 affinity I/O mask -2147483648 2147483647 0 0 affinity mask -2147483648 2147483647 0 0 affinity64 I/O mask -2147483648 2147483647 0 0 affinity64 mask -2147483648 2147483647 0 0 Agent XPs 0 1 1 1 allow updates 0 1 0 0 awe enabled 0 1 0 0 blocked process threshold 0 86400 0 0 c2 audit mode 0 1 0 0 clr enabled 0 1 0 0 common criteria compliance enabled 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 Database Mail XPs 0 1 1 1 default full-text language 0 2147483647 1033 1033 default language 0 9999 0 0 default trace enabled 0 1 1 1 disallow results from triggers 0 1 0 0 fill factor (%) 0 100 90 90 ft crawl bandwidth (max) 0 32767 100 100 ft crawl bandwidth (min) 0 32767 0 0 ft notify bandwidth (max) 0 32767 100 100 ft notify bandwidth (min) 0 32767 0 0 index create memory (KB) 704 2147483647 0 0 in-doubt xact resolution 0 2 0 0 lightweight pooling 0 1 0 0 locks 5000 2147483647 0 0 max degree of parallelism 0 64 0 0 max full-text crawl range 0 256 4 4 max server memory (MB) 16 2147483647 14680064 14680064 max text repl size (B) 0 2147483647 65536 65536 max worker threads 128 32767 0 0 media retention 0 365 2 2 min memory per query (KB) 512 2147483647 1024 1024 min server memory (MB) 0 2147483647 128 128 nested triggers 0 1 1 1 network packet size (B) 512 32767 4096 4096 Ole Automation Procedures 0 1 0 0 open objects 0 2147483647 0 0 PH timeout (s) 1 3600 60 60 precompute rank 0 1 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 admin connections 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 Replication XPs 0 1 0 0 scan for startup procs 0 1 1 0 server trigger recursion 0 1 1 1 set working set size 0 1 0 0 show advanced options 0 1 1 1 SMO and DMO XPs 0 1 1 1 SQL Mail XPs 0 1 1 1 transform noise words 0 1 0 0 two digit year cutoff 1753 9999 2049 2049 user connections 0 32767 0 0 user options 0 32767 0 0 Web Assistant Procedures 0 1 0 0 xp_cmdshell 0 1 0 0 Show quoteHide quote On Dec 2, 10:06 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote: > Can you confirm the max memory setting by running sp_configure and posting > here? > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau > > "Jason" <jsho***@gmail.com> wrote in message > > news:41ce1c04-a001-4fe4-8367-214cd719f09e@z1g2000yqn.googlegroups.com... > Our SQL Server 2005 database appears to be ignoring the max server > memory setting. The server has 16 GB of memory, and we set the max > server memory at 14 GB. Ops Manager keeps warning of low memory and > task manager shows all memory being used. This is a deticated 64-bit > Server 2003 box, so no non-SQL applications are utilizing resources. > > Due to a new initative we need to figure this out. For the application > we are implimented there is the need to set-up a second instance of > SQL on the server for security seporation. The new application will be > more resource intensive, however the current instance hosts more > critical appliations. In order to ensure one does not consume too many > resources my goal is to set a max for reach instance (also leaving a > few GB free for the opperating system). > > As a first step we have installed an additional 16 GB of memory, bring > the box up to 32 GB. I have not yet installed the second instance of > SQL Server, however all the memory on the box is being utilized. > > Any assistance as to what I am missing in order to limit the memory > usage in SQL Server would be appreciated. > > Installed Memory = 32 GB > Max Server Memory = 14 GB > > Current values: > Physical Memory: Total = 33553396 > Physical Memory: Avaliable = 243700 > Physical Memory: System Cache = 444752 > > From sys.dm_os_performance_counters (where object_name like %memory%) > SQLServer:Memory Manager: Connection Memory (KB) = 2776 > SQLServer:Memory Manager: Granted Workspace Memory (KB) = 0 > SQLServer:Memory Manager: Lock Memory (KB) = 8160 > SQLServer:Memory Manager: Lock Blocks Allocated = 8036 > SQLServer:Memory Manager: Lock Owner Blocks Allocated = 9176 > SQLServer:Memory Manager: Lock Blocks = 27 > SQLServer:Memory Manager: Lock Owner Blocks = 135 > SQLServer:Memory Manager: Maximum Workspace Memory (KB) = 22287680 > SQLServer:Memory Manager: Memory Grants Outstanding = 0 > SQLServer:Memory Manager: Memory Grants Pending = 0 > SQLServer:Memory Manager: Optimizer Memory (KB) = 4272 > SQLServer:Memory Manager: SQL Cache Memory (KB) = 65728 > SQLServer:Memory Manager: Target Server Memory (KB) = 30736248 > SQLServer:Memory Manager: Total Server Memory (KB) = 30736248 > > Why would max workspace memory and target server memory be so high? I > thought total server memory was adjusted by the max server memory > setting. Had a similar issue awhile back:
http://kevin3nf.blogspot.com/2008/01/count-your-zeros.html -- Show quoteHide quoteKevin3NF SQL Server dude You want fries with that? http://kevin3nf.blogspot.com/ I only check the newsgroups during work hours, M-F. Hit my blog and the contact links if necessary...I may be available. Twitter: Kevin3NF "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:uETJ7NKVJHA.2080@TK2MSFTNGP06.phx.gbl... > Good catch! > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message > news:%233M9GLKVJHA.6116@TK2MSFTNGP04.phx.gbl... > I think those numbers are in megabytes. So 14,680,064 is 14,680,064 > megabytes, or about 14,680,064,000,000 bytes which is 14 terrabytes. And > that's why it's trying to use all the memory on your server. > > Tom > > "Jason" <jsho***@gmail.com> wrote in message > news:81aa7c01-1e83-4623-81fc-475fa9807041@t2g2000yqm.googlegroups.com... > The max server values (using sp_configure) show as the following: > > Name: max server memory (MB) > Minimum: 16 > Maximum: 2147483647 > Config Value: 14680064 > Run Value: 14680064 > > Below are the rest of the results, incase any of them may shine other > light. > > name minimum maximum config_value run_value > Ad Hoc Distributed Queries 0 1 0 0 > affinity I/O mask -2147483648 2147483647 0 0 > affinity mask -2147483648 2147483647 0 0 > affinity64 I/O mask -2147483648 2147483647 0 0 > affinity64 mask -2147483648 2147483647 0 0 > Agent XPs 0 1 1 1 > allow updates 0 1 0 0 > awe enabled 0 1 0 0 > blocked process threshold 0 86400 0 0 > c2 audit mode 0 1 0 0 > clr enabled 0 1 0 0 > common criteria compliance enabled 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 > Database Mail XPs 0 1 1 1 > default full-text language 0 2147483647 1033 1033 > default language 0 9999 0 0 > default trace enabled 0 1 1 1 > disallow results from triggers 0 1 0 0 > fill factor (%) 0 100 90 90 > ft crawl bandwidth (max) 0 32767 100 100 > ft crawl bandwidth (min) 0 32767 0 0 > ft notify bandwidth (max) 0 32767 100 100 > ft notify bandwidth (min) 0 32767 0 0 > index create memory (KB) 704 2147483647 0 0 > in-doubt xact resolution 0 2 0 0 > lightweight pooling 0 1 0 0 > locks 5000 2147483647 0 0 > max degree of parallelism 0 64 0 0 > max full-text crawl range 0 256 4 4 > max server memory (MB) 16 2147483647 14680064 14680064 > max text repl size (B) 0 2147483647 65536 65536 > max worker threads 128 32767 0 0 > media retention 0 365 2 2 > min memory per query (KB) 512 2147483647 1024 1024 > min server memory (MB) 0 2147483647 128 128 > nested triggers 0 1 1 1 > network packet size (B) 512 32767 4096 4096 > Ole Automation Procedures 0 1 0 0 > open objects 0 2147483647 0 0 > PH timeout (s) 1 3600 60 60 > precompute rank 0 1 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 admin connections 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 > Replication XPs 0 1 0 0 > scan for startup procs 0 1 1 0 > server trigger recursion 0 1 1 1 > set working set size 0 1 0 0 > show advanced options 0 1 1 1 > SMO and DMO XPs 0 1 1 1 > SQL Mail XPs 0 1 1 1 > transform noise words 0 1 0 0 > two digit year cutoff 1753 9999 2049 2049 > user connections 0 32767 0 0 > user options 0 32767 0 0 > Web Assistant Procedures 0 1 0 0 > xp_cmdshell 0 1 0 0 > > > > > > On Dec 2, 10:06 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote: >> Can you confirm the max memory setting by running sp_configure and >> posting >> here? >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS >> SQL Server MVP >> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau >> >> "Jason" <jsho***@gmail.com> wrote in message >> >> news:41ce1c04-a001-4fe4-8367-214cd719f09e@z1g2000yqn.googlegroups.com... >> Our SQL Server 2005 database appears to be ignoring the max server >> memory setting. The server has 16 GB of memory, and we set the max >> server memory at 14 GB. Ops Manager keeps warning of low memory and >> task manager shows all memory being used. This is a deticated 64-bit >> Server 2003 box, so no non-SQL applications are utilizing resources. >> >> Due to a new initative we need to figure this out. For the application >> we are implimented there is the need to set-up a second instance of >> SQL on the server for security seporation. The new application will be >> more resource intensive, however the current instance hosts more >> critical appliations. In order to ensure one does not consume too many >> resources my goal is to set a max for reach instance (also leaving a >> few GB free for the opperating system). >> >> As a first step we have installed an additional 16 GB of memory, bring >> the box up to 32 GB. I have not yet installed the second instance of >> SQL Server, however all the memory on the box is being utilized. >> >> Any assistance as to what I am missing in order to limit the memory >> usage in SQL Server would be appreciated. >> >> Installed Memory = 32 GB >> Max Server Memory = 14 GB >> >> Current values: >> Physical Memory: Total = 33553396 >> Physical Memory: Avaliable = 243700 >> Physical Memory: System Cache = 444752 >> >> From sys.dm_os_performance_counters (where object_name like %memory%) >> SQLServer:Memory Manager: Connection Memory (KB) = 2776 >> SQLServer:Memory Manager: Granted Workspace Memory (KB) = 0 >> SQLServer:Memory Manager: Lock Memory (KB) = 8160 >> SQLServer:Memory Manager: Lock Blocks Allocated = 8036 >> SQLServer:Memory Manager: Lock Owner Blocks Allocated = 9176 >> SQLServer:Memory Manager: Lock Blocks = 27 >> SQLServer:Memory Manager: Lock Owner Blocks = 135 >> SQLServer:Memory Manager: Maximum Workspace Memory (KB) = 22287680 >> SQLServer:Memory Manager: Memory Grants Outstanding = 0 >> SQLServer:Memory Manager: Memory Grants Pending = 0 >> SQLServer:Memory Manager: Optimizer Memory (KB) = 4272 >> SQLServer:Memory Manager: SQL Cache Memory (KB) = 65728 >> SQLServer:Memory Manager: Target Server Memory (KB) = 30736248 >> SQLServer:Memory Manager: Total Server Memory (KB) = 30736248 >> >> Why would max workspace memory and target server memory be so high? I >> thought total server memory was adjusted by the max server memory >> setting. > > Oh gosh, now that is embarrassing. I have been looking at this for so
long and not seeing what was wrong. Thank you so much! Show quoteHide quote On Dec 2, 12:28 pm, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote: > Good catch! > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau > > "Tom Cooper" <tomcoo...@comcast.no.spam.please.net> wrote in message > > news:%233M9GLKVJHA.6116@TK2MSFTNGP04.phx.gbl... > I think those numbers are in megabytes. So 14,680,064 is 14,680,064 > megabytes, or about 14,680,064,000,000 bytes which is 14 terrabytes. And > that's why it's trying to use all the memory on your server. > > Tom > > "Jason" <jsho***@gmail.com> wrote in message > > news:81aa7c01-1e83-4623-81fc-475fa9807041@t2g2000yqm.googlegroups.com... > The max server values (using sp_configure) show as the following: > > Name: max server memory (MB) > Minimum: 16 > Maximum: 2147483647 > Config Value: 14680064 > Run Value: 14680064 > > Below are the rest of the results, incase any of them may shine other > light. > > name minimum maximum config_value run_value > Ad Hoc Distributed Queries 0 1 0 0 > affinity I/O mask -2147483648 2147483647 0 0 > affinity mask -2147483648 2147483647 0 0 > affinity64 I/O mask -2147483648 2147483647 0 0 > affinity64 mask -2147483648 2147483647 0 0 > Agent XPs 0 1 1 1 > allow updates 0 1 0 0 > awe enabled 0 1 0 0 > blocked process threshold 0 86400 0 0 > c2 audit mode 0 1 0 0 > clr enabled 0 1 0 0 > common criteria compliance enabled 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 > Database Mail XPs 0 1 1 1 > default full-text language 0 2147483647 1033 1033 > default language 0 9999 0 0 > default trace enabled 0 1 1 1 > disallow results from triggers 0 1 0 0 > fill factor (%) 0 100 90 90 > ft crawl bandwidth (max) 0 32767 100 100 > ft crawl bandwidth (min) 0 32767 0 0 > ft notify bandwidth (max) 0 32767 100 100 > ft notify bandwidth (min) 0 32767 0 0 > index create memory (KB) 704 2147483647 0 0 > in-doubt xact resolution 0 2 0 0 > lightweight pooling 0 1 0 0 > locks 5000 2147483647 0 0 > max degree of parallelism 0 64 0 0 > max full-text crawl range 0 256 4 4 > max server memory (MB) 16 2147483647 14680064 14680064 > max text repl size (B) 0 2147483647 65536 65536 > max worker threads 128 32767 0 0 > media retention 0 365 2 2 > min memory per query (KB) 512 2147483647 1024 1024 > min server memory (MB) 0 2147483647 128 128 > nested triggers 0 1 1 1 > network packet size (B) 512 32767 4096 4096 > Ole Automation Procedures 0 1 0 0 > open objects 0 2147483647 0 0 > PH timeout (s) 1 3600 60 60 > precompute rank 0 1 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 admin connections 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 > Replication XPs 0 1 0 0 > scan for startup procs 0 1 1 0 > server trigger recursion 0 1 1 1 > set working set size 0 1 0 0 > show advanced options 0 1 1 1 > SMO and DMO XPs 0 1 1 1 > SQL Mail XPs 0 1 1 1 > transform noise words 0 1 0 0 > two digit year cutoff 1753 9999 2049 2049 > user connections 0 32767 0 0 > user options 0 32767 0 0 > Web Assistant Procedures 0 1 0 0 > xp_cmdshell 0 1 0 0 > > On Dec 2, 10:06 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote: > > > > > Can you confirm the max memory setting by running sp_configure and posting > > here? > > > -- > > Tom > > > ---------------------------------------------------- > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > > SQL Server MVP > > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau > > > "Jason" <jsho***@gmail.com> wrote in message > > >news:41ce1c04-a001-4fe4-8367-214cd719f09e@z1g2000yqn.googlegroups.com... > > Our SQL Server 2005 database appears to be ignoring the max server > > memory setting. The server has 16 GB of memory, and we set the max > > server memory at 14 GB. Ops Manager keeps warning of low memory and > > task manager shows all memory being used. This is a deticated 64-bit > > Server 2003 box, so no non-SQL applications are utilizing resources. > > > Due to a new initative we need to figure this out. For the application > > we are implimented there is the need to set-up a second instance of > > SQL on the server for security seporation. The new application will be > > more resource intensive, however the current instance hosts more > > critical appliations. In order to ensure one does not consume too many > > resources my goal is to set a max for reach instance (also leaving a > > few GB free for the opperating system). > > > As a first step we have installed an additional 16 GB of memory, bring > > the box up to 32 GB. I have not yet installed the second instance of > > SQL Server, however all the memory on the box is being utilized. > > > Any assistance as to what I am missing in order to limit the memory > > usage in SQL Server would be appreciated. > > > Installed Memory = 32 GB > > Max Server Memory = 14 GB > > > Current values: > > Physical Memory: Total = 33553396 > > Physical Memory: Avaliable = 243700 > > Physical Memory: System Cache = 444752 > > > From sys.dm_os_performance_counters (where object_name like %memory%) > > SQLServer:Memory Manager: Connection Memory (KB) = 2776 > > SQLServer:Memory Manager: Granted Workspace Memory (KB) = 0 > > SQLServer:Memory Manager: Lock Memory (KB) = 8160 > > SQLServer:Memory Manager: Lock Blocks Allocated = 8036 > > SQLServer:Memory Manager: Lock Owner Blocks Allocated = 9176 > > SQLServer:Memory Manager: Lock Blocks = 27 > > SQLServer:Memory Manager: Lock Owner Blocks = 135 > > SQLServer:Memory Manager: Maximum Workspace Memory (KB) = 22287680 > > SQLServer:Memory Manager: Memory Grants Outstanding = 0 > > SQLServer:Memory Manager: Memory Grants Pending = 0 > > SQLServer:Memory Manager: Optimizer Memory (KB) = 4272 > > SQLServer:Memory Manager: SQL Cache Memory (KB) = 65728 > > SQLServer:Memory Manager: Target Server Memory (KB) = 30736248 > > SQLServer:Memory Manager: Total Server Memory (KB) = 30736248 > > > Why would max workspace memory and target server memory be so high? I > > thought total server memory was adjusted by the max server memory > > setting.- Hide quoted text - > > - Show quoted text -  
Other interesting topics
Re-attaching database
Migrating SQL 2000 to SQL 2005, any risk I change db collation too? Alter Index On Database Rebuild (SQL Server 2005) Consolidate datafiles to one drive SQL GROUP BY CLAUSE SQL 2k5-32bit >SQL 2k5-64bit Taking database offline taking long time Bug or Bad Practice on my part? SQL Server 2005 Alter Index All On DB Rebuild Re: Odd Identity Behavior in Enterprise Manager |
|||||||||||||||||||||||
