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 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