SQL Ran at 100% 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 2000 Forums

Transact-SQL (2000)

SQL Ran at 100% CPU

Author

Topic

Daveyboy527
Starting Member

4 Posts

Posted - 2010-07-30 : 06:49:06

Hi all,

Hope someone may be able to help me. Just yesterday I had a call telling me that one of the sites that I look after was unavailable. I looked into the issue and found the CPU to be running at 100% with SQL being the culprit.

It would seem that our hits counter was causing the issue. I'm by no means a SQL guru so was hoping that someone may shed some light on this for me. This procedure has been running fine for 3 months but only decided to lock up yesterday. See procedure below:

CREATE PROCEDURE usp_FB_Count_Hits_nr
(
@PageID int,
@UserID int
)
AS
BEGIN

Declare @TableID int
Set @TableID = DATEPART(month, GETDATE())

Declare @currentHits int, @CentreID int, @CentreHome int, @CentreVarChar varchar(10), @CentreConst varchar(5), @centrehits int, @currenthits7 int

Set @CentreConst = 1
Set @CentreID = Left(@PageID,2)

Set @CentreVarchar = (Select Cast(@CentreID as varchar(10)) + Cast(@CentreConst as varchar(10)))

Set @CentreHome = (select cast(@CentreVarchar as integer))

--Set @CentreVarChar = Select Cast(@CentreHome as Varchar(5)) + Cast(@centreConst as varchar(5))
--Set @CentreHome = Select Cast(@CentreVarChar as integer)

SET NOCOUNT ON
IF @TableID = 1

BEGIN
Set @currenthits = (Select Hits from FB_Hits_1 where PageID = @PageID)

If Exists(Select PageID from FB_Hits_1 where PageID = @PageID)
Begin
Update FB_Hits_1 Set Hits = @currenthits+1 where PageID = @PageID
End
Else
Begin
Insert into FB_Hits_1 (PageID, Hits) Values (@PageID, 1)
End

If NOT Exists(SELECT PageID FROM FB_Visited_1 WHERE UserID=@UserID AND PageID=@PageId)
Begin
INSERT INTO FB_Visited_1 (UserID, PageID) VALUES(@UserID,@PageId)
End

If NOT Exists(SELECT PageID FROM FB_Visited_1 WHERE UserID=@UserID AND PageID=@CentreHome)
Begin
INSERT INTO FB_Visited_1 (UserID, PageID) VALUES(@UserID,@CentreHome)
End

END

ELSE IF @TableID = 2
BEGIN
Set @currenthits = (Select Hits from FB_Hits_2 where PageID = @PageID)

If Exists(Select PageID from FB_Hits_2 where PageID = @PageID)
Begin
Update FB_Hits_2 Set Hits = @currenthits+1 where PageID = @PageID
End
Else
Begin
Insert into FB_Hits_2 (PageID, Hits) Values (@PageID, 1)
End
If NOT Exists(SELECT PageID FROM FB_Visited_2 WHERE UserID=@UserID AND PageID=@PageId)
Begin
INSERT INTO FB_Visited_2 (UserID, PageID) VALUES(@UserID,@PageId)
End

If NOT Exists(SELECT PageID FROM FB_Visited_2 WHERE UserID=@UserID AND PageID=@CentreHome)
Begin
INSERT INTO FB_Visited_2 (UserID, PageID) VALUES(@UserID,@CentreHome)
End
END
ELSE IF @TableID = 3
BEGIN
Set @currenthits = (Select Hits from FB_Hits_3 where PageID = @PageID)

If Exists(Select PageID from FB_Hits_3 where PageID = @PageID)
Begin
Update FB_Hits_3 Set Hits = @currenthits+1 where PageID = @PageID
End
Else
Begin
Insert into FB_Hits_3 (PageID, Hits) Values (@PageID, 1)
End
If NOT Exists(SELECT PageID FROM FB_Visited_3 WHERE UserID=@UserID AND PageID=@PageId)
Begin
INSERT INTO FB_Visited_3 (UserID, PageID) VALUES(@UserID,@PageId)
End

If NOT Exists(SELECT PageID FROM FB_Visited_3 WHERE UserID=@UserID AND PageID=@CentreHome)
Begin
INSERT INTO FB_Visited_3 (UserID, PageID) VALUES(@UserID,@CentreHome)
End
END
ELSE IF @TableID = 4
BEGIN
Set @currenthits = (Select Hits from FB_Hits_4 where PageID = @PageID)

If Exists(Select PageID from FB_Hits_4 where PageID = @PageID)
Begin
Update FB_Hits_4 Set Hits = @currenthits+1 where PageID = @PageID
End
Else
Begin
Insert into FB_Hits_4 (PageID, Hits) Values (@PageID, 1)
End

If NOT Exists(SELECT PageID FROM FB_Visited_4 WHERE UserID=@UserID AND PageID=@PageId)
Begin
INSERT INTO FB_Visited_4 (UserID, PageID) VALUES(@UserID,@PageId)
End

If NOT Exists(SELECT PageID FROM FB_Visited_4 WHERE UserID=@UserID AND PageID=@CentreHome)
Begin
INSERT INTO FB_Visited_4 (UserID, PageID) VALUES(@UserID,@CentreHome)
End
END
ELSE IF @TableID = 5
BEGIN
Set @currenthits = (Select Hits from FB_Hits_5 where PageID = @PageID)

If Exists(Select PageID from FB_Hits_5 where PageID = @PageID)
Begin
Update FB_Hits_5 Set Hits = @currenthits+1 where PageID = @PageID
End
Else
Begin
Insert into FB_Hits_5 (PageID, Hits) Values (@PageID, 1)
End

Set @centrehits = (Select Hits from FB_Hits_5 where PageID = @CentreHome)

If Exists(Select PageID from FB_Hits_5 where PageID = @CentreHome)
Begin
Update FB_Hits_5 Set Hits = @centrehits+1 where PageID = @CentreHome
End
Else
Begin
Insert into FB_Hits_5 (PageID, Hits) Values (@CentreHome, 1)
End

If NOT Exists(SELECT PageID FROM FB_Visited_5 WHERE UserID=@UserID AND PageID=@PageId)
Begin
INSERT INTO FB_Visited_5 (UserID, PageID) VALUES(@UserID,@PageId)
End

If NOT Exists(SELECT PageID FROM FB_Visited_5 WHERE UserID=@UserID AND PageID=@CentreHome)
Begin
INSERT INTO FB_Visited_5 (UserID, PageID) VALUES(@UserID,@CentreHome)
End
END

ELSE IF @TableID = 6
BEGIN
Set @currenthits = (Select Hits from FB_Hits_6 where PageID = @PageID)

If Exists(Select PageID from FB_Hits_6 where PageID = @PageID)
Begin
Update FB_Hits_6 Set Hits = @currenthits+1 where PageID = @PageID
End
Else
Begin
Insert into FB_Hits_6 (PageID, Hits) Values (@PageID, 1)
End
If NOT Exists(SELECT PageID FROM FB_Visited_6 WHERE UserID=@UserID AND PageID=@PageId)
Begin
INSERT INTO FB_Visited_6 (UserID, PageID) VALUES(@UserID,@PageId)
End

If NOT Exists(SELECT PageID FROM FB_Visited_6 WHERE UserID=@UserID AND PageID=@CentreHome)
Begin
INSERT INTO FB_Visited_6 (UserID, PageID) VALUES(@UserID,@CentreHome)
End
END

ELSE IF @TableID = 7
BEGIN

Set @currenthits =(Select Hits from FB_Hits_7 where PageID = @PageID)

If Exists(Select PageID from FB_Hits_7 where PageID = @PageID)

Update FB_Hits_7 Set Hits = @currenthits+1 where PageID = @PageID

Else
Begin
Insert into FB_Hits_7 (PageID, Hits) Values (@PageID, 1)
End

If NOT Exists(SELECT PageID FROM FB_Visited_7 WHERE UserID=@UserID AND PageID=@PageId)
Begin
INSERT INTO FB_Visited_7 (UserID, PageID) VALUES(@UserID,@PageId)
End

If NOT Exists(SELECT PageID FROM FB_Visited_7 WHERE UserID=@UserID AND PageID=@CentreHome)
Begin
INSERT INTO FB_Visited_7 (UserID, PageID) VALUES(@UserID,@CentreHome)
End

END

ELSE IF @TableID = 8
BEGIN
Set @currenthits = (Select Hits from FB_Hits_8 where PageID = @PageID)

If Exists(Select PageID from FB_Hits_8 where PageID = @PageID)
Begin
Update FB_Hits_8 Set Hits = @currenthits+1 where PageID = @PageID
End
Else
Begin
Insert into FB_Hits_8 (PageID, Hits) Values (@PageID, 1)
End
If NOT Exists(SELECT PageID FROM FB_Visited_8 WHERE UserID=@UserID AND PageID=@PageId)
Begin
INSERT INTO FB_Visited_8 (UserID, PageID) VALUES(@UserID,@PageId)
End

If NOT Exists(SELECT PageID FROM FB_Visited_8 WHERE UserID=@UserID AND PageID=@CentreHome)
Begin
INSERT INTO FB_Visited_8 (UserID, PageID) VALUES(@UserID,@CentreHome)
End
END

ELSE IF @TableID = 9
BEGIN
Set @currenthits = (Select Hits from FB_Hits_9 where PageID = @PageID)

If Exists(Select PageID from FB_Hits_9 where PageID = @PageID)
Begin
Update FB_Hits_9 Set Hits = @currenthits+1 where PageID = @PageID
End
Else
Begin
Insert into FB_Hits_9 (PageID, Hits) Values (@PageID, 1)
End
If NOT Exists(SELECT PageID FROM FB_Visited_9 WHERE UserID=@UserID AND PageID=@PageId)
Begin
INSERT INTO FB_Visited_9 (UserID, PageID) VALUES(@UserID,@PageId)
End

If NOT Exists(SELECT PageID FROM FB_Visited_9 WHERE UserID=@UserID AND PageID=@CentreHome)
Begin
INSERT INTO FB_Visited_9 (UserID, PageID) VALUES(@UserID,@CentreHome)
End
END

ELSE IF @TableID = 10
BEGIN
Set @currenthits = (Select Hits from FB_Hits_10 where PageID = @PageID)

If Exists(Select PageID from FB_Hits_10 where PageID = @PageID)

Begin
Update FB_Hits_10 Set Hits = @currenthits+1 where PageID = @PageID
End
Else
Begin
Insert into FB_Hits_10 (PageID, Hits) Values (@PageID, 1)
End
If NOT Exists(SELECT PageID FROM FB_Visited_10 WHERE UserID=@UserID AND PageID=@PageId)
Begin
INSERT INTO FB_Visited_10 (UserID, PageID) VALUES(@UserID,@PageId)
End

If NOT Exists(SELECT PageID FROM FB_Visited_10 WHERE UserID=@UserID AND PageID=@CentreHome)
Begin
INSERT INTO FB_Visited_10 (UserID, PageID) VALUES(@UserID,@CentreHome)
End
END

ELSE IF @TableID = 11
BEGIN
Set @currenthits = (Select Hits from FB_Hits_11 where PageID = @PageID)

If Exists(Select PageID from FB_Hits_11 where PageID = @PageID)
Begin
Update FB_Hits_11 Set Hits = @currenthits+1 where PageID = @PageID
End
Else
Begin
Insert into FB_Hits_11 (PageID, Hits) Values (@PageID, 1)
End
If NOT Exists(SELECT PageID FROM FB_Visited_11 WHERE UserID=@UserID AND PageID=@PageId)
Begin
INSERT INTO FB_Visited_11 (UserID, PageID) VALUES(@UserID,@PageId)
End

If NOT Exists(SELECT PageID FROM FB_Visited_11 WHERE UserID=@UserID AND PageID=@CentreHome)
Begin
INSERT INTO FB_Visited_11 (UserID, PageID) VALUES(@UserID,@CentreHome)
End
END

ELSE IF @TableID = 12
BEGIN
Set @currenthits = (Select Hits from FB_Hits_12 where PageID = @PageID)

If Exists(Select PageID from FB_Hits_12 where PageID = @PageID)
Begin
Update FB_Hits_12 Set Hits = @currenthits+1 where PageID = @PageID
End
Else
Begin
Insert into FB_Hits_12 (PageID, Hits) Values (@PageID, 1)
End
If NOT Exists(SELECT PageID FROM FB_Visited_12 WHERE UserID=@UserID AND PageID=@PageId)
Begin
INSERT INTO FB_Visited_12 (UserID, PageID) VALUES(@UserID,@PageId)
End

If NOT Exists(SELECT PageID FROM FB_Visited_12 WHERE UserID=@UserID AND PageID=@CentreHome)
Begin
INSERT INTO FB_Visited_12 (UserID, PageID) VALUES(@UserID,@CentreHome)
End
END
SET NOCOUNT OFF
END
GO
-----------------------

Just today have I added the "SET NOCOUNT ON/OFF" to see if it helps. In anyone's experience would the above likely cause an issue?

Many thanks,
Dave

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-07-30 : 07:18:02

1. qualify all tables with dbo.
2. why all the "if not exists"? can you not ensure that these automatically are true?
optional paths may be causing a 'bad execution path' selection problem.

3. what's the logic behing the fb_hits_1,2,3,4,5,6,7,8 tables?
that data model isn't sustainable.
you should really be considering creating a generic "hits" table, with a "table_id" a one of the identifying columns.

Daveyboy527
Starting Member

4 Posts

Posted - 2010-07-30 : 08:15:29

Hi Andrew,

Thanks for your reply.

In answer to your questions/advice:

1) I shall certainly go through and qualify all tables with dbo.

My next response answer questions 2 & 3...

The @TableID checks for the month number. In the current case the month number is 7, so the "hit" gets written to the FB_Hits_7 table. As of 1/8/2010 the data will start writing to the FB_Hits_8 table.

All procedures are called by IIS (.asp files).

In your opinion, would it be worth creating 12 seperate Stored Procs and having IIS choose which SP to call?

Going by my logic (not normally a good thing) you'd end up with the same issue as you still have multiple users trying to update the same record. I'm not sure what best practice is for this?!

Table structure looks like this:

PageID int (PK)
Hits int

Many thanks,
Dave

Kristen
Test

22859 Posts

Posted - 2010-07-30 : 08:26:28

Make sure the indexes are being rebuilt regularly

Personally I would use a single table with a Month Number column (or perhaps a date column and "round" the date to 1st of the month and update that - what happens when you get to the end of the month, do you pre-clear the next table? otherwise you just add to last year's totals. A year+month value would avoid that problem)

Daveyboy527
Starting Member

4 Posts

Posted - 2010-07-30 : 08:32:03

Hi Kristen,

Thanks for your reply.

Yes, we clear down the table at the end of December. Unfortunately its an old system I've inherited so trying to make the most of a bad job until I get a chance to re-write it all.

Thanks,
Dave

Kristen
Test

22859 Posts

Posted - 2010-07-30 : 09:17:47

You could have your Sproc insert into a, new, table with an extra "month" column, and then create 12 views to replace the existing 12 tables - i.e. with the tables' original names - then all existing processes that query the Tables1-12 will continue to work as before.

Daveyboy527
Starting Member

4 Posts

Posted - 2010-07-30 : 09:37:10

Hiya,

Thanks, I'll have a think about that one.

I think my main concern with this is locking. Say that 1000 people visit the site at the same time, they would all be trying to update the 1 record for that page. Is that a likely problem or is SQL intelligent enough to queue the requests?

Dave

Kristen
Test

22859 Posts

Posted - 2010-07-30 : 09:45:14

I don't think it will be a problem, but I think it will be made an awful lot better if the Sproc just says:


BEGIN TRANSACTION

UPDATE MyTable
SET MyCount = MyCount+1
WHERE MyUserID = @UserID AND MyMonthNumber = @MonthNo AND ...

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO MyTable(MyCount, MyUserID, MyMonthNumber) VALUES (1, @UserID, @MonthNo, ...)
END

COMMIT
because that single statement will only lock the record momentarily and not need to do any escalation etc. (I'm assuming it is more likely that a record already exists, than that a record needs creating, hence the UPDATE is first)

Sure, the others will have to get in a queue - but I am not expecting them to wait long.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources