sql is eating up the cpu - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

sql is eating up the cpu

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-02-20 : 07:14:54

how can i figure out why?
it's a brand new high spec computer.

i have sp that is being run probably about 25 times a minute which checks that a records is not a duplicate and inserts... could this be tying up the cpu? how can i figure out what is doing it?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-20 : 07:20:16

Not CPU, but may be result of high I/O. You can check this by adding proper IO related counters to Performance monitor while running the SP. Profiler can also help you to find out culprit on statement level.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-20 : 07:21:11

Set some counters.

Also, is SQL Server the only application on the server?

What service pack level are you at?

Do you have FTI's at all as I have found mssearch.exe can cause problems?

Why are you using a SP to look for duplicates? Do you not have a natural PK? If it is just to supress errors, then perhaps you should do this at the application level.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-02-20 : 07:50:37

i can see in the cpu that it's usinjg 99% of the cpu
how do i run profiler? (this is 2005)

i tried sp_who2 but nothing was blocked?
what do you mean by sent counters?
can you give me a link to the issues that could cause this problem?
i don't want to supress errors as there may be another error and it's important for me to know.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 10:31:06

Or the SP can be made more streamlined?
Post the SP here and maybe we can give some advice how to improve it?

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-02-20 : 12:16:23

sp is as follows:

ALTER PROCEDURE [dbo].[spinsertname]
-- Add the parameters for the stored procedure here
@name varchar(100),
@date datetime,
@picture varchar(100),
@accuracy real,
@picture2 varchar(100),
@picture3 varchar(100),
@imagesexist bit,
@site varchar(100)=''
AS
BEGIN
declare @recexists int,
@countap int,
@countv int,
@p bit,
@v bit

select @permit=0
select @violation=0
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select @recexists=count(id) from images where date=@date and accuracy=@accuracy and name=@name and site=@site and picture=@picture and picture2=@picture2 and picture3=@picture3
if @recexists=0
begin

select @countap=count(name) from ap where name=@name
if @countap>0
select @p=1
select @countv=count(id) from v where name=@name and datediff(day,sent,@date)<14
if @countv>0
select @v=1
insert into images(name,date,accuracy,imagesexist,picture,p,v,picture2,picture3,site)
values(@name,@date,@accuracy,@imagesexist,@picture,@p,@v,@picture2,@picture3,@site)

end
-- Insert statements for procedure here

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 13:04:20

[code]ALTER PROCEDURE dbo.spinsertname
(
@Name VARCHAR(100),
@Date DATETIME,
@Picture VARCHAR(100),
@Accuracy REAL,
@Picture2 VARCHAR(100),
@Picture3 VARCHAR(100),
@ImagesExist BIT,
@Site VARCHAR(100) = ''
AS

SET NOCOUNT ON

DECLARE @p BIT,
@v BIT

IF NOT EXISTS (
SELECT *
FROM Images
WHERE Date = @Date
AND Accuracy = @Accuracy
AND Name = @Name
AND Site = @Site
AND Picture = @Picture
AND Picture2 = @Picture2
AND Picture3 = @Picture3
)
BEGIN
IF EXISTS (
SELECT *
FROM Ap
WHERE Name = @Name
)
SET @P = 1
ELSE
SET @P = 0

IF EXISTS (
SELECT *
FROM V
WHERE Name = @Name
AND Sent >= DATEADD(DAY, -14, @Date)
)
SET @V = 1
ELSE
SET @V = 0

INSERT Images
(
Name,
Date,
Accuracy,
ImagesExist,
Picture,
P,
V,
Picture2,
Picture3,
Site
)
VALUES (
@Name,
@Date,
@Accuracy,
@ImagesExist,
@Picture,
@P,
@V,
@Picture2,
@Picture3,
@Site
)
END[/code]

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-02-20 : 13:17:32

peso you think yours is a faster version then mine?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 15:48:47

Why don't you give it a try?
It should be faster, especially if you have an index over Sent column in V table.

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts