Is there a way to see the memory (RAM) usage by an SQL Server instance, and
get that value by executing a statement in Query Analyzer?

SQL Server 6.5
SQL Server 7
SQL Server 2000
SQL Server 2005
SQL Server 2008

> Is there a way to see the memory (RAM) usage by an SQL Server instance,
> and get that value by executing a statement in Query Analyzer?

*the memory currently used by an instance of an SQL Server engine*

> SQL Server 6.5
> SQL Server 7
> SQL Server 2000
> SQL Server 2005
> SQL Server 2008

* query can be different between different versions of SQL Server
** query can be non-existant if a given version of SQL Server doesn't
support such a query
*** required level of access (dbo, public, sysadmin, fixed sys admin, backup
operator, etc) not important

1) Use xp_cmdshell to fire a batch file that uses wscript to hit WMI to find
the memory used by each sqlserver.exe process.

2) dbcc memorystatus

3) For 2005+, search BOL for the DMV(s) that expose memory counters and
usage.

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


"Ian Boyd" <ian.msnews010@avatopia.com> wrote in message
news:unV3gl05IHA.4988@TK2MSFTNGP04.phx.gbl...
>> Is there a way to see the memory (RAM) usage by an SQL Server instance,
>> and get that value by executing a statement in Query Analyzer?
>
> *the memory currently used by an instance of an SQL Server engine*
>
>> SQL Server 6.5
>> SQL Server 7
>> SQL Server 2000
>> SQL Server 2005
>> SQL Server 2008
>
> * query can be different between different versions of SQL Server
> ** query can be non-existant if a given version of SQL Server doesn't
> support such a query
> *** required level of access (dbo, public, sysadmin, fixed sys admin,
> backup operator, etc) not important
>

Other than the MemtoLeave memory on 32 bit you can see the Target and Total
Server memory usage with the sys.dm_os_performance_counters dmv along with
several other memory counters. What is the goal? A typical SQL Server will
max out the memory available to it after a short term anyway and it does not
release memory unless the OS specifically asks for it. Then there are
certain conditions that must be met and idiosyncrasies.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Ian Boyd" <ian.msnews010@avatopia.com> wrote in message
news:unV3gl05IHA.4988@TK2MSFTNGP04.phx.gbl...
>> Is there a way to see the memory (RAM) usage by an SQL Server instance,
>> and get that value by executing a statement in Query Analyzer?
>
> *the memory currently used by an instance of an SQL Server engine*
>
>> SQL Server 6.5
>> SQL Server 7
>> SQL Server 2000
>> SQL Server 2005
>> SQL Server 2008
>
> * query can be different between different versions of SQL Server
> ** query can be non-existant if a given version of SQL Server doesn't
> support such a query
> *** required level of access (dbo, public, sysadmin, fixed sys admin,
> backup operator, etc) not important
>

> 2) dbcc memorystatus

Excellent! Thank you.

> What is the goal?

Customer's live SQL 2005 machine is running queries with poor execution plan
choices. i wanted to limit our development machine to the same amount. But i
don't have access to the server's desktop, performance counters, task
manager, etc. But we could talk to SQL Server through SQL Server.


In the end, i starved our SQL Server to 30MB of RAM, as opposed to the
normal 300MB that it normally sits on. The query still used the 'proper'
execution plan, with the 'proper' number of logical reads. So memory
starving wasn't able to reproduce the problem.


....starting new thread shortly... so tired.