Hi,
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server
.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that mos
t
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100
%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com
Showing posts with label processor. Show all posts
Showing posts with label processor. Show all posts
Thursday, March 8, 2012
CPU usage on SQL server
Hi,
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that most
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that most
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com
CPU usage on SQL server
Hi,
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that most
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?
Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com
I have a quad processor clustered database server that is experiencing
significant performance problems. There are multiple user databases on ther
server, and the issues manifest themselves in different ways. We have had
users unable to save to a database to complete slow down of the whole server.
The issues seem to be more prevalent in the morning, but we have had them
throughout the day as well. In monitoring the CPU usage we noticed that most
of the activity is going through only 1 of the CPU's. CPU 0 can peak at 100%
utilisation, and the other 3 have little or no activity at all. There has
been no changes to any settings and the SQL Server install is pretty
standard. Does anyone have any ideas?
Meg wrote:
> Hi,
> I have a quad processor clustered database server that is experiencing
> significant performance problems. There are multiple user databases
> on ther server, and the issues manifest themselves in different ways.
> We have had users unable to save to a database to complete slow down
> of the whole server. The issues seem to be more prevalent in the
> morning, but we have had them throughout the day as well. In
> monitoring the CPU usage we noticed that most of the activity is
> going through only 1 of the CPU's. CPU 0 can peak at 100%
> utilisation, and the other 3 have little or no activity at all.
> There has been no changes to any settings and the SQL Server install
> is pretty standard. Does anyone have any ideas?
Have you assigned SQL Server all CPUs? Are you using MAXDOP 1 option in
your queries or have you set the "max degree of parallelism" option on
the server? I would think SQL Server would use all CPUs even if MAXDOP 1
were used when running multiple queries. It's probably some bad query
that is running on a single CPU that is causing the CPU spike. Many
query operations run on a single CPU. And blocking is probably the
problem, not the CPU per se. Although the high CPU is probably causing
extended locking and blocking problems.
You need to profile your database and see how your queries are
performing and figure out which ones are running for long periods and
locking resources required by other SPIDs.
David Gugick
Imceda Software
www.imceda.com
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
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
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
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
CPU usage
In Sql Server 7.0, you could tell it to only use 1 processor out of 4 on a server. Can you do the same thing in Sql Server 2005?Yes. This mechanism hasn't changed since 7.0 except the option is dynamic now. See "affinity mask" option in the sp_configure system sp topic in Books Online.
Subscribe to:
Posts (Atom)