CPU usage by DB

I have a SQL server cluster that hosts about 40 db. The CPU utilization went
from an average of 6% to 30%. I did not add any more DBs during this
timeframe. I can see from the running processes that it is sqlservr process
using it.
How can I determine which DBs are using the most CPU?
thankstime to dust of SQLProfiler and start investigating.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1-F89167C26BBC@.microsoft.com...
>I have a SQL server cluster that hosts about 40 db. The CPU utilization
>went
> from an average of 6% to 30%. I did not add any more DBs during this
> timeframe. I can see from the running processes that it is sqlservr
> process
> using it.
> How can I determine which DBs are using the most CPU?
> thanks|||Not in SQL 2000. No idea what new toys are available for 2005.
Try a PSSDiag to see what all is happening:
http://support.microsoft.com/kb/830232/en-us
--
Kevin Hill
3NF Consulting
www.3nf-inc.com
http://kevin3nf.blogspot.com
"jason" <jason@.discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1-F89167C26BBC@.microsoft.com...
>I have a SQL server cluster that hosts about 40 db. The CPU utilization
>went
> from an average of 6% to 30%. I did not add any more DBs during this
> timeframe. I can see from the running processes that it is sqlservr
> process
> using it.
> How can I determine which DBs are using the most CPU?
> thanks|||Hi Jason,
My understanding of your issue is that:
Your SQL Server cluster hosts about 40 databases. You found that the CPU
utilization went from an average 6% to 30%. You wanted to see which DBs are
busily using CPU.
If I have misunderstood, please let me know.
You may use SQL Profiler and PSSDiag to monitor your SQL Server
performance. However there is a direct way on this issue:
use master
Go
select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 on
t1.dbid=t2.dbid order by t1.dbid
Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
physical_io, memusage, status, and cmd.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles thank you for the assistance.
i ran the query. can you tell me more about what these indicators mean.
e.g. i sorted by cpu. does a large number there definitely indicate its
consuming a lot of cpu?
when i read about it in books online, it says that is the cumulitive CPU
time. to me that means it could have been running for days. so if i look at
the other columns, it show this process logged in oct 10th, over 2 weeks ago.
so is this large cpu value adding up all the time this process has used on
the cpu for the last 2 weeks?
or is there somewhere in books online that i can learn more about what i am
looking at?
"Charles Wang[MSFT]" wrote:
> Hi Jason,
> My understanding of your issue is that:
> Your SQL Server cluster hosts about 40 databases. You found that the CPU
> utilization went from an average 6% to 30%. You wanted to see which DBs are
> busily using CPU.
> If I have misunderstood, please let me know.
> You may use SQL Profiler and PSSDiag to monitor your SQL Server
> performance. However there is a direct way on this issue:
> use master
> Go
> select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 on
> t1.dbid=t2.dbid order by t1.dbid
> Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
> physical_io, memusage, status, and cmd.
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Hi Jason,
I am sorry for not saying clearly in my first reply. Yes, it is cumulitive
time, but it is helpful.
First, you can see the status and cmd columns to check the current
executing command and the current status of the process that the database
used. Second, you can know which database is the busiest from the
cumulitive time since it has been started; Also, you can use subtraction to
compute the CPU time cost, physical IO read and write times by running the
query twice during a time frame, then you will know which database is the
busiest during the time frame.
Hope this helpful. For the columns meaning, you can refer to sysprocesses
in SQL Server Books Online.
If you have any other questions or cnocerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Jason,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support

CPU usage by DB

I have a SQL server cluster that hosts about 40 db. The CPU utilization wen
t
from an average of 6% to 30%. I did not add any more DBs during this
timeframe. I can see from the running processes that it is sqlservr process
using it.
How can I determine which DBs are using the most CPU?
thankstime to dust of SQLProfiler and start investigating.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1-F89167C26BBC@.microsoft.com...
>I have a SQL server cluster that hosts about 40 db. The CPU utilization
>went
> from an average of 6% to 30%. I did not add any more DBs during this
> timeframe. I can see from the running processes that it is sqlservr
> process
> using it.
> How can I determine which DBs are using the most CPU?
> thanks|||Not in SQL 2000. No idea what new toys are available for 2005.
Try a PSSDiag to see what all is happening:
http://support.microsoft.com/kb/830232/en-us
Kevin Hill
3NF Consulting
www.3nf-inc.com
http://kevin3nf.blogspot.com
"jason" <jason@.discussions.microsoft.com> wrote in message
news:6A92892A-7952-4A9A-9BA1-F89167C26BBC@.microsoft.com...
>I have a SQL server cluster that hosts about 40 db. The CPU utilization
>went
> from an average of 6% to 30%. I did not add any more DBs during this
> timeframe. I can see from the running processes that it is sqlservr
> process
> using it.
> How can I determine which DBs are using the most CPU?
> thanks|||Hi Jason,
My understanding of your issue is that:
Your SQL Server cluster hosts about 40 databases. You found that the CPU
utilization went from an average 6% to 30%. You wanted to see which DBs are
busily using CPU.
If I have misunderstood, please let me know.
You may use SQL Profiler and PSSDiag to monitor your SQL Server
performance. However there is a direct way on this issue:
use master
Go
select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 on
t1.dbid=t2.dbid order by t1.dbid
Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
physical_io, memusage, status, and cmd.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles thank you for the assistance.
i ran the query. can you tell me more about what these indicators mean.
e.g. i sorted by cpu. does a large number there definitely indicate its
consuming a lot of cpu?
when i read about it in books online, it says that is the cumulitive CPU
time. to me that means it could have been running for days. so if i look a
t
the other columns, it show this process logged in oct 10th, over 2 weeks ago
.
so is this large cpu value adding up all the time this process has used on
the cpu for the last 2 weeks?
or is there somewhere in books online that i can learn more about what i am
looking at?
"Charles Wang[MSFT]" wrote:

> Hi Jason,
> My understanding of your issue is that:
> Your SQL Server cluster hosts about 40 databases. You found that the CPU
> utilization went from an average 6% to 30%. You wanted to see which DBs ar
e
> busily using CPU.
> If I have misunderstood, please let me know.
> You may use SQL Profiler and PSSDiag to monitor your SQL Server
> performance. However there is a direct way on this issue:
> use master
> Go
> select t2.name as dbname, t1.* from sysprocesses t1 join sysdatabases t2 o
n
> t1.dbid=t2.dbid order by t1.dbid
> Run the SQL statement and pay attention on the columns: lastwaittype, cpu,
> physical_io, memusage, status, and cmd.
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>|||Hi Jason,
I am sorry for not saying clearly in my first reply. Yes, it is cumulitive
time, but it is helpful.
First, you can see the status and cmd columns to check the current
executing command and the current status of the process that the database
used. Second, you can know which database is the busiest from the
cumulitive time since it has been started; Also, you can use subtraction to
compute the CPU time cost, physical IO read and write times by running the
query twice during a time frame, then you will know which database is the
busiest during the time frame.
Hope this helpful. For the columns meaning, you can refer to sysprocesses
in SQL Server Books Online.
If you have any other questions or cnocerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Jason,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support

CPU usage and troubleshoot (sp_who2, profiling)

(I have only little knowledge in mssql)
Hello,
We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
sticked at 100% during few hours. The dbs are used by customers, we
didn't designed them, we just manage the system until middleware level
(SQL Server)
We would like to trouble this CPU problem. I use some tricks found on
the net, as the sp_who2, which gave me some details on the commands
run. I have a lot of sleeping process with AWAITING COMMAND; the
sp_who2 reported me also CPU and IO usage but I don't know which
units.
The output is at http://rafb.net/p/DWkMQO35.html
Can I have some pointers on how use th output of sp_who2 to track
performance problems
Thanks for your help
And if you are using SQL2005 SP2, you would find the newly released dashboard
reports rather useful in finding queries that may be consuming excessive CPU.
Linchi
"tabarnak@.gmail.com" wrote:

> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>
|||On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> Are you on SQL 2000.
> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
> approach I take is normally, to analyse these figures , ascertain whether
> it's an IO or network problem . Then drill down to the db level
> --
> Jack Vamvas
(being away for some days)
Hi Jack,
I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
CXPACKET, LATCH_EX and PAGEIOLATCH_SH
Am I right, could you give some explanatations and hints.
is it possible to clear these stats, in order to have them on a short
sessions, along-side of the profiler for instance.
Thanks
Regards

CPU usage and troubleshoot (sp_who2, profiling)

(I have only little knowledge in mssql)
Hello,
We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
sticked at 100% during few hours. The dbs are used by customers, we
didn't designed them, we just manage the system until middleware level
(SQL Server)
We would like to trouble this CPU problem. I use some tricks found on
the net, as the sp_who2, which gave me some details on the commands
run. I have a lot of sleeping process with AWAITING COMMAND; the
sp_who2 reported me also CPU and IO usage but I don't know which
units.
The output is at http://rafb.net/p/DWkMQO35.html
Can I have some pointers on how use th output of sp_who2 to track
performance problems
Thanks for your helpAre you on SQL 2000.
Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
approach I take is normally, to analyse these figures , ascertain whether
it's an IO or network problem . Then drill down to the db level
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">uk it jobs</a>
"tabarnak@.gmail.com" <baptiste.millemathias@.gmail.com> wrote in message
news:1178200041.682518.327170@.n59g2000hsh.googlegroups.com...
> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>|||And if you are using SQL2005 SP2, you would find the newly released dashboar
d
reports rather useful in finding queries that may be consuming excessive CPU
.
Linchi
"tabarnak@.gmail.com" wrote:

> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>|||On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> Are you on SQL 2000.
> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
> approach I take is normally, to analyse these figures , ascertain whether
> it's an IO or network problem . Then drill down to the db level
> --
> Jack Vamvas
(being away for some days)
Hi Jack,
I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
CXPACKET, LATCH_EX and PAGEIOLATCH_SH
Am I right, could you give some explanatations and hints.
is it possible to clear these stats, in order to have them on a short
sessions, along-side of the profiler for instance.
Thanks
Regards|||I went to the link but got a 404
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/sql
<a href="http://links.10026.com/?link=http://www.itjobfeed.com/sql"> uk it jobs</a>
"tabarnak@.gmail.com" <baptiste.millemathias@.gmail.com> wrote in message
news:1178703761.778779.133060@.e51g2000hsg.googlegroups.com...
> On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> (being away for some days)
> Hi Jack,
> I uploaded the output in http://rafb.net/p/6rXNqc96.html.
> CXPACKET, LATCH_EX and PAGEIOLATCH_SH
> Am I right, could you give some explanatations and hints.
> is it possible to clear these stats, in order to have them on a short
> sessions, along-side of the profiler for instance.
> Thanks
> Regards
>

CPU usage and troubleshoot (sp_who2, profiling)

(I have only little knowledge in mssql)
Hello,
We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
sticked at 100% during few hours. The dbs are used by customers, we
didn't designed them, we just manage the system until middleware level
(SQL Server)
We would like to trouble this CPU problem. I use some tricks found on
the net, as the sp_who2, which gave me some details on the commands
run. I have a lot of sleeping process with AWAITING COMMAND; the
sp_who2 reported me also CPU and IO usage but I don't know which
units.
The output is at http://rafb.net/p/DWkMQO35.html
Can I have some pointers on how use th output of sp_who2 to track
performance problems
Thanks for your helpAre you on SQL 2000.
Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
approach I take is normally, to analyse these figures , ascertain whether
it's an IO or network problem . Then drill down to the db level
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">uk it jobs</a>
"tabarnak@.gmail.com" <baptiste.millemathias@.gmail.com> wrote in message
news:1178200041.682518.327170@.n59g2000hsh.googlegroups.com...
> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>|||And if you are using SQL2005 SP2, you would find the newly released dashboard
reports rather useful in finding queries that may be consuming excessive CPU.
Linchi
"tabarnak@.gmail.com" wrote:
> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>|||On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> Are you on SQL 2000.
> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
> approach I take is normally, to analyse these figures , ascertain whether
> it's an IO or network problem . Then drill down to the db level
> --
> Jack Vamvas
(being away for some days)
Hi Jack,
I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
CXPACKET, LATCH_EX and PAGEIOLATCH_SH
Am I right, could you give some explanatations and hints.
is it possible to clear these stats, in order to have them on a short
sessions, along-side of the profiler for instance.
Thanks
Regards|||I went to the link but got a 404
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/sql
<a href="http://links.10026.com/?link=">http://www.itjobfeed.com/sql"> uk it jobs</a>
"tabarnak@.gmail.com" <baptiste.millemathias@.gmail.com> wrote in message
news:1178703761.778779.133060@.e51g2000hsg.googlegroups.com...
> On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
>> Are you on SQL 2000.
>> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site.
>> The
>> approach I take is normally, to analyse these figures , ascertain whether
>> it's an IO or network problem . Then drill down to the db level
>> --
>> Jack Vamvas
> (being away for some days)
> Hi Jack,
> I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
> CXPACKET, LATCH_EX and PAGEIOLATCH_SH
> Am I right, could you give some explanatations and hints.
> is it possible to clear these stats, in order to have them on a short
> sessions, along-side of the profiler for instance.
> Thanks
> Regards
>

CPU usage 50-70%

hi All,
we have cluster active-passive MSSQL 2000 Server + SP3.
The server experience high CPU usage, the counter % Processor Time is often
continually at 50 - 100 %.
Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
Counter LockWaitTime(ms) sometimes 200.
Are above values high? I mean whether Lock Request/sec might generate such a
heave load on CPU?
any suggestions'
thx for help
--
PITPit,
It depends. :-)
This could be perfectly normal. Then again it might not. These types of
questions can be answered very easily if you benchmark and baseline your
systems regularly. A third party enterprise monitoring tool would be
great for this. "high" or "low" are comparative terms to your normal
mode of operation. They should not be compared to mine or anyone else's
database system.
I would recommend you start collecting performance information so that
these questions can be answered more confidently in future. You could
also produce some nice graphs to show your boss how much of a proactive
DBA you are and earn some brownie points towards your next pay rise. ;-)
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Pit wrote:
> hi All,
> we have cluster active-passive MSSQL 2000 Server + SP3.
> The server experience high CPU usage, the counter % Processor Time is often
> continually at 50 - 100 %.
> Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
> Counter LockWaitTime(ms) sometimes 200.
> Are above values high? I mean whether Lock Request/sec might generate such a
> heave load on CPU?
> any suggestions'
> thx for help

CPU usage 50-70%

hi All,
we have cluster active-passive MSSQL 2000 Server + SP3.
The server experience high CPU usage, the counter % Processor Time is often
continually at 50 - 100 %.
Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
Counter LockWaitTime(ms) sometimes 200.
Are above values high? I mean whether Lock Request/sec might generate such a
heave load on CPU?
any suggestions?
thx for help
PIT
Pit,
It depends. :-)
This could be perfectly normal. Then again it might not. These types of
questions can be answered very easily if you benchmark and baseline your
systems regularly. A third party enterprise monitoring tool would be
great for this. "high" or "low" are comparative terms to your normal
mode of operation. They should not be compared to mine or anyone else's
database system.
I would recommend you start collecting performance information so that
these questions can be answered more confidently in future. You could
also produce some nice graphs to show your boss how much of a proactive
DBA you are and earn some brownie points towards your next pay rise. ;-)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Pit wrote:
> hi All,
> we have cluster active-passive MSSQL 2000 Server + SP3.
> The server experience high CPU usage, the counter % Processor Time is often
> continually at 50 - 100 %.
> Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
> Counter LockWaitTime(ms) sometimes 200.
> Are above values high? I mean whether Lock Request/sec might generate such a
> heave load on CPU?
> any suggestions?
> thx for help

CPU usage 50-70%

hi All,
we have cluster active-passive MSSQL 2000 Server + SP3.
The server experience high CPU usage, the counter % Processor Time is often
continually at 50 - 100 %.
Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
Counter LockWaitTime(ms) sometimes 200.
Are above values high? I mean whether Lock Request/sec might generate such a
heave load on CPU?
any suggestions'
thx for help
--
PITPit,
It depends. :-)
This could be perfectly normal. Then again it might not. These types of
questions can be answered very easily if you benchmark and baseline your
systems regularly. A third party enterprise monitoring tool would be
great for this. "high" or "low" are comparative terms to your normal
mode of operation. They should not be compared to mine or anyone else's
database system.
I would recommend you start collecting performance information so that
these questions can be answered more confidently in future. You could
also produce some nice graphs to show your boss how much of a proactive
DBA you are and earn some brownie points towards your next pay rise. ;-)
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Pit wrote:
> hi All,
> we have cluster active-passive MSSQL 2000 Server + SP3.
> The server experience high CPU usage, the counter % Processor Time is ofte
n
> continually at 50 - 100 %.
> Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
> Counter LockWaitTime(ms) sometimes 200.
> Are above values high? I mean whether Lock Request/sec might generate such
a
> heave load on CPU?
> any suggestions'
> thx for help