Check Memory Usage

Hello,
Is there any stored procedure or anything else that i can use in one script
to check memory usage, config, etc.
Thanks.
Regards,
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:E6CC8850-72C6-496A-8711-A523DB2F90C8@.microsoft.com...

> Is there any stored procedure or anything else that i can use in one
script
> to check memory usage, config, etc.
There used to be a DBCC MEMUSAGE, which is no longer useful in SQL Server
2000.
The most accurate data would come from the performance monitor objects,
which you could retrieve from WMI... See BOL for "Monitoring Memory Usage"
for useful counters.
You could also investigate "DBCC MEMORYSTATUS" as a possibility...
http://support.microsoft.com/default...b;en-us;271624
Steve
|||Hi,
Check out this article
http://www.sql-server-performance.co...ta_caching.asp
For configuration, you can issue
sp_configure
Thanks
Yogish
sql

Check Memory Usage

Hello,
Is there any stored procedure or anything else that i can use in one script
to check memory usage, config, etc.
Thanks.
Regards,"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:E6CC8850-72C6-496A-8711-A523DB2F90C8@.microsoft.com...
> Is there any stored procedure or anything else that i can use in one
script
> to check memory usage, config, etc.
There used to be a DBCC MEMUSAGE, which is no longer useful in SQL Server
2000.
The most accurate data would come from the performance monitor objects,
which you could retrieve from WMI... See BOL for "Monitoring Memory Usage"
for useful counters.
You could also investigate "DBCC MEMORYSTATUS" as a possibility...
http://support.microsoft.com/default.aspx?scid=kb;en-us;271624
Steve|||Hi,
Check out this articl
http://www.sql-server-performance.com/tp_analyzing_sql_server_2000_data_caching.asp
For configuration, you can issue
sp_configure
--
Thanks
Yogish

Check Memory Usage

Hello,
Is there any stored procedure or anything else that i can use in one script
to check memory usage, config, etc.
Thanks.
Regards,"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:E6CC8850-72C6-496A-8711-A523DB2F90C8@.microsoft.com...

> Is there any stored procedure or anything else that i can use in one
script
> to check memory usage, config, etc.
There used to be a DBCC MEMUSAGE, which is no longer useful in SQL Server
2000.
The most accurate data would come from the performance monitor objects,
which you could retrieve from WMI... See BOL for "Monitoring Memory Usage"
for useful counters.
You could also investigate "DBCC MEMORYSTATUS" as a possibility...
http://support.microsoft.com/defaul...kb;en-us;271624
Steve|||Hi,
Check out this article
http://www.sql-server-performance.c...>
g.as
p
For configuration, you can issue
sp_configure
Thanks
Yogish

check memory pressure

Whats the easy way to check for memory pressure to SQL or if its time to add
more RAM or if theres a memory leak
Its easy from a CPU or disk perspective to look at processor % or queue
length to atleast judge there is some bottleneck, but I have been very
uncertain about how to troubleshoot memory and get to know instantly there
is memory problems
ThanksAre you having performance issues? Is the server failing to run batches or
jobs or are you just trying to avert a fire?
"Hassan" wrote:

> Whats the easy way to check for memory pressure to SQL or if its time to a
dd
> more RAM or if theres a memory leak
> Its easy from a CPU or disk perspective to look at processor % or queue
> length to atleast judge there is some bottleneck, but I have been very
> uncertain about how to troubleshoot memory and get to know instantly there
> is memory problems
> Thanks
>
>|||You may want to refer to the following paper. Though it is SQL2005 centric
but is useful for SQL2000 too.
http://download.microsoft.com/downl...otPerfProbs.doc
thanks,
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:54C8BF2A-CD55-46ED-90D7-C0431A9D51B0@.microsoft.com...[vbcol=seagreen]
> Are you having performance issues? Is the server failing to run batches
> or
> jobs or are you just trying to avert a fire?
> "Hassan" wrote:
>|||Following links have some useful information:
1. Monitoring Memory Usage at
http://msdn.microsoft.com/library/d.../>
on_8x0l.asp
2. Identifying Bottlenecks and Performance-Tuning SQL Server
http://www.informit.com/articles/ar...5&seqNum=2&rl=1
"Hassan" wrote:

> Whats the easy way to check for memory pressure to SQL or if its time to a
dd
> more RAM or if theres a memory leak
> Its easy from a CPU or disk perspective to look at processor % or queue
> length to atleast judge there is some bottleneck, but I have been very
> uncertain about how to troubleshoot memory and get to know instantly there
> is memory problems
> Thanks

check memory pressure

Whats the easy way to check for memory pressure to SQL or if its time to add
more RAM or if theres a memory leak
Its easy from a CPU or disk perspective to look at processor % or queue
length to atleast judge there is some bottleneck, but I have been very
uncertain about how to troubleshoot memory and get to know instantly there
is memory problems
Thanks
Are you having performance issues? Is the server failing to run batches or
jobs or are you just trying to avert a fire?
"Hassan" wrote:

> Whats the easy way to check for memory pressure to SQL or if its time to add
> more RAM or if theres a memory leak
> Its easy from a CPU or disk perspective to look at processor % or queue
> length to atleast judge there is some bottleneck, but I have been very
> uncertain about how to troubleshoot memory and get to know instantly there
> is memory problems
> Thanks
>
>
|||You may want to refer to the following paper. Though it is SQL2005 centric
but is useful for SQL2000 too.
http://download.microsoft.com/downlo...tPerfProbs.doc
thanks,
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:54C8BF2A-CD55-46ED-90D7-C0431A9D51B0@.microsoft.com...[vbcol=seagreen]
> Are you having performance issues? Is the server failing to run batches
> or
> jobs or are you just trying to avert a fire?
> "Hassan" wrote:
|||Following links have some useful information:
1. Monitoring Memory Usage at
http://msdn.microsoft.com/library/de...rfmon_8x0l.asp
2. Identifying Bottlenecks and Performance-Tuning SQL Server
http://www.informit.com/articles/art...&seqNum=2&rl=1
"Hassan" wrote:

> Whats the easy way to check for memory pressure to SQL or if its time to add
> more RAM or if theres a memory leak
> Its easy from a CPU or disk perspective to look at processor % or queue
> length to atleast judge there is some bottleneck, but I have been very
> uncertain about how to troubleshoot memory and get to know instantly there
> is memory problems
> Thanks

check memory pressure

Whats the easy way to check for memory pressure to SQL or if its time to add
more RAM or if theres a memory leak
Its easy from a CPU or disk perspective to look at processor % or queue
length to atleast judge there is some bottleneck, but I have been very
uncertain about how to troubleshoot memory and get to know instantly there
is memory problems
ThanksAre you having performance issues? Is the server failing to run batches or
jobs or are you just trying to avert a fire?
"Hassan" wrote:
> Whats the easy way to check for memory pressure to SQL or if its time to add
> more RAM or if theres a memory leak
> Its easy from a CPU or disk perspective to look at processor % or queue
> length to atleast judge there is some bottleneck, but I have been very
> uncertain about how to troubleshoot memory and get to know instantly there
> is memory problems
> Thanks
>
>|||You may want to refer to the following paper. Though it is SQL2005 centric
but is useful for SQL2000 too.
http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/TShootPerfProbs.doc
thanks,
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:54C8BF2A-CD55-46ED-90D7-C0431A9D51B0@.microsoft.com...
> Are you having performance issues? Is the server failing to run batches
> or
> jobs or are you just trying to avert a fire?
> "Hassan" wrote:
>> Whats the easy way to check for memory pressure to SQL or if its time to
>> add
>> more RAM or if theres a memory leak
>> Its easy from a CPU or disk perspective to look at processor % or queue
>> length to atleast judge there is some bottleneck, but I have been very
>> uncertain about how to troubleshoot memory and get to know instantly
>> there
>> is memory problems
>> Thanks
>>|||Following links have some useful information:
1. Monitoring Memory Usage at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_8x0l.asp
2. Identifying Bottlenecks and Performance-Tuning SQL Server
http://www.informit.com/articles/article.asp?p=390585&seqNum=2&rl=1
"Hassan" wrote:
> Whats the easy way to check for memory pressure to SQL or if its time to add
> more RAM or if theres a memory leak
> Its easy from a CPU or disk perspective to look at processor % or queue
> length to atleast judge there is some bottleneck, but I have been very
> uncertain about how to troubleshoot memory and get to know instantly there
> is memory problems
> Thankssql

Check available memory for SQL 2000

We have a batch that takes 30 mins to run, but sometimes
takes 5 hours. I suspect the problem is the amount of
memory available to SQL Server (2000 SP3).
I have noticed that when SQL Server uses 600 Mb, the batch
lasts 30 mins, and when SQL Server only uses 300 Mb, it
lasts 5 hours.
How can I document that SQL Server would need say 500 Mb ?
Is there an SQL statement that would show how much memory
is available in the system to be taken by SQL Server?
Can I use the profiler to see when SQL Server is trying to
obtain more memory, and the request is denyied or fails?
The other application suspected of using up memory on the
server is Analysis Services. Is there a way I can force
Analysis Services to relase memory without stopping and
restarting the service?Hi James,
You can find out how much memory SQL Server wants to use by checking the SQL
Server:Memory Manager:Target Server Memory counter in the Windows
Performance Monitor. The SQL Server:Memory Manager:Total Server Memory
counter indicates how much memory SQL Server actually uses, and of course
you'd like these to counter to show very similar values.
You can't check from inside SQL Server how much 'free' memory is available,
so the best thing to do is probably configure your server so that SQL Server
always has the right amount of memory available.
--
Jacco Schalkwijk
SQL Server MVP
"James" <news@.att.com> wrote in message
news:465e01c3ff7c$16dee5c0$a501280a@.phx.gbl...
> We have a batch that takes 30 mins to run, but sometimes
> takes 5 hours. I suspect the problem is the amount of
> memory available to SQL Server (2000 SP3).
> I have noticed that when SQL Server uses 600 Mb, the batch
> lasts 30 mins, and when SQL Server only uses 300 Mb, it
> lasts 5 hours.
> How can I document that SQL Server would need say 500 Mb ?
> Is there an SQL statement that would show how much memory
> is available in the system to be taken by SQL Server?
> Can I use the profiler to see when SQL Server is trying to
> obtain more memory, and the request is denyied or fails?
> The other application suspected of using up memory on the
> server is Analysis Services. Is there a way I can force
> Analysis Services to relase memory without stopping and
> restarting the service?|||There is a 'Server memory change' event that you can track using Profiler or
server side tracing procedures.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"James" <news@.att.com> wrote in message
news:465e01c3ff7c$16dee5c0$a501280a@.phx.gbl...
We have a batch that takes 30 mins to run, but sometimes
takes 5 hours. I suspect the problem is the amount of
memory available to SQL Server (2000 SP3).
I have noticed that when SQL Server uses 600 Mb, the batch
lasts 30 mins, and when SQL Server only uses 300 Mb, it
lasts 5 hours.
How can I document that SQL Server would need say 500 Mb ?
Is there an SQL statement that would show how much memory
is available in the system to be taken by SQL Server?
Can I use the profiler to see when SQL Server is trying to
obtain more memory, and the request is denyied or fails?
The other application suspected of using up memory on the
server is Analysis Services. Is there a way I can force
Analysis Services to relase memory without stopping and
restarting the service?|||Jame
By default SQL Server should use memory dynamically. In most cases this works well. Your comments about the memory available, suggests to me that you are running other applications on the server. You really should try to run SQL Server on a dedicated server, you can get unpredictable results when it shares a server with other applications
Regard
John|||As I wrote, the other application is Analysis Services
We sell a solution including SQL Server and AS, and we cannot ask the customers to run the solution on 2 machines instead of one.|||Thanks, I had missed that one
You guys don't know a way of throttling Analysis Services' memory, do you?|||May be there's a way, but I don't work with AS. You might want to post this
to the analysis services group.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:E96727BB-930E-4BCE-9731-9F8E4BA4C3E8@.microsoft.com...
Thanks, I had missed that one.
You guys don't know a way of throttling Analysis Services' memory, do you?|||Hi James,
I learned that you want to find a way to force Analysis Services to release
memory without stopping and restarting the service. Based on my experience,
there is no good way to force Analysis Services to release memory and have
Analysis Services maintain adequate performance at the same time. It seems
you have to add new memory hardware or release the memory of other
applications to lessen the pressure of lack of memory.
I found the following articles for your reference.
Chapter 26 - Performance Tuning Analysis Services
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/p
art6/c2661.mspx
Thanks & Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Check available memory for SQL 2000

We have a batch that takes 30 mins to run, but sometimes
takes 5 hours. I suspect the problem is the amount of
memory available to SQL Server (2000 SP3).
I have noticed that when SQL Server uses 600 Mb, the batch
lasts 30 mins, and when SQL Server only uses 300 Mb, it
lasts 5 hours.
How can I document that SQL Server would need say 500 Mb ?
Is there an SQL statement that would show how much memory
is available in the system to be taken by SQL Server?
Can I use the profiler to see when SQL Server is trying to
obtain more memory, and the request is denyied or fails?
The other application suspected of using up memory on the
server is Analysis Services. Is there a way I can force
Analysis Services to relase memory without stopping and
restarting the service?Hi James,
You can find out how much memory SQL Server wants to use by checking the SQL
Server:Memory Manager:Target Server Memory counter in the Windows
Performance Monitor. The SQL Server:Memory Manager:Total Server Memory
counter indicates how much memory SQL Server actually uses, and of course
you'd like these to counter to show very similar values.
You can't check from inside SQL Server how much 'free' memory is available,
so the best thing to do is probably configure your server so that SQL Server
always has the right amount of memory available.
Jacco Schalkwijk
SQL Server MVP
"James" <news@.att.com> wrote in message
news:465e01c3ff7c$16dee5c0$a501280a@.phx.gbl...
> We have a batch that takes 30 mins to run, but sometimes
> takes 5 hours. I suspect the problem is the amount of
> memory available to SQL Server (2000 SP3).
> I have noticed that when SQL Server uses 600 Mb, the batch
> lasts 30 mins, and when SQL Server only uses 300 Mb, it
> lasts 5 hours.
> How can I document that SQL Server would need say 500 Mb ?
> Is there an SQL statement that would show how much memory
> is available in the system to be taken by SQL Server?
> Can I use the profiler to see when SQL Server is trying to
> obtain more memory, and the request is denyied or fails?
> The other application suspected of using up memory on the
> server is Analysis Services. Is there a way I can force
> Analysis Services to relase memory without stopping and
> restarting the service?|||There is a 'Server memory change' event that you can track using Profiler or
server side tracing procedures.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"James" <news@.att.com> wrote in message
news:465e01c3ff7c$16dee5c0$a501280a@.phx.gbl...
We have a batch that takes 30 mins to run, but sometimes
takes 5 hours. I suspect the problem is the amount of
memory available to SQL Server (2000 SP3).
I have noticed that when SQL Server uses 600 Mb, the batch
lasts 30 mins, and when SQL Server only uses 300 Mb, it
lasts 5 hours.
How can I document that SQL Server would need say 500 Mb ?
Is there an SQL statement that would show how much memory
is available in the system to be taken by SQL Server?
Can I use the profiler to see when SQL Server is trying to
obtain more memory, and the request is denyied or fails?
The other application suspected of using up memory on the
server is Analysis Services. Is there a way I can force
Analysis Services to relase memory without stopping and
restarting the service?|||James
By default SQL Server should use memory dynamically. In most cases this work
s well. Your comments about the memory available, suggests to me that you ar
e running other applications on the server. You really should try to run SQL
Server on a dedicated serv
er, you can get unpredictable results when it shares a server with other app
lications.
Regards
John|||Thanks, I had missed that one.
You guys don't know a way of throttling Analysis Services' memory, do you?|||As I wrote, the other application is Analysis Services.
We sell a solution including SQL Server and AS, and we cannot ask the custom
ers to run the solution on 2 machines instead of one.|||May be there's a way, but I don't work with AS. You might want to post this
to the analysis services group.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:E96727BB-930E-4BCE-9731-9F8E4BA4C3E8@.microsoft.com...
Thanks, I had missed that one.
You guys don't know a way of throttling Analysis Services' memory, do you?|||Hi James,
I learned that you want to find a way to force Analysis Services to release
memory without stopping and restarting the service. Based on my experience,
there is no good way to force Analysis Services to release memory and have
Analysis Services maintain adequate performance at the same time. It seems
you have to add new memory hardware or release the memory of other
applications to lessen the pressure of lack of memory.
I found the following articles for your reference.
Chapter 26 - Performance Tuning Analysis Services
http://www.microsoft.com/resources/.../reskit/en-us/p
art6/c2661.mspx
Thanks & Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.