| Author |
SQL Server 2005 64 bit memory question
|
|
| imarchenko 2005-12-23, 8:23 pm |
| Hello!
I just installed 64 bit SQL Server 2005 on Windows 2003 SP1 64 bit. I am
puzzled because Total Server Memory (KB) counter shows much higher value
than Total Server memory (KB). I have got 4 GB of RAM installed. My
understanding I do not need to do anything in order to enable SQL Server to
see all 4 GB available. Can someone shed some light on whether I should
expect to see all memory allocated to SQL Server 2005? Normally, I see
Total and Target counters almost identical on our SQL 200 boxes.
Thanks,
Igor
| |
| Mike Epprecht \(SQL MVP\) 2005-12-24, 7:23 am |
| Hi
If you are running IA-64 (Itanium), then it will use all the memory
automatically.
If it is not using all the memory, it might be due to the fact that you have
not stressed the machine enough and caused enough pages of the DB to be
cached in RAM.
Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"imarchenko" < igormarchenko@hotmai
l.com> wrote in message
news:uUU$n5CCGHA.3744@TK2MSFTNGP10.phx.gbl...
> Hello!
>
> I just installed 64 bit SQL Server 2005 on Windows 2003 SP1 64 bit. I
> am puzzled because Total Server Memory (KB) counter shows much higher
> value than Total Server memory (KB). I have got 4 GB of RAM installed. My
> understanding I do not need to do anything in order to enable SQL Server
> to see all 4 GB available. Can someone shed some light on whether I should
> expect to see all memory allocated to SQL Server 2005? Normally, I see
> Total and Target counters almost identical on our SQL 200 boxes.
>
> Thanks,
> Igor
>
| |
| Andrew J. Kelly 2005-12-24, 9:23 am |
| It is dynamic so it will not show the same counters until you actually use
the memory.
--
Andrew J. Kelly SQL MVP
"imarchenko" < igormarchenko@hotmai
l.com> wrote in message
news:uUU$n5CCGHA.3744@TK2MSFTNGP10.phx.gbl...
> Hello!
>
> I just installed 64 bit SQL Server 2005 on Windows 2003 SP1 64 bit. I
> am puzzled because Total Server Memory (KB) counter shows much higher
> value than Total Server memory (KB). I have got 4 GB of RAM installed. My
> understanding I do not need to do anything in order to enable SQL Server
> to see all 4 GB available. Can someone shed some light on whether I should
> expect to see all memory allocated to SQL Server 2005? Normally, I see
> Total and Target counters almost identical on our SQL 200 boxes.
>
> Thanks,
> Igor
>
| |
| imarchenko 2005-12-24, 11:23 am |
| Mike,
I am running Windows 2003 on AMD Opteron processors. Is Itanium
identifed by processor type or OS type? How can I determine that?
Thanks,
Igor
"Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
news:efV5J0HCGHA.2040@TK2MSFTNGP14.phx.gbl...
> Hi
>
> If you are running IA-64 (Itanium), then it will use all the memory
> automatically.
>
> If it is not using all the memory, it might be due to the fact that you
> have not stressed the machine enough and caused enough pages of the DB to
> be cached in RAM.
>
> Regards
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> IM: mike@epprecht.net
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
> "imarchenko" < igormarchenko@hotmai
l.com> wrote in message
> news:uUU$n5CCGHA.3744@TK2MSFTNGP10.phx.gbl...
>
>
| |
| Andrew J. Kelly 2005-12-24, 8:23 pm |
| Itanium is the processor type and AMD is not it.
--
Andrew J. Kelly SQL MVP
"imarchenko" < igormarchenko@hotmai
l.com> wrote in message
news:%23fGPzzKCGHA.2840@TK2MSFTNGP12.phx.gbl...
> Mike,
>
> I am running Windows 2003 on AMD Opteron processors. Is Itanium
> identifed by processor type or OS type? How can I determine that?
>
> Thanks,
> Igor
>
>
>
> "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
> news:efV5J0HCGHA.2040@TK2MSFTNGP14.phx.gbl...
>
>
| |
| Mike Epprecht \(SQL MVP\) 2005-12-24, 8:23 pm |
| Hi
Itanium is the real 64 bit processor, RISC architecture.
Opterons are 32 bit processors, CISC architecture, with 64 extensions (as
are the Xeons with EM64T extensions).
Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"imarchenko" < igormarchenko@hotmai
l.com> wrote in message
news:%23fGPzzKCGHA.2840@TK2MSFTNGP12.phx.gbl...
> Mike,
>
> I am running Windows 2003 on AMD Opteron processors. Is Itanium
> identifed by processor type or OS type? How can I determine that?
>
> Thanks,
> Igor
>
>
>
> "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
> news:efV5J0HCGHA.2040@TK2MSFTNGP14.phx.gbl...
>
>
| |
| imarchenko 2005-12-25, 3:23 am |
| I am slightly confused. Will SQL Server 64 bit (on Windows 64 bit Opterons
processors) be able to see 4GB out of the box? Do I have to make any
configurational changes to enable SQL Server to see 4GB I have?
Thanks,
Igor
"Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
news:O9$hjcMCGHA.4040@TK2MSFTNGP12.phx.gbl...
> Hi
>
> Itanium is the real 64 bit processor, RISC architecture.
>
> Opterons are 32 bit processors, CISC architecture, with 64 extensions (as
> are the Xeons with EM64T extensions).
>
> Regards
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> IM: mike@epprecht.net
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
> "imarchenko" < igormarchenko@hotmai
l.com> wrote in message
> news:%23fGPzzKCGHA.2840@TK2MSFTNGP12.phx.gbl...
>
>
| |
| Roger Wolter[MSFT] 2005-12-25, 3:23 am |
| 64 bit SQL Server 2005 on a 64 bit OS will see as much memory as the OS
supports - I think around half a terabyte.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"imarchenko" < igormarchenko@hotmai
l.com> wrote in message
news:upjfIQQCGHA.2356@tk2msftngp13.phx.gbl...
>I am slightly confused. Will SQL Server 64 bit (on Windows 64 bit Opterons
>processors) be able to see 4GB out of the box? Do I have to make any
>configurational changes to enable SQL Server to see 4GB I have?
>
> Thanks,
> Igor
>
>
> "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
> news:O9$hjcMCGHA.4040@TK2MSFTNGP12.phx.gbl...
>
>
| |
| imarchenko 2005-12-27, 8:23 pm |
| Roger,
I have been trying to stress out SQL Server 2005 64 bit by running up to
255 instances of the same query using ostress. Total
Server Memory (KB) went up to 52992. Target Server Memory KB) number is
2864544. I still can not explain why SQL Server Total Server Memory is still
so low. Would you have an explanation for this? I have 4GB of RAM on the
server running Windows 2003 64 bit.
Thanks,
Igor
"Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
news:%23mb4$$RCGHA.228@TK2MSFTNGP12.phx.gbl...
> 64 bit SQL Server 2005 on a 64 bit OS will see as much memory as the OS
> supports - I think around half a terabyte.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "imarchenko" < igormarchenko@hotmai
l.com> wrote in message
> news:upjfIQQCGHA.2356@tk2msftngp13.phx.gbl...
>
>
| |
| Andrew J. Kelly 2005-12-27, 8:23 pm |
| What are the queries accessing? If they are all accessing a small portion
of the db over and over again then only that data will be in cache. Just
because SQL Server has that much memory available to it does not mean it
will use it.
--
Andrew J. Kelly SQL MVP
"imarchenko" < igormarchenko@hotmai
l.com> wrote in message
news:O4ukUWzCGHA.2480@TK2MSFTNGP10.phx.gbl...
> Roger,
>
> I have been trying to stress out SQL Server 2005 64 bit by running up
> to 255 instances of the same query using ostress. Total
> Server Memory (KB) went up to 52992. Target Server Memory KB) number is
> 2864544. I still can not explain why SQL Server Total Server Memory is
> still so low. Would you have an explanation for this? I have 4GB of RAM on
> the server running Windows 2003 64 bit.
>
> Thanks,
> Igor
>
>
> "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
> news:%23mb4$$RCGHA.228@TK2MSFTNGP12.phx.gbl...
>
>
| |
| imarchenko 2005-12-27, 8:23 pm |
| Anrew,
I am clearing the cache every time I execute my procedure:
CREATE PROCEDURE usp_testMemory @intNoOfRows INT=10
AS
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
select * from
(select top (@intNoOfRows) * from Adventureworks.Person.Contact c) t1
cross join (select top (@intNoOfRows) * from Adventureworks.Person.Contact
c) t2
GO
Total Server Memory statys unchanged. I am using SQL Server Enterprise
Evaluation Edition. I do not think this makes a difference though.
Igor
"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:ezC81czCGHA.272@TK2MSFTNGP10.phx.gbl...
> What are the queries accessing? If they are all accessing a small portion
> of the db over and over again then only that data will be in cache. Just
> because SQL Server has that much memory available to it does not mean it
> will use it.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "imarchenko" < igormarchenko@hotmai
l.com> wrote in message
> news:O4ukUWzCGHA.2480@TK2MSFTNGP10.phx.gbl...
>
>
| |
| Roger Wolter[MSFT] 2005-12-28, 3:23 am |
| Right, and your cache never gets bigger than what's used for one query
because you're clearing it every time you run the query.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"imarchenko" < igormarchenko@hotmai
l.com> wrote in message
news:OXKvMjzCGHA.140@TK2MSFTNGP12.phx.gbl...
> Anrew,
>
> I am clearing the cache every time I execute my procedure:
>
> CREATE PROCEDURE usp_testMemory @intNoOfRows INT=10
>
> AS
>
> DBCC DROPCLEANBUFFERS
>
> DBCC FREEPROCCACHE
>
> select * from
>
> (select top (@intNoOfRows) * from Adventureworks.Person.Contact c) t1
>
> cross join (select top (@intNoOfRows) * from Adventureworks.Person.Contact
> c) t2
>
> GO
>
> Total Server Memory statys unchanged. I am using SQL Server Enterprise
> Evaluation Edition. I do not think this makes a difference though.
>
>
>
> Igor
>
>
>
> "Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
> news:ezC81czCGHA.272@TK2MSFTNGP10.phx.gbl...
>
>
| |
| Andrew J. Kelly 2005-12-28, 3:23 am |
| Why would you want to clear the cache each time you execute the procedure?
Even if you didn't you are selecting the same data over and over again. The
cache will only get as large as there are rows in that query.
--
Andrew J. Kelly SQL MVP
"imarchenko" < igormarchenko@hotmai
l.com> wrote in message
news:OXKvMjzCGHA.140@TK2MSFTNGP12.phx.gbl...
> Anrew,
>
> I am clearing the cache every time I execute my procedure:
>
> CREATE PROCEDURE usp_testMemory @intNoOfRows INT=10
>
> AS
>
> DBCC DROPCLEANBUFFERS
>
> DBCC FREEPROCCACHE
>
> select * from
>
> (select top (@intNoOfRows) * from Adventureworks.Person.Contact c) t1
>
> cross join (select top (@intNoOfRows) * from Adventureworks.Person.Contact
> c) t2
>
> GO
>
> Total Server Memory statys unchanged. I am using SQL Server Enterprise
> Evaluation Edition. I do not think this makes a difference though.
>
>
>
> Igor
>
>
>
> "Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
> news:ezC81czCGHA.272@TK2MSFTNGP10.phx.gbl...
>
>
| |
| imarchenko 2005-12-28, 3:23 am |
| I am executing procedure with randomly generated @intNoOfRows.
I agree with you on cache size. My mistake. I will do more advanced testing
tomorrow.
Thanks,
Igor
"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:%23MNMBJ2CGHA.2704@TK2MSFTNGP15.phx.gbl...
> Why would you want to clear the cache each time you execute the procedure?
> Even if you didn't you are selecting the same data over and over again.
> The cache will only get as large as there are rows in that query.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "imarchenko" < igormarchenko@hotmai
l.com> wrote in message
> news:OXKvMjzCGHA.140@TK2MSFTNGP12.phx.gbl...
>
>
| |
| Andrew J. Kelly 2005-12-28, 9:23 am |
| You are still reading the same rows over and over again. SQL Server will
most likely retrieve the rows in the same order each time so by varying only
the TOP you don't get a true random sample. You are only going against one
table as well. You really need to use many tables and vary the WHERE clause
to get random data and enough to fill the buffer cache.
--
Andrew J. Kelly SQL MVP
"imarchenko" < igormarchenko@hotmai
l.com> wrote in message
news:uajYfU3CGHA.312@TK2MSFTNGP09.phx.gbl...
> I am executing procedure with randomly generated @intNoOfRows.
> I agree with you on cache size. My mistake. I will do more advanced
> testing tomorrow.
>
> Thanks,
> Igor
>
>
> "Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
> news:%23MNMBJ2CGHA.2704@TK2MSFTNGP15.phx.gbl...
>
>
|
|
|
|