Memory usage per database

Does anybody know if there is a tool to tell the memory
usage per database? I need the tool to create a report and
analyze it to see memory usage for each database.I am not sure if profiler or performance can provide this info to you. Have
you tried them?
"Tracy Yang" <tyang@.nas.edu> wrote in message
news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
> Does anybody know if there is a tool to tell the memory
> usage per database? I need the tool to create a report and
> analyze it to see memory usage for each database.
>
>|||There isn't one that I know of at the moment that will do what your asking.
--
Andrew J. Kelly
SQL Server MVP
"Tracy Yang" <tyang@.nas.edu> wrote in message
news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
> Does anybody know if there is a tool to tell the memory
> usage per database? I need the tool to create a report and
> analyze it to see memory usage for each database.
>
>|||I believe InDepth for SQL Server from Veritas will do this if my memory
serves me correctly. You could do the same kind of think with Profiler
(although with greater overhead). What exactly are you trying to measure
this for ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tracy Yang" <tyang@.nas.edu> wrote in message
news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
Does anybody know if there is a tool to tell the memory
usage per database? I need the tool to create a report and
analyze it to see memory usage for each database.|||I thought it might as well but from looking at the web site I couldn't
confirm that. I know it does almost everything else , just not sure about
splitting the cache on a db level or not.
--
Andrew J. Kelly
SQL Server MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
> I believe InDepth for SQL Server from Veritas will do this if my memory
> serves me correctly. You could do the same kind of think with Profiler
> (although with greater overhead). What exactly are you trying to measure
> this for ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Tracy Yang" <tyang@.nas.edu> wrote in message
> news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
> Does anybody know if there is a tool to tell the memory
> usage per database? I need the tool to create a report and
> analyze it to see memory usage for each database.
>
>|||Last time I used it was before Veritas took it over so I'm not entirely sure
it does. I think tracking IO per db would be more realistic, not sure what
memory per database means.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
I thought it might as well but from looking at the web site I couldn't
confirm that. I know it does almost everything else , just not sure about
splitting the cache on a db level or not.
--
Andrew J. Kelly
SQL Server MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
> I believe InDepth for SQL Server from Veritas will do this if my memory
> serves me correctly. You could do the same kind of think with Profiler
> (although with greater overhead). What exactly are you trying to measure
> this for ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Tracy Yang" <tyang@.nas.edu> wrote in message
> news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
> Does anybody know if there is a tool to tell the memory
> usage per database? I need the tool to create a report and
> analyze it to see memory usage for each database.
>
>|||The memory reach 85% of 2GB in Standard Edition, how could
I tell which database is the biggest user?
>--Original Message--
>Last time I used it was before Veritas took it over so
I'm not entirely sure
>it does. I think tracking IO per db would be more
realistic, not sure what
>memory per database means.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
message
>news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
>I thought it might as well but from looking at the web
site I couldn't
>confirm that. I know it does almost everything else ,
just not sure about
>splitting the cache on a db level or not.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
message
>news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
>> I believe InDepth for SQL Server from Veritas will do
this if my memory
>> serves me correctly. You could do the same kind of
think with Profiler
>> (although with greater overhead). What exactly are you
trying to measure
>> this for ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Tracy Yang" <tyang@.nas.edu> wrote in message
>> news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
>> Does anybody know if there is a tool to tell the memory
>> usage per database? I need the tool to create a report
and
>> analyze it to see memory usage for each database.
>>
>>
>
>.
>|||One way to get a feel is to monitor the system for a while in Profiler using
the Batch Completed event and the Reads and Writes columns. You can put the
trace into a table and run some queries via the dbname (or what ever works
best for you) to sum up this activity. While it doesn't directly relate to
memory consumption it will give you a pretty good idea of what is accessed
most and how the cache "may" be using it. Since sql server doesn't release
memory once it has it unless the OS specifically calls for it the amount
used is not a very good indicator to make some decisions on. If you only
have 2GB total and std edition of sql will only use about 1.7GB anyway,
which is about 85%. If you had more ram available it would most likely use
that as well. What is it your looking to find and why? Maybe we can
suggest a better approach.
--
Andrew J. Kelly
SQL Server MVP
"Tracy Yang" <tracylyang@.hotmail.com> wrote in message
news:0e8701c3a3b5$258e32b0$a501280a@.phx.gbl...
> The memory reach 85% of 2GB in Standard Edition, how could
> I tell which database is the biggest user?
>
> >--Original Message--
> >Last time I used it was before Veritas took it over so
> I'm not entirely sure
> >it does. I think tracking IO per db would be more
> realistic, not sure what
> >memory per database means.
> >
> >--
> >HTH
> >
> >Jasper Smith (SQL Server MVP)
> >
> >I support PASS - the definitive, global
> >community for SQL Server professionals -
> >http://www.sqlpass.org
> >
> >"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> message
> >news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
> >I thought it might as well but from looking at the web
> site I couldn't
> >confirm that. I know it does almost everything else ,
> just not sure about
> >splitting the cache on a db level or not.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
> message
> >news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
> >> I believe InDepth for SQL Server from Veritas will do
> this if my memory
> >> serves me correctly. You could do the same kind of
> think with Profiler
> >> (although with greater overhead). What exactly are you
> trying to measure
> >> this for ?
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >>
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "Tracy Yang" <tyang@.nas.edu> wrote in message
> >> news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
> >> Does anybody know if there is a tool to tell the memory
> >> usage per database? I need the tool to create a report
> and
> >> analyze it to see memory usage for each database.
> >>
> >>
> >>
> >>
> >
> >
> >
> >.
> >|||dbcc memusage
use at your own risk, I've seen it cause UMS scheduling errors.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eYXXVJ8oDHA.648@.TK2MSFTNGP11.phx.gbl...
> One way to get a feel is to monitor the system for a while in Profiler
using
> the Batch Completed event and the Reads and Writes columns. You can put
the
> trace into a table and run some queries via the dbname (or what ever works
> best for you) to sum up this activity. While it doesn't directly relate
to
> memory consumption it will give you a pretty good idea of what is accessed
> most and how the cache "may" be using it. Since sql server doesn't
release
> memory once it has it unless the OS specifically calls for it the amount
> used is not a very good indicator to make some decisions on. If you only
> have 2GB total and std edition of sql will only use about 1.7GB anyway,
> which is about 85%. If you had more ram available it would most likely
use
> that as well. What is it your looking to find and why? Maybe we can
> suggest a better approach.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Tracy Yang" <tracylyang@.hotmail.com> wrote in message
> news:0e8701c3a3b5$258e32b0$a501280a@.phx.gbl...
> > The memory reach 85% of 2GB in Standard Edition, how could
> > I tell which database is the biggest user?
> >
> >
> > >--Original Message--
> > >Last time I used it was before Veritas took it over so
> > I'm not entirely sure
> > >it does. I think tracking IO per db would be more
> > realistic, not sure what
> > >memory per database means.
> > >
> > >--
> > >HTH
> > >
> > >Jasper Smith (SQL Server MVP)
> > >
> > >I support PASS - the definitive, global
> > >community for SQL Server professionals -
> > >http://www.sqlpass.org
> > >
> > >"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> > message
> > >news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
> > >I thought it might as well but from looking at the web
> > site I couldn't
> > >confirm that. I know it does almost everything else ,
> > just not sure about
> > >splitting the cache on a db level or not.
> > >
> > >--
> > >
> > >Andrew J. Kelly
> > >SQL Server MVP
> > >
> > >
> > >"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
> > message
> > >news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
> > >> I believe InDepth for SQL Server from Veritas will do
> > this if my memory
> > >> serves me correctly. You could do the same kind of
> > think with Profiler
> > >> (although with greater overhead). What exactly are you
> > trying to measure
> > >> this for ?
> > >>
> > >> --
> > >> HTH
> > >>
> > >> Jasper Smith (SQL Server MVP)
> > >>
> > >> I support PASS - the definitive, global
> > >> community for SQL Server professionals -
> > >> http://www.sqlpass.org
> > >>
> > >> "Tracy Yang" <tyang@.nas.edu> wrote in message
> > >> news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
> > >> Does anybody know if there is a tool to tell the memory
> > >> usage per database? I need the tool to create a report
> > and
> > >> analyze it to see memory usage for each database.
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> > >
> > >.
> > >
>|||How to measure if 2GB of memory is enough for the
databases, I might have dozens of DBs with large
transactions on the server. Using Profiler is to trace the
bad queries or stored procedure, what if the applications
are better tuned, how can I know if the dozens of DBs can
fits into 2GB memory. To be honest with you, performance
tuning is weakest part for me. Thanks for you patience!
>--Original Message--
>One way to get a feel is to monitor the system for a
while in Profiler using
>the Batch Completed event and the Reads and Writes
columns. You can put the
>trace into a table and run some queries via the dbname
(or what ever works
>best for you) to sum up this activity. While it doesn't
directly relate to
>memory consumption it will give you a pretty good idea of
what is accessed
>most and how the cache "may" be using it. Since sql
server doesn't release
>memory once it has it unless the OS specifically calls
for it the amount
>used is not a very good indicator to make some decisions
on. If you only
>have 2GB total and std edition of sql will only use about
1.7GB anyway,
>which is about 85%. If you had more ram available it
would most likely use
>that as well. What is it your looking to find and why?
Maybe we can
>suggest a better approach.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Tracy Yang" <tracylyang@.hotmail.com> wrote in message
>news:0e8701c3a3b5$258e32b0$a501280a@.phx.gbl...
>> The memory reach 85% of 2GB in Standard Edition, how
could
>> I tell which database is the biggest user?
>>
>> >--Original Message--
>> >Last time I used it was before Veritas took it over so
>> I'm not entirely sure
>> >it does. I think tracking IO per db would be more
>> realistic, not sure what
>> >memory per database means.
>> >
>> >--
>> >HTH
>> >
>> >Jasper Smith (SQL Server MVP)
>> >
>> >I support PASS - the definitive, global
>> >community for SQL Server professionals -
>> >http://www.sqlpass.org
>> >
>> >"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote
in
>> message
>> >news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
>> >I thought it might as well but from looking at the web
>> site I couldn't
>> >confirm that. I know it does almost everything else ,
>> just not sure about
>> >splitting the cache on a db level or not.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
>> message
>> >news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
>> >> I believe InDepth for SQL Server from Veritas will do
>> this if my memory
>> >> serves me correctly. You could do the same kind of
>> think with Profiler
>> >> (although with greater overhead). What exactly are
you
>> trying to measure
>> >> this for ?
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >>
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "Tracy Yang" <tyang@.nas.edu> wrote in message
>> >> news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
>> >> Does anybody know if there is a tool to tell the
memory
>> >> usage per database? I need the tool to create a
report
>> and
>> >> analyze it to see memory usage for each database.
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>> >
>> >.
>> >
>
>.
>|||I don't believe it breaks this down as she wants to individual db info.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:upbudM8oDHA.2772@.TK2MSFTNGP10.phx.gbl...
> dbcc memusage
> use at your own risk, I've seen it cause UMS scheduling errors.
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eYXXVJ8oDHA.648@.TK2MSFTNGP11.phx.gbl...
> > One way to get a feel is to monitor the system for a while in Profiler
> using
> > the Batch Completed event and the Reads and Writes columns. You can put
> the
> > trace into a table and run some queries via the dbname (or what ever
works
> > best for you) to sum up this activity. While it doesn't directly relate
> to
> > memory consumption it will give you a pretty good idea of what is
accessed
> > most and how the cache "may" be using it. Since sql server doesn't
> release
> > memory once it has it unless the OS specifically calls for it the amount
> > used is not a very good indicator to make some decisions on. If you
only
> > have 2GB total and std edition of sql will only use about 1.7GB anyway,
> > which is about 85%. If you had more ram available it would most likely
> use
> > that as well. What is it your looking to find and why? Maybe we can
> > suggest a better approach.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Tracy Yang" <tracylyang@.hotmail.com> wrote in message
> > news:0e8701c3a3b5$258e32b0$a501280a@.phx.gbl...
> > > The memory reach 85% of 2GB in Standard Edition, how could
> > > I tell which database is the biggest user?
> > >
> > >
> > > >--Original Message--
> > > >Last time I used it was before Veritas took it over so
> > > I'm not entirely sure
> > > >it does. I think tracking IO per db would be more
> > > realistic, not sure what
> > > >memory per database means.
> > > >
> > > >--
> > > >HTH
> > > >
> > > >Jasper Smith (SQL Server MVP)
> > > >
> > > >I support PASS - the definitive, global
> > > >community for SQL Server professionals -
> > > >http://www.sqlpass.org
> > > >
> > > >"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> > > message
> > > >news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
> > > >I thought it might as well but from looking at the web
> > > site I couldn't
> > > >confirm that. I know it does almost everything else ,
> > > just not sure about
> > > >splitting the cache on a db level or not.
> > > >
> > > >--
> > > >
> > > >Andrew J. Kelly
> > > >SQL Server MVP
> > > >
> > > >
> > > >"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
> > > message
> > > >news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
> > > >> I believe InDepth for SQL Server from Veritas will do
> > > this if my memory
> > > >> serves me correctly. You could do the same kind of
> > > think with Profiler
> > > >> (although with greater overhead). What exactly are you
> > > trying to measure
> > > >> this for ?
> > > >>
> > > >> --
> > > >> HTH
> > > >>
> > > >> Jasper Smith (SQL Server MVP)
> > > >>
> > > >> I support PASS - the definitive, global
> > > >> community for SQL Server professionals -
> > > >> http://www.sqlpass.org
> > > >>
> > > >> "Tracy Yang" <tyang@.nas.edu> wrote in message
> > > >> news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
> > > >> Does anybody know if there is a tool to tell the memory
> > > >> usage per database? I need the tool to create a report
> > > and
> > > >> analyze it to see memory usage for each database.
> > > >>
> > > >>
> > > >>
> > > >>
> > > >
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||You basically tell by looking at your perfmon counters for the disks. If
your disk queues are high then it is a sign that you either need to better
tune your queries or schema or you don't have enough memory for the disk
cache. The size of the db can have little to do with how much data you
actually access from the disk. Have a look here for some pointers:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Tracy Yang" <tracylyang@.hotmail.com> wrote in message
news:111d01c3a3c8$6dd491a0$a601280a@.phx.gbl...
> How to measure if 2GB of memory is enough for the
> databases, I might have dozens of DBs with large
> transactions on the server. Using Profiler is to trace the
> bad queries or stored procedure, what if the applications
> are better tuned, how can I know if the dozens of DBs can
> fits into 2GB memory. To be honest with you, performance
> tuning is weakest part for me. Thanks for you patience!
>
> >--Original Message--
> >One way to get a feel is to monitor the system for a
> while in Profiler using
> >the Batch Completed event and the Reads and Writes
> columns. You can put the
> >trace into a table and run some queries via the dbname
> (or what ever works
> >best for you) to sum up this activity. While it doesn't
> directly relate to
> >memory consumption it will give you a pretty good idea of
> what is accessed
> >most and how the cache "may" be using it. Since sql
> server doesn't release
> >memory once it has it unless the OS specifically calls
> for it the amount
> >used is not a very good indicator to make some decisions
> on. If you only
> >have 2GB total and std edition of sql will only use about
> 1.7GB anyway,
> >which is about 85%. If you had more ram available it
> would most likely use
> >that as well. What is it your looking to find and why?
> Maybe we can
> >suggest a better approach.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Tracy Yang" <tracylyang@.hotmail.com> wrote in message
> >news:0e8701c3a3b5$258e32b0$a501280a@.phx.gbl...
> >> The memory reach 85% of 2GB in Standard Edition, how
> could
> >> I tell which database is the biggest user?
> >>
> >>
> >> >--Original Message--
> >> >Last time I used it was before Veritas took it over so
> >> I'm not entirely sure
> >> >it does. I think tracking IO per db would be more
> >> realistic, not sure what
> >> >memory per database means.
> >> >
> >> >--
> >> >HTH
> >> >
> >> >Jasper Smith (SQL Server MVP)
> >> >
> >> >I support PASS - the definitive, global
> >> >community for SQL Server professionals -
> >> >http://www.sqlpass.org
> >> >
> >> >"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote
> in
> >> message
> >> >news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
> >> >I thought it might as well but from looking at the web
> >> site I couldn't
> >> >confirm that. I know it does almost everything else ,
> >> just not sure about
> >> >splitting the cache on a db level or not.
> >> >
> >> >--
> >> >
> >> >Andrew J. Kelly
> >> >SQL Server MVP
> >> >
> >> >
> >> >"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
> >> message
> >> >news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
> >> >> I believe InDepth for SQL Server from Veritas will do
> >> this if my memory
> >> >> serves me correctly. You could do the same kind of
> >> think with Profiler
> >> >> (although with greater overhead). What exactly are
> you
> >> trying to measure
> >> >> this for ?
> >> >>
> >> >> --
> >> >> HTH
> >> >>
> >> >> Jasper Smith (SQL Server MVP)
> >> >>
> >> >> I support PASS - the definitive, global
> >> >> community for SQL Server professionals -
> >> >> http://www.sqlpass.org
> >> >>
> >> >> "Tracy Yang" <tyang@.nas.edu> wrote in message
> >> >> news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
> >> >> Does anybody know if there is a tool to tell the
> memory
> >> >> usage per database? I need the tool to create a
> report
> >> and
> >> >> analyze it to see memory usage for each database.
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Sure it does, I've used the below script to determine memory usage by apps.
Like I said, tho, it can be impactive and I think it's undoc'd/unsupported
set nocount on
use master
go
if object_id('tempdb..#memusage') is not null
drop table #memusage
create table #memusage (dbid int, objectid int, indexid int, buffers int,
dirty int, objectname sysname NULL)
-- 8.0 has a new column
if charindex('Microsoft SQL Server 7.00',@.@.version) = 0
insert #memusage (dbid, objectid, indexid, buffers, dirty)
exec('dbcc memusage')
else
insert #memusage (dbid, objectid, indexid, buffers)
exec('dbcc memusage')
declare @.dbid int
declare @.sql varchar(1000)
declare c cursor for
select distinct dbid
from #memusage
open c
fetch next from c into @.dbid
while @.@.fetch_status = 0
begin
set @.sql = 'update #memusage
set objectname = (select max(name) from ' + db_name(@.dbid) + '..sysobjects
where id = objectid)
where dbid = ' + convert(varchar,@.dbid)
exec(@.sql)
fetch next from c into @.dbid
end
close c
deallocate c
-- MB is pages/128
select db_name(dbid), objectname, indexid, buffers/128 as MB_Buffers from
#memusage
order by buffers desc
select sum(buffers/128) as MB_Buffers_Total
from #memusage
drop table #memusage
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Os3F7pBpDHA.2000@.TK2MSFTNGP10.phx.gbl...
> I don't believe it breaks this down as she wants to individual db info.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:upbudM8oDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > dbcc memusage
> >
> > use at your own risk, I've seen it cause UMS scheduling errors.
> >
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:eYXXVJ8oDHA.648@.TK2MSFTNGP11.phx.gbl...
> > > One way to get a feel is to monitor the system for a while in Profiler
> > using
> > > the Batch Completed event and the Reads and Writes columns. You can
put
> > the
> > > trace into a table and run some queries via the dbname (or what ever
> works
> > > best for you) to sum up this activity. While it doesn't directly
relate
> > to
> > > memory consumption it will give you a pretty good idea of what is
> accessed
> > > most and how the cache "may" be using it. Since sql server doesn't
> > release
> > > memory once it has it unless the OS specifically calls for it the
amount
> > > used is not a very good indicator to make some decisions on. If you
> only
> > > have 2GB total and std edition of sql will only use about 1.7GB
anyway,
> > > which is about 85%. If you had more ram available it would most
likely
> > use
> > > that as well. What is it your looking to find and why? Maybe we can
> > > suggest a better approach.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Tracy Yang" <tracylyang@.hotmail.com> wrote in message
> > > news:0e8701c3a3b5$258e32b0$a501280a@.phx.gbl...
> > > > The memory reach 85% of 2GB in Standard Edition, how could
> > > > I tell which database is the biggest user?
> > > >
> > > >
> > > > >--Original Message--
> > > > >Last time I used it was before Veritas took it over so
> > > > I'm not entirely sure
> > > > >it does. I think tracking IO per db would be more
> > > > realistic, not sure what
> > > > >memory per database means.
> > > > >
> > > > >--
> > > > >HTH
> > > > >
> > > > >Jasper Smith (SQL Server MVP)
> > > > >
> > > > >I support PASS - the definitive, global
> > > > >community for SQL Server professionals -
> > > > >http://www.sqlpass.org
> > > > >
> > > > >"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> > > > message
> > > > >news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
> > > > >I thought it might as well but from looking at the web
> > > > site I couldn't
> > > > >confirm that. I know it does almost everything else ,
> > > > just not sure about
> > > > >splitting the cache on a db level or not.
> > > > >
> > > > >--
> > > > >
> > > > >Andrew J. Kelly
> > > > >SQL Server MVP
> > > > >
> > > > >
> > > > >"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
> > > > message
> > > > >news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
> > > > >> I believe InDepth for SQL Server from Veritas will do
> > > > this if my memory
> > > > >> serves me correctly. You could do the same kind of
> > > > think with Profiler
> > > > >> (although with greater overhead). What exactly are you
> > > > trying to measure
> > > > >> this for ?
> > > > >>
> > > > >> --
> > > > >> HTH
> > > > >>
> > > > >> Jasper Smith (SQL Server MVP)
> > > > >>
> > > > >> I support PASS - the definitive, global
> > > > >> community for SQL Server professionals -
> > > > >> http://www.sqlpass.org
> > > > >>
> > > > >> "Tracy Yang" <tyang@.nas.edu> wrote in message
> > > > >> news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
> > > > >> Does anybody know if there is a tool to tell the memory
> > > > >> usage per database? I need the tool to create a report
> > > > and
> > > > >> analyze it to see memory usage for each database.
> > > > >>
> > > > >>
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||Sorry about that Kevin. I was thinking DBCC MEMORYSTATUS the whole time.
And you are also correct about it being undoc'd and unsupported.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:ecnKZOHpDHA.2512@.TK2MSFTNGP09.phx.gbl...
> Sure it does, I've used the below script to determine memory usage by
apps.
> Like I said, tho, it can be impactive and I think it's undoc'd/unsupported
>
> set nocount on
> use master
> go
> if object_id('tempdb..#memusage') is not null
> drop table #memusage
> create table #memusage (dbid int, objectid int, indexid int, buffers int,
> dirty int, objectname sysname NULL)
> -- 8.0 has a new column
> if charindex('Microsoft SQL Server 7.00',@.@.version) = 0
> insert #memusage (dbid, objectid, indexid, buffers, dirty)
> exec('dbcc memusage')
> else
> insert #memusage (dbid, objectid, indexid, buffers)
> exec('dbcc memusage')
>
> declare @.dbid int
> declare @.sql varchar(1000)
> declare c cursor for
> select distinct dbid
> from #memusage
> open c
> fetch next from c into @.dbid
> while @.@.fetch_status = 0
> begin
> set @.sql = 'update #memusage
> set objectname = (select max(name) from ' + db_name(@.dbid) + '..sysobjects
> where id = objectid)
> where dbid = ' + convert(varchar,@.dbid)
> exec(@.sql)
> fetch next from c into @.dbid
> end
>
> close c
> deallocate c
> -- MB is pages/128
> select db_name(dbid), objectname, indexid, buffers/128 as MB_Buffers from
> #memusage
> order by buffers desc
> select sum(buffers/128) as MB_Buffers_Total
> from #memusage
> drop table #memusage
>
>
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:Os3F7pBpDHA.2000@.TK2MSFTNGP10.phx.gbl...
> > I don't believe it breaks this down as she wants to individual db info.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:upbudM8oDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > > dbcc memusage
> > >
> > > use at your own risk, I've seen it cause UMS scheduling errors.
> > >
> > >
> > > --
> > > Kevin Connell, MCDBA
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:eYXXVJ8oDHA.648@.TK2MSFTNGP11.phx.gbl...
> > > > One way to get a feel is to monitor the system for a while in
Profiler
> > > using
> > > > the Batch Completed event and the Reads and Writes columns. You can
> put
> > > the
> > > > trace into a table and run some queries via the dbname (or what ever
> > works
> > > > best for you) to sum up this activity. While it doesn't directly
> relate
> > > to
> > > > memory consumption it will give you a pretty good idea of what is
> > accessed
> > > > most and how the cache "may" be using it. Since sql server doesn't
> > > release
> > > > memory once it has it unless the OS specifically calls for it the
> amount
> > > > used is not a very good indicator to make some decisions on. If you
> > only
> > > > have 2GB total and std edition of sql will only use about 1.7GB
> anyway,
> > > > which is about 85%. If you had more ram available it would most
> likely
> > > use
> > > > that as well. What is it your looking to find and why? Maybe we
can
> > > > suggest a better approach.
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Tracy Yang" <tracylyang@.hotmail.com> wrote in message
> > > > news:0e8701c3a3b5$258e32b0$a501280a@.phx.gbl...
> > > > > The memory reach 85% of 2GB in Standard Edition, how could
> > > > > I tell which database is the biggest user?
> > > > >
> > > > >
> > > > > >--Original Message--
> > > > > >Last time I used it was before Veritas took it over so
> > > > > I'm not entirely sure
> > > > > >it does. I think tracking IO per db would be more
> > > > > realistic, not sure what
> > > > > >memory per database means.
> > > > > >
> > > > > >--
> > > > > >HTH
> > > > > >
> > > > > >Jasper Smith (SQL Server MVP)
> > > > > >
> > > > > >I support PASS - the definitive, global
> > > > > >community for SQL Server professionals -
> > > > > >http://www.sqlpass.org
> > > > > >
> > > > > >"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
> > > > > message
> > > > > >news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
> > > > > >I thought it might as well but from looking at the web
> > > > > site I couldn't
> > > > > >confirm that. I know it does almost everything else ,
> > > > > just not sure about
> > > > > >splitting the cache on a db level or not.
> > > > > >
> > > > > >--
> > > > > >
> > > > > >Andrew J. Kelly
> > > > > >SQL Server MVP
> > > > > >
> > > > > >
> > > > > >"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
> > > > > message
> > > > > >news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
> > > > > >> I believe InDepth for SQL Server from Veritas will do
> > > > > this if my memory
> > > > > >> serves me correctly. You could do the same kind of
> > > > > think with Profiler
> > > > > >> (although with greater overhead). What exactly are you
> > > > > trying to measure
> > > > > >> this for ?
> > > > > >>
> > > > > >> --
> > > > > >> HTH
> > > > > >>
> > > > > >> Jasper Smith (SQL Server MVP)
> > > > > >>
> > > > > >> I support PASS - the definitive, global
> > > > > >> community for SQL Server professionals -
> > > > > >> http://www.sqlpass.org
> > > > > >>
> > > > > >> "Tracy Yang" <tyang@.nas.edu> wrote in message
> > > > > >> news:10cc01c3a2f2$d5b5c270$a401280a@.phx.gbl...
> > > > > >> Does anybody know if there is a tool to tell the memory
> > > > > >> usage per database? I need the tool to create a report
> > > > > and
> > > > > >> analyze it to see memory usage for each database.
> > > > > >>
> > > > > >>
> > > > > >>
> > > > > >>
> > > > > >
> > > > > >
> > > > > >
> > > > > >.
> > > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Does DBCC memorystatus support on SQL 2K, couldn't find
from BOL.
>--Original Message--
>Sorry about that Kevin. I was thinking DBCC MEMORYSTATUS
the whole time.
>And you are also correct about it being undoc'd and
unsupported.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
>news:ecnKZOHpDHA.2512@.TK2MSFTNGP09.phx.gbl...
>> Sure it does, I've used the below script to determine
memory usage by
>apps.
>> Like I said, tho, it can be impactive and I think it's
undoc'd/unsupported
>>
>> set nocount on
>> use master
>> go
>> if object_id('tempdb..#memusage') is not null
>> drop table #memusage
>> create table #memusage (dbid int, objectid int, indexid
int, buffers int,
>> dirty int, objectname sysname NULL)
>> -- 8.0 has a new column
>> if charindex('Microsoft SQL Server 7.00',@.@.version) = 0
>> insert #memusage (dbid, objectid, indexid, buffers,
dirty)
>> exec('dbcc memusage')
>> else
>> insert #memusage (dbid, objectid, indexid, buffers)
>> exec('dbcc memusage')
>>
>> declare @.dbid int
>> declare @.sql varchar(1000)
>> declare c cursor for
>> select distinct dbid
>> from #memusage
>> open c
>> fetch next from c into @.dbid
>> while @.@.fetch_status = 0
>> begin
>> set @.sql = 'update #memusage
>> set objectname = (select max(name) from ' + db_name
(@.dbid) + '..sysobjects
>> where id = objectid)
>> where dbid = ' + convert(varchar,@.dbid)
>> exec(@.sql)
>> fetch next from c into @.dbid
>> end
>>
>> close c
>> deallocate c
>> -- MB is pages/128
>> select db_name(dbid), objectname, indexid, buffers/128
as MB_Buffers from
>> #memusage
>> order by buffers desc
>> select sum(buffers/128) as MB_Buffers_Total
>> from #memusage
>> drop table #memusage
>>
>>
>> --
>> Kevin Connell, MCDBA
>> ----
>> The views expressed here are my own
>> and not of my employer.
>> ----
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote
in message
>> news:Os3F7pBpDHA.2000@.TK2MSFTNGP10.phx.gbl...
>> > I don't believe it breaks this down as she wants to
individual db info.
>> >
>> > --
>> >
>> > Andrew J. Kelly
>> > SQL Server MVP
>> >
>> >
>> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
>> > news:upbudM8oDHA.2772@.TK2MSFTNGP10.phx.gbl...
>> > > dbcc memusage
>> > >
>> > > use at your own risk, I've seen it cause UMS
scheduling errors.
>> > >
>> > >
>> > > --
>> > > Kevin Connell, MCDBA
>> > > ----
>> > > The views expressed here are my own
>> > > and not of my employer.
>> > > ----
>> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
wrote in message
>> > > news:eYXXVJ8oDHA.648@.TK2MSFTNGP11.phx.gbl...
>> > > > One way to get a feel is to monitor the system
for a while in
>Profiler
>> > > using
>> > > > the Batch Completed event and the Reads and
Writes columns. You can
>> put
>> > > the
>> > > > trace into a table and run some queries via the
dbname (or what ever
>> > works
>> > > > best for you) to sum up this activity. While it
doesn't directly
>> relate
>> > > to
>> > > > memory consumption it will give you a pretty good
idea of what is
>> > accessed
>> > > > most and how the cache "may" be using it. Since
sql server doesn't
>> > > release
>> > > > memory once it has it unless the OS specifically
calls for it the
>> amount
>> > > > used is not a very good indicator to make some
decisions on. If you
>> > only
>> > > > have 2GB total and std edition of sql will only
use about 1.7GB
>> anyway,
>> > > > which is about 85%. If you had more ram
available it would most
>> likely
>> > > use
>> > > > that as well. What is it your looking to find
and why? Maybe we
>can
>> > > > suggest a better approach.
>> > > >
>> > > > --
>> > > >
>> > > > Andrew J. Kelly
>> > > > SQL Server MVP
>> > > >
>> > > >
>> > > > "Tracy Yang" <tracylyang@.hotmail.com> wrote in
message
>> > > > news:0e8701c3a3b5$258e32b0$a501280a@.phx.gbl...
>> > > > > The memory reach 85% of 2GB in Standard
Edition, how could
>> > > > > I tell which database is the biggest user?
>> > > > >
>> > > > >
>> > > > > >--Original Message--
>> > > > > >Last time I used it was before Veritas took it
over so
>> > > > > I'm not entirely sure
>> > > > > >it does. I think tracking IO per db would be
more
>> > > > > realistic, not sure what
>> > > > > >memory per database means.
>> > > > > >
>> > > > > >--
>> > > > > >HTH
>> > > > > >
>> > > > > >Jasper Smith (SQL Server MVP)
>> > > > > >
>> > > > > >I support PASS - the definitive, global
>> > > > > >community for SQL Server professionals -
>> > > > > >http://www.sqlpass.org
>> > > > > >
>> > > > > >"Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote in
>> > > > > message
>> > > > > >news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
>> > > > > >I thought it might as well but from looking at
the web
>> > > > > site I couldn't
>> > > > > >confirm that. I know it does almost
everything else ,
>> > > > > just not sure about
>> > > > > >splitting the cache on a db level or not.
>> > > > > >
>> > > > > >--
>> > > > > >
>> > > > > >Andrew J. Kelly
>> > > > > >SQL Server MVP
>> > > > > >
>> > > > > >
>> > > > > >"Jasper Smith" <jasper_smith9@.hotmail.com>
wrote in
>> > > > > message
>> > > > > >news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
>> > > > > >> I believe InDepth for SQL Server from
Veritas will do
>> > > > > this if my memory
>> > > > > >> serves me correctly. You could do the same
kind of
>> > > > > think with Profiler
>> > > > > >> (although with greater overhead). What
exactly are you
>> > > > > trying to measure
>> > > > > >> this for ?
>> > > > > >>
>> > > > > >> --
>> > > > > >> HTH
>> > > > > >>
>> > > > > >> Jasper Smith (SQL Server MVP)
>> > > > > >>
>> > > > > >> I support PASS - the definitive, global
>> > > > > >> community for SQL Server professionals -
>> > > > > >> http://www.sqlpass.org
>> > > > > >>
>> > > > > >> "Tracy Yang" <tyang@.nas.edu> wrote in message
>> > > > > >> news:10cc01c3a2f2$d5b5c270
$a401280a@.phx.gbl...
>> > > > > >> Does anybody know if there is a tool to tell
the memory
>> > > > > >> usage per database? I need the tool to
create a report
>> > > > > and
>> > > > > >> analyze it to see memory usage for each
database.
>> > > > > >>
>> > > > > >>
>> > > > > >>
>> > > > > >>
>> > > > > >
>> > > > > >
>> > > > > >
>> > > > > >.
>> > > > > >
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>
>.
>|||I don't think dbcc memusage nor dbcc memorystatus is documented or
supported. use at your own risk.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Tracy Yang" <tracylyang@.hotmail.com> wrote in message
news:027101c3a499$e1ed1b30$a301280a@.phx.gbl...
> Does DBCC memorystatus support on SQL 2K, couldn't find
> from BOL.
> >--Original Message--
> >Sorry about that Kevin. I was thinking DBCC MEMORYSTATUS
> the whole time.
> >And you are also correct about it being undoc'd and
> unsupported.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> >news:ecnKZOHpDHA.2512@.TK2MSFTNGP09.phx.gbl...
> >> Sure it does, I've used the below script to determine
> memory usage by
> >apps.
> >> Like I said, tho, it can be impactive and I think it's
> undoc'd/unsupported
> >>
> >>
> >> set nocount on
> >>
> >> use master
> >> go
> >>
> >> if object_id('tempdb..#memusage') is not null
> >> drop table #memusage
> >>
> >> create table #memusage (dbid int, objectid int, indexid
> int, buffers int,
> >> dirty int, objectname sysname NULL)
> >>
> >> -- 8.0 has a new column
> >> if charindex('Microsoft SQL Server 7.00',@.@.version) = 0
> >> insert #memusage (dbid, objectid, indexid, buffers,
> dirty)
> >> exec('dbcc memusage')
> >> else
> >> insert #memusage (dbid, objectid, indexid, buffers)
> >> exec('dbcc memusage')
> >>
> >>
> >> declare @.dbid int
> >> declare @.sql varchar(1000)
> >> declare c cursor for
> >> select distinct dbid
> >> from #memusage
> >>
> >> open c
> >> fetch next from c into @.dbid
> >> while @.@.fetch_status = 0
> >> begin
> >>
> >> set @.sql = 'update #memusage
> >> set objectname = (select max(name) from ' + db_name
> (@.dbid) + '..sysobjects
> >> where id = objectid)
> >> where dbid = ' + convert(varchar,@.dbid)
> >> exec(@.sql)
> >>
> >> fetch next from c into @.dbid
> >> end
> >>
> >>
> >> close c
> >> deallocate c
> >>
> >> -- MB is pages/128
> >> select db_name(dbid), objectname, indexid, buffers/128
> as MB_Buffers from
> >> #memusage
> >> order by buffers desc
> >>
> >> select sum(buffers/128) as MB_Buffers_Total
> >> from #memusage
> >>
> >> drop table #memusage
> >>
> >>
> >>
> >>
> >> --
> >> Kevin Connell, MCDBA
> >> ----
> >> The views expressed here are my own
> >> and not of my employer.
> >> ----
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote
> in message
> >> news:Os3F7pBpDHA.2000@.TK2MSFTNGP10.phx.gbl...
> >> > I don't believe it breaks this down as she wants to
> individual db info.
> >> >
> >> > --
> >> >
> >> > Andrew J. Kelly
> >> > SQL Server MVP
> >> >
> >> >
> >> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> >> > news:upbudM8oDHA.2772@.TK2MSFTNGP10.phx.gbl...
> >> > > dbcc memusage
> >> > >
> >> > > use at your own risk, I've seen it cause UMS
> scheduling errors.
> >> > >
> >> > >
> >> > > --
> >> > > Kevin Connell, MCDBA
> >> > > ----
> >> > > The views expressed here are my own
> >> > > and not of my employer.
> >> > > ----
> >> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
> wrote in message
> >> > > news:eYXXVJ8oDHA.648@.TK2MSFTNGP11.phx.gbl...
> >> > > > One way to get a feel is to monitor the system
> for a while in
> >Profiler
> >> > > using
> >> > > > the Batch Completed event and the Reads and
> Writes columns. You can
> >> put
> >> > > the
> >> > > > trace into a table and run some queries via the
> dbname (or what ever
> >> > works
> >> > > > best for you) to sum up this activity. While it
> doesn't directly
> >> relate
> >> > > to
> >> > > > memory consumption it will give you a pretty good
> idea of what is
> >> > accessed
> >> > > > most and how the cache "may" be using it. Since
> sql server doesn't
> >> > > release
> >> > > > memory once it has it unless the OS specifically
> calls for it the
> >> amount
> >> > > > used is not a very good indicator to make some
> decisions on. If you
> >> > only
> >> > > > have 2GB total and std edition of sql will only
> use about 1.7GB
> >> anyway,
> >> > > > which is about 85%. If you had more ram
> available it would most
> >> likely
> >> > > use
> >> > > > that as well. What is it your looking to find
> and why? Maybe we
> >can
> >> > > > suggest a better approach.
> >> > > >
> >> > > > --
> >> > > >
> >> > > > Andrew J. Kelly
> >> > > > SQL Server MVP
> >> > > >
> >> > > >
> >> > > > "Tracy Yang" <tracylyang@.hotmail.com> wrote in
> message
> >> > > > news:0e8701c3a3b5$258e32b0$a501280a@.phx.gbl...
> >> > > > > The memory reach 85% of 2GB in Standard
> Edition, how could
> >> > > > > I tell which database is the biggest user?
> >> > > > >
> >> > > > >
> >> > > > > >--Original Message--
> >> > > > > >Last time I used it was before Veritas took it
> over so
> >> > > > > I'm not entirely sure
> >> > > > > >it does. I think tracking IO per db would be
> more
> >> > > > > realistic, not sure what
> >> > > > > >memory per database means.
> >> > > > > >
> >> > > > > >--
> >> > > > > >HTH
> >> > > > > >
> >> > > > > >Jasper Smith (SQL Server MVP)
> >> > > > > >
> >> > > > > >I support PASS - the definitive, global
> >> > > > > >community for SQL Server professionals -
> >> > > > > >http://www.sqlpass.org
> >> > > > > >
> >> > > > > >"Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote in
> >> > > > > message
> >> > > > > >news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
> >> > > > > >I thought it might as well but from looking at
> the web
> >> > > > > site I couldn't
> >> > > > > >confirm that. I know it does almost
> everything else ,
> >> > > > > just not sure about
> >> > > > > >splitting the cache on a db level or not.
> >> > > > > >
> >> > > > > >--
> >> > > > > >
> >> > > > > >Andrew J. Kelly
> >> > > > > >SQL Server MVP
> >> > > > > >
> >> > > > > >
> >> > > > > >"Jasper Smith" <jasper_smith9@.hotmail.com>
> wrote in
> >> > > > > message
> >> > > > > >news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
> >> > > > > >> I believe InDepth for SQL Server from
> Veritas will do
> >> > > > > this if my memory
> >> > > > > >> serves me correctly. You could do the same
> kind of
> >> > > > > think with Profiler
> >> > > > > >> (although with greater overhead). What
> exactly are you
> >> > > > > trying to measure
> >> > > > > >> this for ?
> >> > > > > >>
> >> > > > > >> --
> >> > > > > >> HTH
> >> > > > > >>
> >> > > > > >> Jasper Smith (SQL Server MVP)
> >> > > > > >>
> >> > > > > >> I support PASS - the definitive, global
> >> > > > > >> community for SQL Server professionals -
> >> > > > > >> http://www.sqlpass.org
> >> > > > > >>
> >> > > > > >> "Tracy Yang" <tyang@.nas.edu> wrote in message
> >> > > > > >> news:10cc01c3a2f2$d5b5c270
> $a401280a@.phx.gbl...
> >> > > > > >> Does anybody know if there is a tool to tell
> the memory
> >> > > > > >> usage per database? I need the tool to
> create a report
> >> > > > > and
> >> > > > > >> analyze it to see memory usage for each
> database.
> >> > > > > >>
> >> > > > > >>
> >> > > > > >>
> >> > > > > >>
> >> > > > > >
> >> > > > > >
> >> > > > > >
> >> > > > > >.
> >> > > > > >
> >> > > >
> >> > > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >|||Not it is not supported either. While you may get these to work I would be
very leery of using them (or any unsupported commands) on a production
machine. There is a KB that states running Memusage on a busy system may
cause it to have problems.
--
Andrew J. Kelly
SQL Server MVP
"Tracy Yang" <tracylyang@.hotmail.com> wrote in message
news:027101c3a499$e1ed1b30$a301280a@.phx.gbl...
> Does DBCC memorystatus support on SQL 2K, couldn't find
> from BOL.
> >--Original Message--
> >Sorry about that Kevin. I was thinking DBCC MEMORYSTATUS
> the whole time.
> >And you are also correct about it being undoc'd and
> unsupported.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> >news:ecnKZOHpDHA.2512@.TK2MSFTNGP09.phx.gbl...
> >> Sure it does, I've used the below script to determine
> memory usage by
> >apps.
> >> Like I said, tho, it can be impactive and I think it's
> undoc'd/unsupported
> >>
> >>
> >> set nocount on
> >>
> >> use master
> >> go
> >>
> >> if object_id('tempdb..#memusage') is not null
> >> drop table #memusage
> >>
> >> create table #memusage (dbid int, objectid int, indexid
> int, buffers int,
> >> dirty int, objectname sysname NULL)
> >>
> >> -- 8.0 has a new column
> >> if charindex('Microsoft SQL Server 7.00',@.@.version) = 0
> >> insert #memusage (dbid, objectid, indexid, buffers,
> dirty)
> >> exec('dbcc memusage')
> >> else
> >> insert #memusage (dbid, objectid, indexid, buffers)
> >> exec('dbcc memusage')
> >>
> >>
> >> declare @.dbid int
> >> declare @.sql varchar(1000)
> >> declare c cursor for
> >> select distinct dbid
> >> from #memusage
> >>
> >> open c
> >> fetch next from c into @.dbid
> >> while @.@.fetch_status = 0
> >> begin
> >>
> >> set @.sql = 'update #memusage
> >> set objectname = (select max(name) from ' + db_name
> (@.dbid) + '..sysobjects
> >> where id = objectid)
> >> where dbid = ' + convert(varchar,@.dbid)
> >> exec(@.sql)
> >>
> >> fetch next from c into @.dbid
> >> end
> >>
> >>
> >> close c
> >> deallocate c
> >>
> >> -- MB is pages/128
> >> select db_name(dbid), objectname, indexid, buffers/128
> as MB_Buffers from
> >> #memusage
> >> order by buffers desc
> >>
> >> select sum(buffers/128) as MB_Buffers_Total
> >> from #memusage
> >>
> >> drop table #memusage
> >>
> >>
> >>
> >>
> >> --
> >> Kevin Connell, MCDBA
> >> ----
> >> The views expressed here are my own
> >> and not of my employer.
> >> ----
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote
> in message
> >> news:Os3F7pBpDHA.2000@.TK2MSFTNGP10.phx.gbl...
> >> > I don't believe it breaks this down as she wants to
> individual db info.
> >> >
> >> > --
> >> >
> >> > Andrew J. Kelly
> >> > SQL Server MVP
> >> >
> >> >
> >> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> >> > news:upbudM8oDHA.2772@.TK2MSFTNGP10.phx.gbl...
> >> > > dbcc memusage
> >> > >
> >> > > use at your own risk, I've seen it cause UMS
> scheduling errors.
> >> > >
> >> > >
> >> > > --
> >> > > Kevin Connell, MCDBA
> >> > > ----
> >> > > The views expressed here are my own
> >> > > and not of my employer.
> >> > > ----
> >> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
> wrote in message
> >> > > news:eYXXVJ8oDHA.648@.TK2MSFTNGP11.phx.gbl...
> >> > > > One way to get a feel is to monitor the system
> for a while in
> >Profiler
> >> > > using
> >> > > > the Batch Completed event and the Reads and
> Writes columns. You can
> >> put
> >> > > the
> >> > > > trace into a table and run some queries via the
> dbname (or what ever
> >> > works
> >> > > > best for you) to sum up this activity. While it
> doesn't directly
> >> relate
> >> > > to
> >> > > > memory consumption it will give you a pretty good
> idea of what is
> >> > accessed
> >> > > > most and how the cache "may" be using it. Since
> sql server doesn't
> >> > > release
> >> > > > memory once it has it unless the OS specifically
> calls for it the
> >> amount
> >> > > > used is not a very good indicator to make some
> decisions on. If you
> >> > only
> >> > > > have 2GB total and std edition of sql will only
> use about 1.7GB
> >> anyway,
> >> > > > which is about 85%. If you had more ram
> available it would most
> >> likely
> >> > > use
> >> > > > that as well. What is it your looking to find
> and why? Maybe we
> >can
> >> > > > suggest a better approach.
> >> > > >
> >> > > > --
> >> > > >
> >> > > > Andrew J. Kelly
> >> > > > SQL Server MVP
> >> > > >
> >> > > >
> >> > > > "Tracy Yang" <tracylyang@.hotmail.com> wrote in
> message
> >> > > > news:0e8701c3a3b5$258e32b0$a501280a@.phx.gbl...
> >> > > > > The memory reach 85% of 2GB in Standard
> Edition, how could
> >> > > > > I tell which database is the biggest user?
> >> > > > >
> >> > > > >
> >> > > > > >--Original Message--
> >> > > > > >Last time I used it was before Veritas took it
> over so
> >> > > > > I'm not entirely sure
> >> > > > > >it does. I think tracking IO per db would be
> more
> >> > > > > realistic, not sure what
> >> > > > > >memory per database means.
> >> > > > > >
> >> > > > > >--
> >> > > > > >HTH
> >> > > > > >
> >> > > > > >Jasper Smith (SQL Server MVP)
> >> > > > > >
> >> > > > > >I support PASS - the definitive, global
> >> > > > > >community for SQL Server professionals -
> >> > > > > >http://www.sqlpass.org
> >> > > > > >
> >> > > > > >"Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote in
> >> > > > > message
> >> > > > > >news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
> >> > > > > >I thought it might as well but from looking at
> the web
> >> > > > > site I couldn't
> >> > > > > >confirm that. I know it does almost
> everything else ,
> >> > > > > just not sure about
> >> > > > > >splitting the cache on a db level or not.
> >> > > > > >
> >> > > > > >--
> >> > > > > >
> >> > > > > >Andrew J. Kelly
> >> > > > > >SQL Server MVP
> >> > > > > >
> >> > > > > >
> >> > > > > >"Jasper Smith" <jasper_smith9@.hotmail.com>
> wrote in
> >> > > > > message
> >> > > > > >news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
> >> > > > > >> I believe InDepth for SQL Server from
> Veritas will do
> >> > > > > this if my memory
> >> > > > > >> serves me correctly. You could do the same
> kind of
> >> > > > > think with Profiler
> >> > > > > >> (although with greater overhead). What
> exactly are you
> >> > > > > trying to measure
> >> > > > > >> this for ?
> >> > > > > >>
> >> > > > > >> --
> >> > > > > >> HTH
> >> > > > > >>
> >> > > > > >> Jasper Smith (SQL Server MVP)
> >> > > > > >>
> >> > > > > >> I support PASS - the definitive, global
> >> > > > > >> community for SQL Server professionals -
> >> > > > > >> http://www.sqlpass.org
> >> > > > > >>
> >> > > > > >> "Tracy Yang" <tyang@.nas.edu> wrote in message
> >> > > > > >> news:10cc01c3a2f2$d5b5c270
> $a401280a@.phx.gbl...
> >> > > > > >> Does anybody know if there is a tool to tell
> the memory
> >> > > > > >> usage per database? I need the tool to
> create a report
> >> > > > > and
> >> > > > > >> analyze it to see memory usage for each
> database.
> >> > > > > >>
> >> > > > > >>
> >> > > > > >>
> >> > > > > >>
> >> > > > > >
> >> > > > > >
> >> > > > > >
> >> > > > > >.
> >> > > > > >
> >> > > >
> >> > > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >|||I have *definately* seen DBCC MEMUSAGE cause temporary problems .
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eddbuKKpDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Not it is not supported either. While you may get these to work I would
be
> very leery of using them (or any unsupported commands) on a production
> machine. There is a KB that states running Memusage on a busy system may
> cause it to have problems.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Tracy Yang" <tracylyang@.hotmail.com> wrote in message
> news:027101c3a499$e1ed1b30$a301280a@.phx.gbl...
> > Does DBCC memorystatus support on SQL 2K, couldn't find
> > from BOL.
> >
> > >--Original Message--
> > >Sorry about that Kevin. I was thinking DBCC MEMORYSTATUS
> > the whole time.
> > >And you are also correct about it being undoc'd and
> > unsupported.
> > >
> > >--
> > >
> > >Andrew J. Kelly
> > >SQL Server MVP
> > >
> > >
> > >"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > >news:ecnKZOHpDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > >> Sure it does, I've used the below script to determine
> > memory usage by
> > >apps.
> > >> Like I said, tho, it can be impactive and I think it's
> > undoc'd/unsupported
> > >>
> > >>
> > >> set nocount on
> > >>
> > >> use master
> > >> go
> > >>
> > >> if object_id('tempdb..#memusage') is not null
> > >> drop table #memusage
> > >>
> > >> create table #memusage (dbid int, objectid int, indexid
> > int, buffers int,
> > >> dirty int, objectname sysname NULL)
> > >>
> > >> -- 8.0 has a new column
> > >> if charindex('Microsoft SQL Server 7.00',@.@.version) = 0
> > >> insert #memusage (dbid, objectid, indexid, buffers,
> > dirty)
> > >> exec('dbcc memusage')
> > >> else
> > >> insert #memusage (dbid, objectid, indexid, buffers)
> > >> exec('dbcc memusage')
> > >>
> > >>
> > >> declare @.dbid int
> > >> declare @.sql varchar(1000)
> > >> declare c cursor for
> > >> select distinct dbid
> > >> from #memusage
> > >>
> > >> open c
> > >> fetch next from c into @.dbid
> > >> while @.@.fetch_status = 0
> > >> begin
> > >>
> > >> set @.sql = 'update #memusage
> > >> set objectname = (select max(name) from ' + db_name
> > (@.dbid) + '..sysobjects
> > >> where id = objectid)
> > >> where dbid = ' + convert(varchar,@.dbid)
> > >> exec(@.sql)
> > >>
> > >> fetch next from c into @.dbid
> > >> end
> > >>
> > >>
> > >> close c
> > >> deallocate c
> > >>
> > >> -- MB is pages/128
> > >> select db_name(dbid), objectname, indexid, buffers/128
> > as MB_Buffers from
> > >> #memusage
> > >> order by buffers desc
> > >>
> > >> select sum(buffers/128) as MB_Buffers_Total
> > >> from #memusage
> > >>
> > >> drop table #memusage
> > >>
> > >>
> > >>
> > >>
> > >> --
> > >> Kevin Connell, MCDBA
> > >> ----
> > >> The views expressed here are my own
> > >> and not of my employer.
> > >> ----
> > >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote
> > in message
> > >> news:Os3F7pBpDHA.2000@.TK2MSFTNGP10.phx.gbl...
> > >> > I don't believe it breaks this down as she wants to
> > individual db info.
> > >> >
> > >> > --
> > >> >
> > >> > Andrew J. Kelly
> > >> > SQL Server MVP
> > >> >
> > >> >
> > >> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > >> > news:upbudM8oDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > >> > > dbcc memusage
> > >> > >
> > >> > > use at your own risk, I've seen it cause UMS
> > scheduling errors.
> > >> > >
> > >> > >
> > >> > > --
> > >> > > Kevin Connell, MCDBA
> > >> > > ----
> > >> > > The views expressed here are my own
> > >> > > and not of my employer.
> > >> > > ----
> > >> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com>
> > wrote in message
> > >> > > news:eYXXVJ8oDHA.648@.TK2MSFTNGP11.phx.gbl...
> > >> > > > One way to get a feel is to monitor the system
> > for a while in
> > >Profiler
> > >> > > using
> > >> > > > the Batch Completed event and the Reads and
> > Writes columns. You can
> > >> put
> > >> > > the
> > >> > > > trace into a table and run some queries via the
> > dbname (or what ever
> > >> > works
> > >> > > > best for you) to sum up this activity. While it
> > doesn't directly
> > >> relate
> > >> > > to
> > >> > > > memory consumption it will give you a pretty good
> > idea of what is
> > >> > accessed
> > >> > > > most and how the cache "may" be using it. Since
> > sql server doesn't
> > >> > > release
> > >> > > > memory once it has it unless the OS specifically
> > calls for it the
> > >> amount
> > >> > > > used is not a very good indicator to make some
> > decisions on. If you
> > >> > only
> > >> > > > have 2GB total and std edition of sql will only
> > use about 1.7GB
> > >> anyway,
> > >> > > > which is about 85%. If you had more ram
> > available it would most
> > >> likely
> > >> > > use
> > >> > > > that as well. What is it your looking to find
> > and why? Maybe we
> > >can
> > >> > > > suggest a better approach.
> > >> > > >
> > >> > > > --
> > >> > > >
> > >> > > > Andrew J. Kelly
> > >> > > > SQL Server MVP
> > >> > > >
> > >> > > >
> > >> > > > "Tracy Yang" <tracylyang@.hotmail.com> wrote in
> > message
> > >> > > > news:0e8701c3a3b5$258e32b0$a501280a@.phx.gbl...
> > >> > > > > The memory reach 85% of 2GB in Standard
> > Edition, how could
> > >> > > > > I tell which database is the biggest user?
> > >> > > > >
> > >> > > > >
> > >> > > > > >--Original Message--
> > >> > > > > >Last time I used it was before Veritas took it
> > over so
> > >> > > > > I'm not entirely sure
> > >> > > > > >it does. I think tracking IO per db would be
> > more
> > >> > > > > realistic, not sure what
> > >> > > > > >memory per database means.
> > >> > > > > >
> > >> > > > > >--
> > >> > > > > >HTH
> > >> > > > > >
> > >> > > > > >Jasper Smith (SQL Server MVP)
> > >> > > > > >
> > >> > > > > >I support PASS - the definitive, global
> > >> > > > > >community for SQL Server professionals -
> > >> > > > > >http://www.sqlpass.org
> > >> > > > > >
> > >> > > > > >"Andrew J. Kelly"
> > <sqlmvpnooospam@.shadhawk.com> wrote in
> > >> > > > > message
> > >> > > > > >news:%23QUf9lxoDHA.2000@.TK2MSFTNGP10.phx.gbl...
> > >> > > > > >I thought it might as well but from looking at
> > the web
> > >> > > > > site I couldn't
> > >> > > > > >confirm that. I know it does almost
> > everything else ,
> > >> > > > > just not sure about
> > >> > > > > >splitting the cache on a db level or not.
> > >> > > > > >
> > >> > > > > >--
> > >> > > > > >
> > >> > > > > >Andrew J. Kelly
> > >> > > > > >SQL Server MVP
> > >> > > > > >
> > >> > > > > >
> > >> > > > > >"Jasper Smith" <jasper_smith9@.hotmail.com>
> > wrote in
> > >> > > > > message
> > >> > > > > >news:%23qJeqWxoDHA.2732@.TK2MSFTNGP11.phx.gbl...
> > >> > > > > >> I believe InDepth for SQL Server from
> > Veritas will do
> > >> > > > > this if my memory
> > >> > > > > >> serves me correctly. You could do the same
> > kind of
> > >> > > > > think with Profiler
> > >> > > > > >> (although with greater overhead). What
> > exactly are you
> > >> > > > > trying to measure
> > >> > > > > >> this for ?
> > >> > > > > >>
> > >> > > > > >> --
> > >> > > > > >> HTH
> > >> > > > > >>
> > >> > > > > >> Jasper Smith (SQL Server MVP)
> > >> > > > > >>
> > >> > > > > >> I support PASS - the definitive, global
> > >> > > > > >> community for SQL Server professionals -
> > >> > > > > >> http://www.sqlpass.org
> > >> > > > > >>
> > >> > > > > >> "Tracy Yang" <tyang@.nas.edu> wrote in message
> > >> > > > > >> news:10cc01c3a2f2$d5b5c270
> > $a401280a@.phx.gbl...
> > >> > > > > >> Does anybody know if there is a tool to tell
> > the memory
> > >> > > > > >> usage per database? I need the tool to
> > create a report
> > >> > > > > and
> > >> > > > > >> analyze it to see memory usage for each
> > database.
> > >> > > > > >>
> > >> > > > > >>
> > >> > > > > >>
> > >> > > > > >>
> > >> > > > > >
> > >> > > > > >
> > >> > > > > >
> > >> > > > > >.
> > >> > > > > >
> > >> > > >
> > >> > > >
> > >> > >
> > >> > >
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> > >.
> > >
>