> About the memory usage: as far as i know, the sql server use first the RAM
> memory , and after it being full, it start use the tempDB on the HD
> (virtual memory)
>
> my question is: can i know this on runtime that the amount of memory usage
> has started to use virtual memory?
>
SQL Server uses RAM mostly for buffer cache. This cache is not written to
tempdb regardless of memory pressure because dirty pages are written to the
directly to the underlying database files and less frequently used
unmodified pages are freed. In other words, tempdb is not really used for
virtual memory like OS page files.
In addition to temporary user objects, SQL server uses tempdb for internal
work objects and intermediate storage during query processing. Additional
memory can reduce the amount of tempdb space and I/O. However, since you
are constrained by a 2GB memory limit, about all you really do is monitor
tempdb space and ensure database files are large enough to prevent autogrow
during normal operation.
Are you currently experiencing a performance problem? It's usually best to
concentrate on indexing and query tuning. SQL Server generally does a
pretty good job of self-tuning memory.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" wrote in message
> Whell Dan.
>
> About PINTABLE: i've seen the there isn't much diffrence of putting tables
> like this in memory. In the execution plan it reduce it with 0.1 subtree
> cost approx which is not do that mutch to improve performance.
>
> Moreover, the sql server my office is Standard edition which can't be use
> more then 2GB of memory. And as my understand, if the table is being
> loaded to memory, the usage become more limited. ecpecily if the average
> of usage today closes to 1.8GB
>
> therefore i will cancel the using of PINTABLE.
>
> About the memory usage: as far as i know, the sql server use first the RAM
> memory , and after it being full, it start use the tempDB on the HD
> (virtual memory)
>
> my question is: can i know this on runtime that the amount of memory usage
> has started to use virtual memory?
>
> "Dan Guzman" wrote in message
>
>>> I've started placing small tables in the memory by DBCC PINTABLE
>>
>> Why are you doing this? SQL Server automatically keeps frequently used
>> data in cache. By pinning a table in memory, you are reserving memory
>> that may be better used elsewhere. Note that DBCC PINTABLE has been
>> removed from SQL 2005.
>>
>>> Can i get on query anlyser the amout of the memory that sql server is
>>> using right now?
>>
>> See the sysperfinfo system table.
>>
>>> and can i know how mutch of this memory is being using by RAM or HD?
>>
>> I don't understand this your question.
>>
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Roy Goldhammer" wrote in message
>>
>>> Hello there
>>>
>>> I have sql server 2000 SP4
>>>
>>> I've started placing small tables in the memory by DBCC PINTABLE
>>>
>>> Can i get on query anlyser the amout of the memory that sql server is
>>> using right now?
>>>
>>> and can i know how mutch of this memory is being using by RAM or HD?
>>>
>>
>
> >> Stay informed about: memory usage 
FAQ
Search
Profile
Private Messages
Log in
