Hi and TIA! Problem I'm having is after or during when I work in SQL EM
designing views/sp/tables etc and I close down EM and I then start noticing
a degraded performance on my computer. I view task manager and notice that
the Mem. Usage is way up to 200mb and continues to grow. On the Performance
tab the Available Physical Memory will drop to 9K and continues to decrease
as well as the System Cache. If I stop and restart SQL server the Available
will normally shoot back up to 500mb or so and Cache will increase as well.
My question is why this happens and is there a way to prevent it. I'm
running XPPro(SP2), 768mb RAM, 2gb Virtual Memory, 1.5ghz, Toshiba Tecra.
Also, this is only installed on my machine with no clients connected.
Thanks for any advice you can provide and for your time.
******************
ReggieThat server plays with another one making up a cluster?
HD is full?
Any suspicious service associated to Sql Server is running all time?
"Reggie" wrote:
> Hi and TIA! Problem I'm having is after or during when I work in SQL EM
> designing views/sp/tables etc and I close down EM and I then start noticin
g
> a degraded performance on my computer. I view task manager and notice tha
t
> the Mem. Usage is way up to 200mb and continues to grow. On the Performan
ce
> tab the Available Physical Memory will drop to 9K and continues to decreas
e
> as well as the System Cache. If I stop and restart SQL server the Availab
le
> will normally shoot back up to 500mb or so and Cache will increase as well
.
> My question is why this happens and is there a way to prevent it. I'm
> running XPPro(SP2), 768mb RAM, 2gb Virtual Memory, 1.5ghz, Toshiba Tecra.
> Also, this is only installed on my machine with no clients connected.
> Thanks for any advice you can provide and for your time.
> --
> ******************
> Reggie
>
>|||Hi
If you are using a single machine that is not dedicated to only running SQL
Server, then you may want to limit the maximum memory that sql server using
by changing the "max server memory" settings in the server properties dialog
using EM or using sp_configure in Query Analyser. See the topic "Server
Memory Options" in Books online for more.
John
"Reggie" wrote:
> Hi and TIA! Problem I'm having is after or during when I work in SQL EM
> designing views/sp/tables etc and I close down EM and I then start noticin
g
> a degraded performance on my computer. I view task manager and notice tha
t
> the Mem. Usage is way up to 200mb and continues to grow. On the Performan
ce
> tab the Available Physical Memory will drop to 9K and continues to decreas
e
> as well as the System Cache. If I stop and restart SQL server the Availab
le
> will normally shoot back up to 500mb or so and Cache will increase as well
.
> My question is why this happens and is there a way to prevent it. I'm
> running XPPro(SP2), 768mb RAM, 2gb Virtual Memory, 1.5ghz, Toshiba Tecra.
> Also, this is only installed on my machine with no clients connected.
> Thanks for any advice you can provide and for your time.
> --
> ******************
> Reggie
>
>|||Enric, Not part of a cluster
HD is not full.
No services(that I know of) except SQLServerAgent.
I'm going to try limiting the Max server memory as John suggested and see if
this helps. Thanks for your time.
******************
Reggie
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:00B779BD-9FDE-402B-B905-A396947CC73B@.microsoft.com...
> That server plays with another one making up a cluster?
> HD is full?
> Any suspicious service associated to Sql Server is running all time?
> "Reggie" wrote:
>|||John, It is a single machine and not a dedicated server. I will try your
suggestion. Thanks for your time.
******************
Reggie
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:17602C4D-383B-4EF6-A53B-FB821C937707@.microsoft.com...
> Hi
> If you are using a single machine that is not dedicated to only running
> SQL
> Server, then you may want to limit the maximum memory that sql server
> using
> by changing the "max server memory" settings in the server properties
> dialog
> using EM or using sp_configure in Query Analyser. See the topic "Server
> Memory Options" in Books online for more.
> John
> "Reggie" wrote:
>
Showing posts with label tia. Show all posts
Showing posts with label tia. Show all posts
Monday, March 12, 2012
Monday, February 20, 2012
Memory managment question
Hello,
I was wondering if there is any 'un-documented' infomation on managing
SQL's memory cache, such as buffer and procedure?
TIA
Joe DThis is a multi-part message in MIME format.
--020902000504060103040400
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
perfmon is a good place to get info about the current memory breakdown
(particularly the Buffer Manager counters). You can get info about
what's in the procedure cache in SQL 2000 by querying the
master.dbo.syscacheobjects table. In SQL 2005 you can get a fair bit of
info about the memory breakdown and what's in the cache by querying the
memory & cache related dynamic management views & functions (eg.
sys.dm_os_memory_clerks, sys.dm_exec_cached_plans,
sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of
this is undocumented - it's all spelled out fairly clearly in BOL I think.
As for managing the split between data cache and procedure cache, you
can't. It's managed internally and you have no control over it (from
memory you could configure the split in SQL Server 4.21 but not since).
--
*mike hodgson*
http://sqlnerd.blogspot.com
Joe D wrote:
>Hello,
> I was wondering if there is any 'un-documented' infomation on managing
>SQL's memory cache, such as buffer and procedure?
>TIA
>Joe D
>
>
--020902000504060103040400
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>perfmon is a good place to get info about the current memory
breakdown (particularly the Buffer Manager counters). You can get info
about what's in the procedure cache in SQL 2000 by querying the
master.dbo.syscacheobjects table. In SQL 2005 you can get a fair bit
of info about the memory breakdown and what's in the cache by querying
the memory & cache related dynamic management views & functions
(eg. sys.dm_os_memory_clerks, sys.dm_exec_cached_plans,
sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of
this is undocumented - it's all spelled out fairly clearly in BOL I
think.<br>
<br>
As for managing the split between data cache and procedure cache, you
can't. It's managed internally and you have no control over it (from
memory you could configure the split in SQL Server 4.21 but not since).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Joe D wrote:
<blockquote cite="mide1e8vo$11v3$1@.sxnews1.qg.com" type="cite">
<pre wrap="">Hello,
I was wondering if there is any 'un-documented' infomation on managing
SQL's memory cache, such as buffer and procedure?
TIA
Joe D
</pre>
</blockquote>
</body>
</html>
--020902000504060103040400--|||This is a multi-part message in MIME format.
--=_NextPart_000_04FB_01C65CC5.B7FEA730
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Mike
You could actually configure the split between data and proc cache up =through 6.5. The big re-architecture of the product happened in version =7.
-- HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:OyO3WvPXGHA.1192@.TK2MSFTNGP03.phx.gbl...
perfmon is a good place to get info about the current memory breakdown =(particularly the Buffer Manager counters). You can get info about =what's in the procedure cache in SQL 2000 by querying the =master.dbo.syscacheobjects table. In SQL 2005 you can get a fair bit of =info about the memory breakdown and what's in the cache by querying the =memory & cache related dynamic management views & functions (eg. =sys.dm_os_memory_clerks, sys.dm_exec_cached_plans, =sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of =this is undocumented - it's all spelled out fairly clearly in BOL I =think.
As for managing the split between data cache and procedure cache, you =can't. It's managed internally and you have no control over it (from =memory you could configure the split in SQL Server 4.21 but not since).
--
mike hodgson
http://sqlnerd.blogspot.com=20
Joe D wrote: Hello,
I was wondering if there is any 'un-documented' infomation on =managing SQL's memory cache, such as buffer and procedure?
TIA
Joe D
--=_NextPart_000_04FB_01C65CC5.B7FEA730
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi Mike
You could actually configure the split =between data and proc cache up through 6.5. The big re-architecture of the product =happened in version 7.
-- HTHKalen Delaney, SQL Server MVPhttp://www.solidqualitylearning.com">www.solidqualitylearning.com=
"Mike Hodgson" wrote =in message news:OyO3WvPXGHA.1192=@.TK2MSFTNGP03.phx.gbl...perfmon is a good place to get info about the current memory breakdown =(particularly the Buffer Manager counters). You can get info about what's in =the procedure cache in SQL 2000 by querying the master.dbo.syscacheobjects = table. In SQL 2005 you can get a fair bit of info about the =memory breakdown and what's in the cache by querying the memory & cache =related dynamic management views & functions (eg. sys.dm_os_memory_clerks, = sys.dm_exec_cached_plans, sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of this is undocumented - it's =all spelled out fairly clearly in BOL I think.As for managing the =split between data cache and procedure cache, you can't. It's managed internally and you have no control over it (from memory you could =configure the split in SQL Server 4.21 but not since).
--mike =hodgsonhttp://sqlnerd.blogspot.com Joe D wrote: Hello,
I was wondering if there is any 'un-documented' infomation on =managing SQL's memory cache, such as buffer and procedure?
TIA
Joe D
--=_NextPart_000_04FB_01C65CC5.B7FEA730--|||Thank you both for verifying what I had thought was the case to be. I have a
client that I'm working with to help solve some issues and I needed a 2nd
opinion/verification of how memory was used by SQL.
Have a good day.
JD
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eIg1vBQXGHA.3684@.TK2MSFTNGP05.phx.gbl...
Hi Mike
You could actually configure the split between data and proc cache up
through 6.5. The big re-architecture of the product happened in version 7.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:OyO3WvPXGHA.1192@.TK2MSFTNGP03.phx.gbl...
perfmon is a good place to get info about the current memory breakdown
(particularly the Buffer Manager counters). You can get info about what's
in the procedure cache in SQL 2000 by querying the
master.dbo.syscacheobjects table. In SQL 2005 you can get a fair bit of
info about the memory breakdown and what's in the cache by querying the
memory & cache related dynamic management views & functions (eg.
sys.dm_os_memory_clerks, sys.dm_exec_cached_plans,
sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of this is
undocumented - it's all spelled out fairly clearly in BOL I think.
As for managing the split between data cache and procedure cache, you can't.
It's managed internally and you have no control over it (from memory you
could configure the split in SQL Server 4.21 but not since).
--
mike hodgson
http://sqlnerd.blogspot.com
Joe D wrote:
Hello,
I was wondering if there is any 'un-documented' infomation on managing
SQL's memory cache, such as buffer and procedure?
TIA
Joe D|||Join the Club JD.
I've had this customer who knew just enough information to be dangerous
but not enough to really know what they where talking about. It seems
no matter what I have to tell them or show them, they think they know a
better way. Even though that way goes against all the data & info that
I show them.
Billy
I was wondering if there is any 'un-documented' infomation on managing
SQL's memory cache, such as buffer and procedure?
TIA
Joe DThis is a multi-part message in MIME format.
--020902000504060103040400
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
perfmon is a good place to get info about the current memory breakdown
(particularly the Buffer Manager counters). You can get info about
what's in the procedure cache in SQL 2000 by querying the
master.dbo.syscacheobjects table. In SQL 2005 you can get a fair bit of
info about the memory breakdown and what's in the cache by querying the
memory & cache related dynamic management views & functions (eg.
sys.dm_os_memory_clerks, sys.dm_exec_cached_plans,
sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of
this is undocumented - it's all spelled out fairly clearly in BOL I think.
As for managing the split between data cache and procedure cache, you
can't. It's managed internally and you have no control over it (from
memory you could configure the split in SQL Server 4.21 but not since).
--
*mike hodgson*
http://sqlnerd.blogspot.com
Joe D wrote:
>Hello,
> I was wondering if there is any 'un-documented' infomation on managing
>SQL's memory cache, such as buffer and procedure?
>TIA
>Joe D
>
>
--020902000504060103040400
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>perfmon is a good place to get info about the current memory
breakdown (particularly the Buffer Manager counters). You can get info
about what's in the procedure cache in SQL 2000 by querying the
master.dbo.syscacheobjects table. In SQL 2005 you can get a fair bit
of info about the memory breakdown and what's in the cache by querying
the memory & cache related dynamic management views & functions
(eg. sys.dm_os_memory_clerks, sys.dm_exec_cached_plans,
sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of
this is undocumented - it's all spelled out fairly clearly in BOL I
think.<br>
<br>
As for managing the split between data cache and procedure cache, you
can't. It's managed internally and you have no control over it (from
memory you could configure the split in SQL Server 4.21 but not since).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Joe D wrote:
<blockquote cite="mide1e8vo$11v3$1@.sxnews1.qg.com" type="cite">
<pre wrap="">Hello,
I was wondering if there is any 'un-documented' infomation on managing
SQL's memory cache, such as buffer and procedure?
TIA
Joe D
</pre>
</blockquote>
</body>
</html>
--020902000504060103040400--|||This is a multi-part message in MIME format.
--=_NextPart_000_04FB_01C65CC5.B7FEA730
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Mike
You could actually configure the split between data and proc cache up =through 6.5. The big re-architecture of the product happened in version =7.
-- HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message =news:OyO3WvPXGHA.1192@.TK2MSFTNGP03.phx.gbl...
perfmon is a good place to get info about the current memory breakdown =(particularly the Buffer Manager counters). You can get info about =what's in the procedure cache in SQL 2000 by querying the =master.dbo.syscacheobjects table. In SQL 2005 you can get a fair bit of =info about the memory breakdown and what's in the cache by querying the =memory & cache related dynamic management views & functions (eg. =sys.dm_os_memory_clerks, sys.dm_exec_cached_plans, =sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of =this is undocumented - it's all spelled out fairly clearly in BOL I =think.
As for managing the split between data cache and procedure cache, you =can't. It's managed internally and you have no control over it (from =memory you could configure the split in SQL Server 4.21 but not since).
--
mike hodgson
http://sqlnerd.blogspot.com=20
Joe D wrote: Hello,
I was wondering if there is any 'un-documented' infomation on =managing SQL's memory cache, such as buffer and procedure?
TIA
Joe D
--=_NextPart_000_04FB_01C65CC5.B7FEA730
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi Mike
You could actually configure the split =between data and proc cache up through 6.5. The big re-architecture of the product =happened in version 7.
-- HTHKalen Delaney, SQL Server MVPhttp://www.solidqualitylearning.com">www.solidqualitylearning.com=
"Mike Hodgson"
--mike =hodgsonhttp://sqlnerd.blogspot.com Joe D wrote: Hello,
I was wondering if there is any 'un-documented' infomation on =managing SQL's memory cache, such as buffer and procedure?
TIA
Joe D
--=_NextPart_000_04FB_01C65CC5.B7FEA730--|||Thank you both for verifying what I had thought was the case to be. I have a
client that I'm working with to help solve some issues and I needed a 2nd
opinion/verification of how memory was used by SQL.
Have a good day.
JD
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eIg1vBQXGHA.3684@.TK2MSFTNGP05.phx.gbl...
Hi Mike
You could actually configure the split between data and proc cache up
through 6.5. The big re-architecture of the product happened in version 7.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:OyO3WvPXGHA.1192@.TK2MSFTNGP03.phx.gbl...
perfmon is a good place to get info about the current memory breakdown
(particularly the Buffer Manager counters). You can get info about what's
in the procedure cache in SQL 2000 by querying the
master.dbo.syscacheobjects table. In SQL 2005 you can get a fair bit of
info about the memory breakdown and what's in the cache by querying the
memory & cache related dynamic management views & functions (eg.
sys.dm_os_memory_clerks, sys.dm_exec_cached_plans,
sys.dm_exec_plan_attributes(), sys.dm_exec_sql_text()). But none of this is
undocumented - it's all spelled out fairly clearly in BOL I think.
As for managing the split between data cache and procedure cache, you can't.
It's managed internally and you have no control over it (from memory you
could configure the split in SQL Server 4.21 but not since).
--
mike hodgson
http://sqlnerd.blogspot.com
Joe D wrote:
Hello,
I was wondering if there is any 'un-documented' infomation on managing
SQL's memory cache, such as buffer and procedure?
TIA
Joe D|||Join the Club JD.
I've had this customer who knew just enough information to be dangerous
but not enough to really know what they where talking about. It seems
no matter what I have to tell them or show them, they think they know a
better way. Even though that way goes against all the data & info that
I show them.
Billy
Subscribe to:
Posts (Atom)