|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > CPU bottleneck
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Sylvain 2002-11-28, 8:23 am |
| Hi all,
I have a big problem with a sql server 2000 on which the dedicated CPU stay
always above 90%.
It happens since the time that we migrate this server from another one. The
new one configuration is the following:
- 4*XEON 700 Mhz, 2Gb de RAM and SAN connection (it should be ok !!!)
The new instance is linked to an application that sniff computer on the
network and save these info on the DB (a lot of transaction read/write)
Before on the old server (PIII 450 396 Mo RAM)we never had this behaviour.
In order to secure other instance on the new server we can dedicate only one
processor and 800 Mo of RAM....
So if you have any idea ......
| |
| Allan Mitchell 2002-11-28, 8:23 am |
| Have you run sp_updatestats in the database ?
Can you see what is causing the CPU spikes ?
--
Allan Mitchell (Microsoft SQL Server MVP)
www.SQLDTS.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Sylvain" <smorice@amadeus.net> wrote in message
news:uCUQaEulCHA.2032@tkmsftngp02...
> Hi all,
>
> I have a big problem with a sql server 2000 on which the dedicated CPU
stay
> always above 90%.
>
> It happens since the time that we migrate this server from another one.
The
> new one configuration is the following:
> - 4*XEON 700 Mhz, 2Gb de RAM and SAN connection (it should be ok !!!)
> The new instance is linked to an application that sniff computer on the
> network and save these info on the DB (a lot of transaction read/write)
>
> Before on the old server (PIII 450 396 Mo RAM)we never had this behaviour.
>
> In order to secure other instance on the new server we can dedicate only
one
> processor and 800 Mo of RAM....
>
> So if you have any idea ......
>
>
>
>
>
| |
| Sylvain 2002-11-28, 8:23 am |
| I've already ran an update of the statistics ....
About the CPU spikes the only thing I see is that it comes from the
instance, mssql server process about 100 %.....(CPU Queue 3 or 4 at maximum)
\Sylvain
"Allan Mitchell" <allan@no-spam.SQLDTS.com> wrote in message
news:ewEheIulCHA.1412@tkmsftngp04...
> Have you run sp_updatestats in the database ?
> Can you see what is causing the CPU spikes ?
>
>
>
> --
>
>
>
> Allan Mitchell (Microsoft SQL Server MVP)
> www.SQLDTS.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
>
>
> "Sylvain" <smorice@amadeus.net> wrote in message
> news:uCUQaEulCHA.2032@tkmsftngp02...
> > Hi all,
> >
> > I have a big problem with a sql server 2000 on which the dedicated CPU
> stay
> > always above 90%.
> >
> > It happens since the time that we migrate this server from another one.
> The
> > new one configuration is the following:
> > - 4*XEON 700 Mhz, 2Gb de RAM and SAN connection (it should be ok
!!!)
> > The new instance is linked to an application that sniff computer on the
> > network and save these info on the DB (a lot of transaction read/write)
> >
> > Before on the old server (PIII 450 396 Mo RAM)we never had this
behaviour.
> >
> > In order to secure other instance on the new server we can dedicate only
> one
> > processor and 800 Mo of RAM....
> >
> > So if you have any idea ......
> >
> >
> >
> >
> >
>
>
| |
|
|
A long shot, but if you went from a single proc to a quad when the problem
started, try setting the parallelism to 1 instead of 0 (default).
"Sylvain" <smorice@amadeus.net> wrote in message
news:uCUQaEulCHA.2032@tkmsftngp02...
> Hi all,
>
> I have a big problem with a sql server 2000 on which the dedicated CPU
stay
> always above 90%.
>
> It happens since the time that we migrate this server from another one.
The
> new one configuration is the following:
> - 4*XEON 700 Mhz, 2Gb de RAM and SAN connection (it should be ok !!!)
> The new instance is linked to an application that sniff computer on the
> network and save these info on the DB (a lot of transaction read/write)
>
> Before on the old server (PIII 450 396 Mo RAM)we never had this behaviour.
>
> In order to secure other instance on the new server we can dedicate only
one
> processor and 800 Mo of RAM....
>
> So if you have any idea ......
>
>
>
>
>
| |
| Jasper Smith 2002-11-28, 12:23 pm |
| Here's a little procedure that will help identify the
spid's consuming the most resources in a given period
You can then use DBCC INPUTBUFFER(spid) or
Profiler to see what they are doing.
use master
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_spidtop10]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[sp_spidtop10]
GO
CREATE PROCEDURE sp_spidtop10
(
@order varchar(3) ='CPU',
@spid int = NULL,
@interval int = NULL
)
AS
/*
Procedure to show top 10 resource using SPID's
based on a passed in order value (default CPU)
or view the resource usage of one spid, for a
period of 30 seconds (default)
Change Description Author Date
Initial Creation J Smith 10 Sep 2001
*/
SET NOCOUNT ON
DECLARE @cinterval char(8)
IF @interval IS NOT NULL
BEGIN
IF @interval > 59 SET @interval = 59 /* max period */
SET @cinterval = '00:00:' + CAST(@interval as char(2))
END
ELSE
BEGIN
SET @cinterval = '00:00:30' /* default period */
END
CREATE TABLE #thread (RUN INT NOT NULL,
SPID INT NOT NULL,
THREAD INT NULL,
STATUS varchar(30) NOT NULL,
[IO] INT NOT NULL,
CPU INT NOT NULL,
MEM INT NOT NULL,
LOCKS int NOT NULL,
BLK INT NOT NULL
CONSTRAINT PK_thread1 PRIMARY KEY CLUSTERED
(RUN,SPID))
/* Initial snapshot */
INSERT #thread
SELECT 1,pr.spid,pr.kpid,pr.status,pr.physical_io,pr.cpu,
pr.memusage,ISNULL(count(lk.spid),0),pr.blocked
FROM master.dbo.sysprocesses pr
LEFT OUTER JOIN master.dbo.syslocks lk ON pr.spid = lk.spid
WHERE pr.spid>=50 AND pr.spid<>@@SPID
AND (@spid is NULL OR pr.spid=@spid)
GROUP BY pr.spid,pr.kpid,pr.status,pr.physical_io,
pr.cpu,pr.memusage,pr.blocked
/* controlled by @interval parameter */
WAITFOR DELAY @cinterval
/* Secondary snapshot */
INSERT #thread
SELECT 2,pr.spid,pr.kpid,pr.status,pr.physical_io,pr.cpu,
pr.memusage,ISNULL(count(lk.spid),0),pr.blocked
FROM master.dbo.sysprocesses pr
LEFT OUTER JOIN master.dbo.syslocks lk ON pr.spid = lk.spid
WHERE pr.spid>=50 AND pr.spid<>@@SPID
AND (@spid is NULL OR pr.spid=@spid)
GROUP BY pr.spid,pr.kpid,pr.status,pr.physical_io,
pr.cpu,pr.memusage,pr.blocked
/* Get the top 10 ordered by @order (default CPU) */
SELECT TOP 10 T.*
FROM(
SELECT a.SPID,b.THREAD,b.STATUS,(b.[IO]-a.[IO]) as 'IO',
(b.CPU-a.CPU) as 'CPU',(b.MEM-a.MEM) as 'MEM',
b.LOCKS,b.BLK
FROM #thread a
JOIN #thread b
ON a.SPID=b.SPID and a.RUN=1 and b.RUN=2) T
ORDER BY CASE @order
WHEN 'IO' THEN [IO]
WHEN 'CPU' THEN CPU
WHEN 'MEM' THEN MEM
WHEN 'LCK' THEN LOCKS
ELSE CPU END DESC
DROP TABLE #thread
RETURN
GO
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Sylvain" <smorice@amadeus.net> wrote in message
news:uifNoZulCHA.1924@tkmsftngp04...
> I've already ran an update of the statistics ....
>
> About the CPU spikes the only thing I see is that it comes from the
> instance, mssql server process about 100 %.....(CPU Queue 3 or 4 at
maximum)
>
> \Sylvain
> "Allan Mitchell" <allan@no-spam.SQLDTS.com> wrote in message
> news:ewEheIulCHA.1412@tkmsftngp04...
> > Have you run sp_updatestats in the database ?
> > Can you see what is causing the CPU spikes ?
> >
> >
> >
> > --
> >
> >
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > www.SQLDTS.com
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> >
> >
> > "Sylvain" <smorice@amadeus.net> wrote in message
> > news:uCUQaEulCHA.2032@tkmsftngp02...
> > > Hi all,
> > >
> > > I have a big problem with a sql server 2000 on which the dedicated CPU
> > stay
> > > always above 90%.
> > >
> > > It happens since the time that we migrate this server from another
one.
> > The
> > > new one configuration is the following:
> > > - 4*XEON 700 Mhz, 2Gb de RAM and SAN connection (it should be ok
> !!!)
> > > The new instance is linked to an application that sniff computer on
the[c
olor=darkred]
> > > network and save these info on the DB (a lot of transaction[/color]
read/ write)
> > >
> > > Before on the old server (PIII 450 396 Mo RAM)we never had this
> behaviour.
> > >
> > > In order to secure other instance on the new server we can dedicate
only
> > one
> > > processor and 800 Mo of RAM....
> > >
> > > So if you have any idea ......
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>
|
|
|
|
|