|
|
You're in the
sql server new users
group:config question
sql server new users:
hello,
we have a dual xeon box with 4 Gb of RAM running windows 2003 sp1 and sql 2000 sp4. The database is 200 Mb and will probably max out at 400 Mb. Is there any way to keep the entire database in memory? Any issues with doing this? thanks
As long as SQL Server has enough memory devoted to it at all times the data
should stay in cache after the first time you access it. It only gets pushed out of cache if it needs the memory space for other data. With 4GB on the machine this should not be an issue at all. Of coarse each time you restart the server or SQL Server the cache gets flushed. That's a lot of machine for a 200MB db so it leads me to believe you have other apps running on the box as well. If so and they use up a LOT of memory you can set SQL Server to a fixed amount of memory or a MIN size that will ensure SQL Server keeps x many MBs at all times. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Troy" <manuel@pushit.com> wrote in message news:OfXx6sIOGHA.428@tk2msftngp13.phx.gbl... > hello, > > we have a dual xeon box with 4 Gb of RAM running windows 2003 sp1 and sql > 2000 sp4. The database is 200 Mb and will probably max out at 400 Mb. Is > there any way to keep the entire database in memory? Any issues with doing > this? > > thanks > >
thanks for responding. The box is dedicated SQL server and nothing else. How
do I keep the db in memory? [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:eUd030JOGHA.3276@TK2MSFTNGP09.phx.gbl... > As long as SQL Server has enough memory devoted to it at all times the > data should stay in cache after the first time you access it. It only gets > pushed out of cache if it needs the memory space for other data. With 4GB > on the machine this should not be an issue at all. Of coarse each time > you restart the server or SQL Server the cache gets flushed. That's a > lot of machine for a 200MB db so it leads me to believe you have other > apps running on the box as well. If so and they use up a LOT of memory > you can set SQL Server to a fixed amount of memory or a MIN size that will > ensure SQL Server keeps x many MBs at all times. > > -- > Andrew J. Kelly SQL MVP > > > "Troy" <manuel@pushit.com> wrote in message > news:OfXx6sIOGHA.428@tk2msftngp13.phx.gbl... >> hello, >> >> we have a dual xeon box with 4 Gb of RAM running windows 2003 sp1 and sql >> 2000 sp4. The database is 200 Mb and will probably max out at 400 Mb. Is >> there any way to keep the entire database in memory? Any issues with >> doing this? >> >> thanks >> >> > >
Every page in the database that SQL server has accessed will stay in
memory until it needs to be swapped out due to lack of memory (unlikely in your case - a 4GB box with a 200MB database and nothing else vying for memory) or the SQL Server service is stopped (like when you reboot the server for example). You cannot pre-load the entire database into RAM, but every time you access a page it'll stay in RAM (ie. cache aka. the buffer pool). You don't need to do anything special, that's just the default behaviour of SQL Server. -- *mike hodgson* http://sqlnerd.blogspot.com [quoted text, click to view] Troy wrote:
>thanks for responding. The box is dedicated SQL server and nothing else. How >do I keep the db in memory? > > > >"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >news:eUd030JOGHA.3276@TK2MSFTNGP09.phx.gbl... > > >>As long as SQL Server has enough memory devoted to it at all times the >>data should stay in cache after the first time you access it. It only gets >>pushed out of cache if it needs the memory space for other data. With 4GB >>on the machine this should not be an issue at all. Of coarse each time >>you restart the server or SQL Server the cache gets flushed. That's a >>lot of machine for a 200MB db so it leads me to believe you have other >>apps running on the box as well. If so and they use up a LOT of memory >>you can set SQL Server to a fixed amount of memory or a MIN size that will >>ensure SQL Server keeps x many MBs at all times. >> >>-- >>Andrew J. Kelly SQL MVP >> >> >>"Troy" <manuel@pushit.com> wrote in message >>news:OfXx6sIOGHA.428@tk2msftngp13.phx.gbl... >> >> >>>hello, >>> >>>we have a dual xeon box with 4 Gb of RAM running windows 2003 sp1 and sql >>>2000 sp4. The database is 200 Mb and will probably max out at 400 Mb. Is >>>there any way to keep the entire database in memory? Any issues with >>>doing this? >>> >>>thanks >>> >>> >>> >>> >> >> > > >
My first sentence answered that, cache is memory.
-- Andrew J. Kelly SQL MVP [quoted text, click to view] "Troy" <manuel@pushit.com> wrote in message news:%23e7p0xQOGHA.3856@TK2MSFTNGP12.phx.gbl... > thanks for responding. The box is dedicated SQL server and nothing else. > How do I keep the db in memory? > > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:eUd030JOGHA.3276@TK2MSFTNGP09.phx.gbl... >> As long as SQL Server has enough memory devoted to it at all times the >> data should stay in cache after the first time you access it. It only >> gets pushed out of cache if it needs the memory space for other data. >> With 4GB on the machine this should not be an issue at all. Of coarse >> each time you restart the server or SQL Server the cache gets flushed. >> That's a lot of machine for a 200MB db so it leads me to believe you have >> other apps running on the box as well. If so and they use up a LOT of >> memory you can set SQL Server to a fixed amount of memory or a MIN size >> that will ensure SQL Server keeps x many MBs at all times. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Troy" <manuel@pushit.com> wrote in message >> news:OfXx6sIOGHA.428@tk2msftngp13.phx.gbl... >>> hello, >>> >>> we have a dual xeon box with 4 Gb of RAM running windows 2003 sp1 and >>> sql 2000 sp4. The database is 200 Mb and will probably max out at 400 >>> Mb. Is there any way to keep the entire database in memory? Any issues >>> with doing this? >>> >>> thanks >>> >>> >> >> > >
what about dbcc pintable?
[quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:%23NobMAUOGHA.2604@TK2MSFTNGP09.phx.gbl... > My first sentence answered that, cache is memory. > > > -- > Andrew J. Kelly SQL MVP > > > "Troy" <manuel@pushit.com> wrote in message > news:%23e7p0xQOGHA.3856@TK2MSFTNGP12.phx.gbl... >> thanks for responding. The box is dedicated SQL server and nothing else. >> How do I keep the db in memory? >> >> >> >> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >> news:eUd030JOGHA.3276@TK2MSFTNGP09.phx.gbl... >>> As long as SQL Server has enough memory devoted to it at all times the >>> data should stay in cache after the first time you access it. It only >>> gets pushed out of cache if it needs the memory space for other data. >>> With 4GB on the machine this should not be an issue at all. Of coarse >>> each time you restart the server or SQL Server the cache gets flushed. >>> That's a lot of machine for a 200MB db so it leads me to believe you >>> have other apps running on the box as well. If so and they use up a LOT >>> of memory you can set SQL Server to a fixed amount of memory or a MIN >>> size that will ensure SQL Server keeps x many MBs at all times. >>> >>> -- >>> Andrew J. Kelly SQL MVP >>> >>> >>> "Troy" <manuel@pushit.com> wrote in message >>> news:OfXx6sIOGHA.428@tk2msftngp13.phx.gbl... >>>> hello, >>>> >>>> we have a dual xeon box with 4 Gb of RAM running windows 2003 sp1 and >>>> sql 2000 sp4. The database is 200 Mb and will probably max out at 400 >>>> Mb. Is there any way to keep the entire database in memory? Any issues >>>> with doing this? >>>> >>>> thanks >>>> >>>> >>> >>> >> >> > >
That was originally meant to keep a particular table and it's indexes in
memory when there wasn't enough for everything. In your case (and most others) it is useless. It often hurt more than it helped since SQL Server is usually smart enough to know which data it should keep in memory if there isn't enough for all of it. Once again you have more than enough so unless you restart it should all stay in memory once it gets there. And FYI DBCC PINTABLE is being depreciated and is a no-op in SQL2005. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Troy" <manuel@pushit.com> wrote in message news:ePyQM6VOGHA.916@TK2MSFTNGP10.phx.gbl... > what about dbcc pintable? > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:%23NobMAUOGHA.2604@TK2MSFTNGP09.phx.gbl... >> My first sentence answered that, cache is memory. >> >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Troy" <manuel@pushit.com> wrote in message >> news:%23e7p0xQOGHA.3856@TK2MSFTNGP12.phx.gbl... >>> thanks for responding. The box is dedicated SQL server and nothing else. >>> How do I keep the db in memory? >>> >>> >>> >>> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >>> news:eUd030JOGHA.3276@TK2MSFTNGP09.phx.gbl... >>>> As long as SQL Server has enough memory devoted to it at all times the >>>> data should stay in cache after the first time you access it. It only >>>> gets pushed out of cache if it needs the memory space for other data. >>>> With 4GB on the machine this should not be an issue at all. Of coarse >>>> each time you restart the server or SQL Server the cache gets flushed. >>>> That's a lot of machine for a 200MB db so it leads me to believe you >>>> have other apps running on the box as well. If so and they use up a >>>> LOT of memory you can set SQL Server to a fixed amount of memory or a >>>> MIN size that will ensure SQL Server keeps x many MBs at all times. >>>> >>>> -- >>>> Andrew J. Kelly SQL MVP >>>> >>>> >>>> "Troy" <manuel@pushit.com> wrote in message >>>> news:OfXx6sIOGHA.428@tk2msftngp13.phx.gbl... >>>>> hello, >>>>> >>>>> we have a dual xeon box with 4 Gb of RAM running windows 2003 sp1 and >>>>> sql 2000 sp4. The database is 200 Mb and will probably max out at 400 >>>>> Mb. Is there any way to keep the entire database in memory? Any issues >>>>> with doing this? >>>>> >>>>> thanks >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
cool - thanks
[quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:%23OG7E0WOGHA.2064@TK2MSFTNGP09.phx.gbl... > That was originally meant to keep a particular table and it's indexes in > memory when there wasn't enough for everything. In your case (and most > others) it is useless. It often hurt more than it helped since SQL Server > is usually smart enough to know which data it should keep in memory if > there isn't enough for all of it. Once again you have more than enough so > unless you restart it should all stay in memory once it gets there. And > FYI DBCC PINTABLE is being depreciated and is a no-op in SQL2005. > > -- > Andrew J. Kelly SQL MVP > > > "Troy" <manuel@pushit.com> wrote in message > news:ePyQM6VOGHA.916@TK2MSFTNGP10.phx.gbl... >> what about dbcc pintable? >> >> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >> news:%23NobMAUOGHA.2604@TK2MSFTNGP09.phx.gbl... >>> My first sentence answered that, cache is memory. >>> >>> >>> -- >>> Andrew J. Kelly SQL MVP >>> >>> >>> "Troy" <manuel@pushit.com> wrote in message >>> news:%23e7p0xQOGHA.3856@TK2MSFTNGP12.phx.gbl... >>>> thanks for responding. The box is dedicated SQL server and nothing >>>> else. How do I keep the db in memory? >>>> >>>> >>>> >>>> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >>>> news:eUd030JOGHA.3276@TK2MSFTNGP09.phx.gbl... >>>>> As long as SQL Server has enough memory devoted to it at all times the >>>>> data should stay in cache after the first time you access it. It only >>>>> gets pushed out of cache if it needs the memory space for other data. >>>>> With 4GB on the machine this should not be an issue at all. Of coarse >>>>> each time you restart the server or SQL Server the cache gets flushed. >>>>> That's a lot of machine for a 200MB db so it leads me to believe you >>>>> have other apps running on the box as well. If so and they use up a >>>>> LOT of memory you can set SQL Server to a fixed amount of memory or a >>>>> MIN size that will ensure SQL Server keeps x many MBs at all times. >>>>> >>>>> -- >>>>> Andrew J. Kelly SQL MVP >>>>> >>>>> >>>>> "Troy" <manuel@pushit.com> wrote in message >>>>> news:OfXx6sIOGHA.428@tk2msftngp13.phx.gbl... >>>>>> hello, >>>>>> >>>>>> we have a dual xeon box with 4 Gb of RAM running windows 2003 sp1 and >>>>>> sql 2000 sp4. The database is 200 Mb and will probably max out at 400 >>>>>> Mb. Is there any way to keep the entire database in memory? Any >>>>>> issues with doing this? >>>>>> >>>>>> thanks >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |