|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Dynamic memory setting
I have inherited a SQL 2K server that is having some severe performance
problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in the boot.ini. No other applications are running on the server. The available memory is showing 1.2GB. The previous administrator has the server set with a fixed memory setting of 8120MB. My question, is there a benefit that I am unaware of in having the server set with fixed memory versus dynamic? Is not dynamic memory a better configuration when SQL Server is not competing with other applications? If you are using AWE and you have 8GB you should set the MAX memory to no
more than 7GB since AWE is not dynamic in SQL2000. -- Andrew J. Kelly SQL MVP "Robert R via SQLMonster.com" <u3288@uwe> wrote in message news:5ad0d04f5b9b2@uwe...Show quoteHide quote >I have inherited a SQL 2K server that is having some severe performance > problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting > in > the boot.ini. No other applications are running on the server. The > available > memory is showing 1.2GB. > > The previous administrator has the server set with a fixed memory setting > of > 8120MB. > > My question, is there a benefit that I am unaware of in having the server > set > with fixed memory versus dynamic? Is not dynamic memory a better > configuration when SQL Server is not competing with other applications? > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1 OK. Thanks, for making me aware of that, but what about the question(s) I
posed: "Is there a benefit that I am unaware of in having the server set with fixed memory versus dynamic? Is not dynamic memory a better configuration when SQL Server is not competing with other applications?" Andrew J. Kelly wrote: >If you are using AWE and you have 8GB you should set the MAX memory to no >more than 7GB since AWE is not dynamic in SQL2000. > >>I have inherited a SQL 2K server that is having some severe performance >> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting >[quoted text clipped - 11 lines] >> with fixed memory versus dynamic? Is not dynamic memory a better >> configuration when SQL Server is not competing with other applications? I thought that when I stated the memory was not dynamic it made this
irrelevant. It's rare that the Fixed memory option is used and I would uncheck this but the memory in AWE is always static in SQL2000. -- Andrew J. Kelly SQL MVP "Robert R via SQLMonster.com" <u3288@uwe> wrote in message news:5ad1454f51cda@uwe...Show quoteHide quote > OK. Thanks, for making me aware of that, but what about the question(s) I > posed: > > "Is there a benefit that I am unaware of in having the server set > with fixed memory versus dynamic? Is not dynamic memory a better > configuration when SQL Server is not competing with other applications?" > > > Andrew J. Kelly wrote: >>If you are using AWE and you have 8GB you should set the MAX memory to no >>more than 7GB since AWE is not dynamic in SQL2000. >> >>>I have inherited a SQL 2K server that is having some severe performance >>> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting >>[quoted text clipped - 11 lines] >>> with fixed memory versus dynamic? Is not dynamic memory a better >>> configuration when SQL Server is not competing with other applications? > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1 Ordinarily, allowing SQL Server to dynamically manage the memory
available to it is the better (more efficient) option, however your options are more limited with AWE memory. SQL Server 2000 does not swap pages out of memory when using AWE memory (it locks pages in memory), so you cannot use the dynamic memory management in SQL Server 2000 when you allow SQL Server address space over 4GB - you must specify a max server memory size (the pages in memory committed by SQL Server won't be released until the SQL Server service shuts down). SQL 2005 EE on Win 2003 takes advantage of changes to the underlying OS and allows dynamic memory management with AWE, but with SQL 2000 (no matter what OS) you're stuck with fixed AWE memory unfortunately. So figure out how much memory is needed for the non-SQL stuff on the box and allocate the rest to SQL Server. As Andrew suggested, a dedicated SQL server with 8GB of physical RAM probably doesn't need more than about 1GB for the OS et al. (monitoring agents, etc.), so you should be OK allocating about 7GB to SQL Server. Show quoteHide quote >I have inherited a SQL 2K server that is having some severe performance >problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in >the boot.ini. No other applications are running on the server. The available >memory is showing 1.2GB. > >The previous administrator has the server set with a fixed memory setting of >8120MB. > >My question, is there a benefit that I am unaware of in having the server set >with fixed memory versus dynamic? Is not dynamic memory a better >configuration when SQL Server is not competing with other applications? > > > Hello Robert
I administer myself a windows 2000 / SQL 2000 server with 8 GB of memory ( this erver has superior performance ) if you use PAE icw AWE enabled you should first calculate how manny memory your server needs in a typicall load ( i use 512 MB for my windows 2000 advanced server ) now fix the rest of the memory size in the AWE setting for SQL use if you start your task manager you should see that the 7,5 gigabytes is constantly claimed by SQL server > My question, is there a benefit that I am unaware of in having the server In my opinion it is the other way around ,,,, however with PAE and AWE this > set > with fixed memory versus dynamic? Is not dynamic memory a better > configuration when SQL Server is not competing with other applications? is simply not an option regards Michel Posseth [MCP] "Robert R via SQLMonster.com" <u3288@uwe> wrote in message news:5ad0d04f5b9b2@uwe...Show quoteHide quote >I have inherited a SQL 2K server that is having some severe performance > problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting > in > the boot.ini. No other applications are running on the server. The > available > memory is showing 1.2GB. > > The previous administrator has the server set with a fixed memory setting > of > 8120MB. > > My question, is there a benefit that I am unaware of in having the server > set > with fixed memory versus dynamic? Is not dynamic memory a better > configuration when SQL Server is not competing with other applications? > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1 Hello, Robert!
To decide if you will use fixe the memory to SQL Server, you need to know your environment. The SQL Server works better if it has memory enough to process the information and transaction. If your SQL Server has any other application installed you should not set fixed memory, because other process must require more memory and the server will not have memory enough to supress this request. So, you should analyze it before and after that define if you will use fixed memory or not. I have problems too with hyper-memory allocation to SQL Server, or better, I had define more memory that it needed. I recomend for you, that you set a min memory and a maximum memory before, monitor its performance and if you have many memory free, cache hit ratio below 90% and other important counters to determine if SQL Server is using memory efficiently. If you need a help, please send me a message. Ok? Take care, when you define fixed memory. You should monitor in System Monitor (W2k ou W2k3) how many Pages/sec your server is paging because it. Ok? Bye Juliano Horta Robert R wrote: >I have inherited a SQL 2K server that is having some severe performance >problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in >the boot.ini. No other applications are running on the server. The available >memory is showing 1.2GB. > >The previous administrator has the server set with a fixed memory setting of >8120MB. > >My question, is there a benefit that I am unaware of in having the server set >with fixed memory versus dynamic? Is not dynamic memory a better >configuration when SQL Server is not competing with other applications? I think you missed the bit where Robert said he has 8GB in his box and
therefore must use AWE (and therefore, by implication, cannot use dynamic memory management). Show quoteHide quote >Hello, Robert! > >To decide if you will use fixe the memory to SQL Server, you need to know >your environment. The SQL Server works better if it has memory enough to >process the information and transaction. If your SQL Server has any other >application installed you should not set fixed memory, because other process >must require more memory and the server will not have memory enough to >supress this request. >So, you should analyze it before and after that define if you will use fixed >memory or not. >I have problems too with hyper-memory allocation to SQL Server, or better, I >had define more memory that it needed. >I recomend for you, that you set a min memory and a maximum memory before, >monitor its performance and if you have many memory free, cache hit ratio >below 90% and other important counters to determine if SQL Server is using >memory efficiently. >If you need a help, please send me a message. Ok? >Take care, when you define fixed memory. You should monitor in System Monitor >(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok? > >Bye > >Juliano Horta > > > >Robert R wrote: > > >>I have inherited a SQL 2K server that is having some severe performance >>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in >>the boot.ini. No other applications are running on the server. The available >>memory is showing 1.2GB. >> >>The previous administrator has the server set with a fixed memory setting of >>8120MB. >> >>My question, is there a benefit that I am unaware of in having the server set >>with fixed memory versus dynamic? Is not dynamic memory a better >>configuration when SQL Server is not competing with other applications? >> >> > > > Hello! Mike.
Why, He cannot use dynamic memory management? What's the relationship between AWE enabled and memory options? Thanks Mike Hodgson wrote: Show quoteHide quote >I think you missed the bit where Robert said he has 8GB in his box and >therefore must use AWE (and therefore, by implication, cannot use >dynamic memory management). > >-- >*mike hodgson* >http://sqlnerd.blogspot.com > >>Hello, Robert! >> >[quoted text clipped - 36 lines] >> >> When using AWE memory SQL Server locks pages in memory. It will not
swap pages out to disk if another application makes a request for memory and there is not enough available at the time (like it normally does when it is using dynamic memory management). Using min & max server memory is fine but once SQL marks the memory page as committed, it stays that way until SQL Server shuts down (ie. it will not be released). Dynamic memory management (at least in my book) also involves releasing memory, when needed, to maintain a minimum free memory threshold (off the top of my head I think the default threshold is 10MB). Perhaps I misunderstood your post but I thought you were advocating setting a max & min level and letting SQL Server allocate and release memory as needed. Show quoteHide quote >Hello! Mike. > >Why, He cannot use dynamic memory management? What's the relationship between >AWE enabled and memory options? > > >Thanks > >Mike Hodgson wrote: > > >>I think you missed the bit where Robert said he has 8GB in his box and >>therefore must use AWE (and therefore, by implication, cannot use >>dynamic memory management). >> >>-- >>*mike hodgson* >>http://sqlnerd.blogspot.com >> >> >> >>>Hello, Robert! >>> >>> >>> >>[quoted text clipped - 36 lines] >> >> >>> >>> > > >  |
|||||||||||||||||||||||
Other interesting topics