having 64 bit sql 2005 running on win 2003 [also 64 bit] on computer with 24
gb of ram [ram is cheap today]. sql server is the only task running on this
computer.
when in perfmon whatching memory usage counter="available mbytes" on
object="memory", i see that this counter never drops below 8 or 9 gb, even
under the heaviest load. does it mean that so many physical ram is never
used?

is it possible to force maximal memory usage [not to left that unused
memory], and minimize disk access [of course, only for reading cache, i hope
that writing to disk is done regularly]

any suggestion?

thnx

sali
Take a look at how to set MAX an MIN memory for SQL Server in the BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/4093553c-9a50-49bd-b3c3-587535b3b6c4.htm

"sali" <sali (AT) euroherc (DOT) hr> wrote

You can try to run a DBCC CHECKDB on your poduction databases.
This will force SQL Server to fully load the database in memory.

To check the SQL Server memory usage, the best way is to look in perfmon the
SQL Server memory counters: Total Server memory....

"sali" <sali (AT) euroherc (DOT) hr> a écrit dans le message de groupe de discussion :
#yC39TGLKHA.4516 (AT) TK2MSFTNGP02 (DOT) phx.gbl...

"You can try to run a DBCC CHECKDB on your poduction databases.
This will force SQL Server to fully load the database in memory."

Ummm...what?

--

Kevin3NF
SQL Server dude

You want fries with that?
http://kevin3nf.blogspot.com/

Blog posts for new DBAs:
http://kevin3nf.blogspot.com/search?q=sql+101

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

"Pascal Deliot" <PDELIOT (AT) microsoft (DOT) com> wrote

I think it is a valid statement Kevin3NF - checkdb will roll through pretty
much all pages in a database, thus requiring those pages to be brought into
RAM for processing.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Kevin3NF" <kevin (AT) SPAMTRAP (DOT) DallasDBAs.com> wrote

I'll have to research that part, but if the statement is accurate that the
database will be full loaded into memory, doesn't that mean you can't
checkdb on any db larger than your available physical RAM?

Off to Tweet Paul Randal

--

Kevin3NF
SQL Server dude

You want fries with that?
http://kevin3nf.blogspot.com/

Blog posts for new DBAs:
http://kevin3nf.blogspot.com/search?q=sql+101

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

"TheSQLGuru" <kgboles (AT) earthlink (DOT) net> wrote

Kevin3NF (kevin (AT) SPAMTRAP (DOT) DallasDBAs.com) writes:
Of course you can! Pages will be flushed from the cache when they are no
longer needed.

Pascal's suggestion was to aimed to create a scenario where SQL Server
uses all memory it can grab.

Not that I am sure that it answers sali's original question. It could
simply the case that his application does not need more than 8-9 GB of
memory. But if the database is > 24 GB and DBCC CHECKDB does not fill
the cache something may be fishy.

Then again, Lock pages in memory may affect things here.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Now I thought on 2005 and greater, CHECKDB was run against a snapshot of the
database and not the database itself. That would mean (to me) that it's not
loading the data pages into memory. I could, of course - be wrong...

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Yes it uses a snapshot but a snapshot will only be used for any pages that
change during the operation. Any unchanged pages will still be read from
cache and if they aren't in the cache it brings them into the cache so it
can read it. SQL Server always reads from cache.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Jeffrey Williams" <jeff.williams3188 (AT) verizon (DOT) net> wrote