finding memory, cpu and io percentage

Is there a way to convert the numbers under memory, cpu and I/O usage into some kind of percentage or number of KB? So far I have been getting by with comparing the numbers to eachother to get a relative amount, but it would be nice to have some more soli
d numbers.
How/Where are you obtaining the memory, cpu, and I/O usage numbers?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:9F6A55CB-DE72-4D8E-8ED5-37930A460C54@.microsoft.com...
> Is there a way to convert the numbers under memory, cpu and I/O usage into
some kind of percentage or number of KB? So far I have been getting by with
comparing the numbers to eachother to get a relative amount, but it would be
nice to have some more solid numbers.
|||Sorry - I wasn't very clear - I mean the numbers from the Process Info under Current Activity in EM.
Thanks
"Gregory A. Larsen" wrote:

> How/Where are you obtaining the memory, cpu, and I/O usage numbers?
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:9F6A55CB-DE72-4D8E-8ED5-37930A460C54@.microsoft.com...
> some kind of percentage or number of KB? So far I have been getting by with
> comparing the numbers to eachother to get a relative amount, but it would be
> nice to have some more solid numbers.
>
>
|||Those numbers basically come from the sysprocesses table but you might want
to think about using profiler and perfmon to track you performance stats
instead.
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:E8DAECFF-47C6-49A9-95DA-13FDF725342E@.microsoft.com...
> Sorry - I wasn't very clear - I mean the numbers from the Process Info
under Current Activity in EM.
> Thanks
> "Gregory A. Larsen" wrote:
>
> ----
--
> ----
--[vbcol=seagreen]
into[vbcol=seagreen]
with[vbcol=seagreen]
would be[vbcol=seagreen]
|||I have not found a way to track memory in the profiler - is there a way that I missed?
"Andrew J. Kelly" wrote:

> Those numbers basically come from the sysprocesses table but you might want
> to think about using profiler and perfmon to track you performance stats
> instead.
> http://www.microsoft.com/sql/techinf...perftuning.asp
> Performance WP's
> http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
> http://www.sql-server-performance.co...ance_audit.asp
> Hardware Performance CheckList
> http://www.sql-server-performance.co...mance_tips.asp
> SQL 2000 Performance tuning tips
> http://www.support.microsoft.com/?id=q224587 Troubleshooting App
> Performance
> http://msdn.microsoft.com/library/de...rfmon_24u1.asp
> Disk Monitoring
>
> --
> Andrew J. Kelly SQL MVP
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:E8DAECFF-47C6-49A9-95DA-13FDF725342E@.microsoft.com...
> under Current Activity in EM.
> --
> --
> into
> with
> would be
>
>
|||No but you can track it with Perfmon just not on a per connection basis.
Sysprocesses has this but I don't know how useful this is. Is there
something in particular you are attempting to do?
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:B5C4F377-570B-49E3-8013-CC2EC102D284@.microsoft.com...
> I have not found a way to track memory in the profiler - is there a way
that I missed?[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
want[vbcol=seagreen]
counters[vbcol=seagreen]
http://www.sql-server-performance.co...mance_tips.asp[vbcol=seagreen]
http://msdn.microsoft.com/library/de...rfmon_24u1.asp[vbcol=seagreen]
> ----
> ----
usage[vbcol=seagreen]
by[vbcol=seagreen]

finding memory, cpu and io percentage

Is there a way to convert the numbers under memory, cpu and I/O usage into s
ome kind of percentage or number of KB? So far I have been getting by with c
omparing the numbers to eachother to get a relative amount, but it would be
nice to have some more soli
d numbers.How/Where are you obtaining the memory, cpu, and I/O usage numbers?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:9F6A55CB-DE72-4D8E-8ED5-37930A460C54@.microsoft.com...
> Is there a way to convert the numbers under memory, cpu and I/O usage into
some kind of percentage or number of KB? So far I have been getting by with
comparing the numbers to eachother to get a relative amount, but it would be
nice to have some more solid numbers.|||Sorry - I wasn't very clear - I mean the numbers from the Process Info under
Current Activity in EM.
Thanks
"Gregory A. Larsen" wrote:

> How/Where are you obtaining the memory, cpu, and I/O usage numbers?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:9F6A55CB-DE72-4D8E-8ED5-37930A460C54@.microsoft.com...
> some kind of percentage or number of KB? So far I have been getting by wit
h
> comparing the numbers to eachother to get a relative amount, but it would
be
> nice to have some more solid numbers.
>
>|||Those numbers basically come from the sysprocesses table but you might want
to think about using profiler and perfmon to track you performance stats
instead.
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:E8DAECFF-47C6-49A9-95DA-13FDF725342E@.microsoft.com...
> Sorry - I wasn't very clear - I mean the numbers from the Process Info
under Current Activity in EM.
> Thanks
> "Gregory A. Larsen" wrote:
>
> ----
--
> ----
--[vbcol=seagreen]
into[vbcol=seagreen]
with[vbcol=seagreen]
would be[vbcol=seagreen]|||I have not found a way to track memory in the profiler - is there a way that
I missed?
"Andrew J. Kelly" wrote:

> Those numbers basically come from the sysprocesses table but you might wan
t
> to think about using profiler and perfmon to track you performance stats
> instead.
> http://www.microsoft.com/sql/techin.../perftuning.asp
> Performance WP's
> http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
> http://www.sql-server-performance.c...mance_audit.asp
> hardware Performance CheckList
> http://www.sql-server-performance.c...rmance_tips.asp
> SQL 2000 Performance tuning tips
> http://www.support.microsoft.com/?id=q224587 Troubleshooting App
> Performance
> http://msdn.microsoft.com/library/d...
fmon_24u1.asp
> Disk Monitoring
>
> --
> Andrew J. Kelly SQL MVP
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:E8DAECFF-47C6-49A9-95DA-13FDF725342E@.microsoft.com...
> under Current Activity in EM.
> --
> --
> into
> with
> would be
>
>|||No but you can track it with Perfmon just not on a per connection basis.
Sysprocesses has this but I don't know how useful this is. Is there
something in particular you are attempting to do?
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:B5C4F377-570B-49E3-8013-CC2EC102D284@.microsoft.com...
> I have not found a way to track memory in the profiler - is there a way
that I missed?[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
>
want[vbcol=seagreen]
counters[vbcol=seagreen]
http://www.sql-server-performance.c...rmance_tips.asp[vbcol=seagreen]
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp[/url
][vbcol=seagreen]
> ----
> ----
usage[vbcol=seagreen]
by[vbcol=seagreen]sql

Finding CPU usage by database on a server

We often have many databases on a single SQL Server instance. Is there a
simple way to determine resource usage (mainly CPU) on a per database basis?
For example, we have databases A through Z. I want to be able to determine
that database A is taking up 5% of the CPU, while Y is taking up 95%.
Anybody have any experience along these lines?
Thanks in advanceKevin Lavelle wrote:
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
If your users tend be in a single database for the duration of their
connections, the easiest way is to capture the Disconnect event from
Profiler which aggregates all the CPU, Duration, Reads, and Writes for
the connection. A simple query can total everything up by database
(Database ID).
If that's not the case, and users tend to switch databases a lot, the
second easiest way is to capture the SQL:BatchCompleted and
RPC:Completed events (leave TextData out and capture only performance
related columns and the DatabaseID). You can then total up the results
by SQL execution and group by database id.
In either case, using a server-side trace is better. But for the first
option, using Profiler may be sufficient.
David Gugick
Imceda Software
www.imceda.com|||It is not easily achievable. You may profile the SQL server and look at the
CPU column based on grouping of databases. But that is very crude and rough
estimate.
"Kevin Lavelle" <kevinlavelle@.community.nospam> wrote in message
news:BA3B738C-3745-41D5-9CA6-4C264367BEFF@.microsoft.com...
> We often have many databases on a single SQL Server instance. Is there a
> simple way to determine resource usage (mainly CPU) on a per database
> basis?
> For example, we have databases A through Z. I want to be able to
> determine
> that database A is taking up 5% of the CPU, while Y is taking up 95%.
> Anybody have any experience along these lines?
> Thanks in advance|||Hi Kevin,
One of our products within the Quest Central suite, Performance Analysis, does exactly this. Plus, it also gives you performance information on a variety of other dimensions, such as application, tables, filegroups, etc. Check it out at http://www.quest.com/quest_central_for_sql_server/index.asp.
There's a free trial version available on the website.
HTH,
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
>

Finding CPU usage by database on a server

We often have many databases on a single SQL Server instance. Is there a
simple way to determine resource usage (mainly CPU) on a per database basis?
For example, we have databases A through Z. I want to be able to determine
that database A is taking up 5% of the CPU, while Y is taking up 95%.
Anybody have any experience along these lines?
Thanks in advance
Kevin Lavelle wrote:
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
If your users tend be in a single database for the duration of their
connections, the easiest way is to capture the Disconnect event from
Profiler which aggregates all the CPU, Duration, Reads, and Writes for
the connection. A simple query can total everything up by database
(Database ID).
If that's not the case, and users tend to switch databases a lot, the
second easiest way is to capture the SQL:BatchCompleted and
RPC:Completed events (leave TextData out and capture only performance
related columns and the DatabaseID). You can then total up the results
by SQL execution and group by database id.
In either case, using a server-side trace is better. But for the first
option, using Profiler may be sufficient.
David Gugick
Imceda Software
www.imceda.com
|||It is not easily achievable. You may profile the SQL server and look at the
CPU column based on grouping of databases. But that is very crude and rough
estimate.
"Kevin Lavelle" <kevinlavelle@.community.nospam> wrote in message
news:BA3B738C-3745-41D5-9CA6-4C264367BEFF@.microsoft.com...
> We often have many databases on a single SQL Server instance. Is there a
> simple way to determine resource usage (mainly CPU) on a per database
> basis?
> For example, we have databases A through Z. I want to be able to
> determine
> that database A is taking up 5% of the CPU, while Y is taking up 95%.
> Anybody have any experience along these lines?
> Thanks in advance
|||Hi Kevin,
One of our products within the Quest Central suite, Performance Analysis, does exactly this. Plus, it also gives you performance information on a variety of other dimensions, such as application, tables, filegroups, etc. Check it out at http://www.quest
.com/quest_central_for_sql_server/index.asp.
There's a free trial version available on the website.
HTH,
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)

> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
>

Find the table name corresponding to a page

I am trying to find the table name associated with page#.
This is the query executed.
select * from master..sysprocesses where cpu > 2000
and this is the result.
14:1:3068040 [WAITRESOURCE]
[select * from sysindexes where id = 3068040 does not return any data]
TIA
Manoj Kumar
try select db_name(14) for the database
and select object_name(3068040) for the table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Manoj Kumar" <ManojKumar@.discussions.microsoft.com> wrote in message
news:0A0E5BE7-1969-436B-A116-D91E39D9305F@.microsoft.com...
>I am trying to find the table name associated with page#.
> This is the query executed.
> select * from master..sysprocesses where cpu > 2000
> and this is the result.
> 14:1:3068040 [WAITRESOURCE]
> [select * from sysindexes where id = 3068040 does not return any data]
> TIA
> --
> Manoj Kumar
|||Manoj, a combination of sp_who2 and sp_lock will give you the locked
resource (or the corresponding current activity screens in EM).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Manoj, you can use DBCC PAGE to show information on page # 3068040.
In the DBCC page output - you will see an object_id that you can tie up to
sysobjects...
"Manoj Kumar" wrote:

> I am trying to find the table name associated with page#.
> This is the query executed.
> select * from master..sysprocesses where cpu > 2000
> and this is the result.
> 14:1:3068040 [WAITRESOURCE]
> [select * from sysindexes where id = 3068040 does not return any data]
> TIA
> --
> Manoj Kumar