SQL Server Performance Forum – Threads Archive

CPU running 100 percent

Hi All, I have a database where it has 13 tables and 13 stored procedures to insert data to all these tables. Basically all the SP’s do the same thing .. it will chek if the data exists , if not it will insert the data. On the said database during the day there will be around a Million Inserts thru out the day and the data files are on RAID 5. We are experiecing a 100% CPU during the day and the processor Queue Length is very high.
If I see the Transactions/per sec in Perfmon , I see an average of 80 tps( which I feel its not very high) . we have 5 servers which connects to SQL Server with one login to Pump the data to SQL Server. It opens up around 70 connections and all the connections will be executing one of the 13 stored Procedures. Can anyone point me in a right direction as to where to start looking as to why the CPU is at 100 percnt.
This is the skeleton for all the 13 Stored Procedures.. CREATE PROCEDURE usp_insTCanMsg16
Input Parameters , — Input parameters will be the Columns Values
@riStatus INTOUTPUT
/*
0 – Success
-1 Error
1 – Dulicate
*/
AS
SET NOCOUNT ON
BEGIN
IF EXISTS (SELECT STMT)
BEGIN
SELECT @riStatus = 1
INSERT INTO TCancelMsgException
(
)
VALUES
(
)
END
ELSE
BEGIN
BEGIN TRAN CanMsg
INSERT INTO TCancelMsg
(
)
VALUES
(
) IF @@Error != 0
BEGIN
ROLLBACK TRAN CanMsg
SELECT @riStatus = -1
END
ELSE
BEGIN
COMMIT TRAN CanMsg
SELECT @riStatus = 0
END
END
END
Thx
Venu

Hi ya, run sqlprofiler to capture batch<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tmtcomplete and sp<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tmtcomplete with a duration > 100ms. For any statements found check indexing. your general design does double the amount of work that SQL needs to do, instead you could ensure that there is aunique index on your key, insert the row and then pick up the duplicate row message in the application Cheers Twan

How many dups do you usually have? If there are many that will cause sp to be recompiled (= high cpu) each time exection path differs from cached one. You can avoid it by puting inserts in both table into separate sp. Twan suggested better solution. My recommendation is backup plan in case you are not able to affect application design.

Hi ya, I don’t quite follow the reasoning here? A recompile won’t be caused by different parameters or the proc being more suited to a different plan? It would be recompiled if the content in the table changed drastically or if temporary tables were created, or if the objects referred to are dropped and recreated, etc.? Cheers
Twan

If you have code like: if (condition) exec if_query else exec else_query_different_then_if_query I believe that only execution plan for one branch is cached (last used branch). So if next time you have to use other branch, new plan has to be generated. By exec query I don’t mean exec proc. If you call proc in each branch their exec plan would be cached and that would mitigate the problem. At least this is what I read somewhere. To be honest I tested the behavior in case of @Variable = <value> condition which might not be the case here. However, I expect the same behavior. Anyway, if you know that I am wrong don’t hesitate to let me know. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Hi ya, a proc with an if/else won’t cause a partial compile of the proc, nor a reompile. SQL will compile up the whole lot. It is not THAT smart <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> However if you have parameters/variables that are used within a statement and the value of those change wildly, then it may be worth splitting the proc into sub-procs. This isn’t because of recompile problems, but because of problems with a plan being compiled only upon the first execution. any parameter values are used to determine the most optimal plan and any variables are replaced by ‘magic values’ to work out the optimal plan. In some cases calling a proc with recompile can help, in others creating different procs for different optimal plans may help. An example of this may be a pair of date parameters which can be a wide range or a narrow range. You may want to have two procs, one that is called when passing a wide range of dates, the other when passing a narrow range of dates. Both could still have optimal plans and the parent proc can be called without the app being concerned about the quirks of the underlying sql server ok rambling now Cheers Twan

Thanks Guys for all the Responces.
We don’t have lot of Duplicates on the database But still we need to check it and its by design.
we have two stored procedures which takes more than 100 ms. But both the table has indexes. One table has a Column ‘TDID’ which is an Integer and it has Clustered Index(PK). when I do IF EXISTS ( Select Stmt ). It does a Clustered SCAN. If I use the INDEX hint , the logical reads has a big number compared to with out using the INDEX hint.
And the other table has a Primary Key on Four Columns which are all interger Fields. Even this goes for a Clustered SCAN. How to make the EXISTs CLAUSE to use INDEX Seek in this Scenario Venu

not sure, but perhaps try to ensure that you don’t do select *, instead use the key column…? or create a unique non-clustered index on the same field (if you get stuck and need a workaround…) Cheers
Twan

I don’t do a SELECT * … Moreover don’t you think creating a non clustered Index on the same column will have a over head of a BOOKMARK LOOKUP. Venu

<blockquote id="quote">quote:<hr height="1" noshade id="quote">a proc with an if/else won’t cause a partial compile of the proc, nor a reompile.<hr height="1" noshade id="quote"></blockquote id="quote">You made me do a homework. I read ms articles about recompilation (I didn’t do it for a very long time) and it looked like you might be right. I was not persuaded, so I run a test. And guess what? You are right! :| It is funny (or maybe not) that we have rule in company (and no, rule existed before I came) that we have to solve branches like I described and official explanation is "because of recompilation". I also remember reading article suggesting the same thing. Too bad I can’t find it now. That reminds me to another myth, this one created and/or supported by Microsoft about how to choose clustered index. Anyway, thank you for opening my eyes. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Almost forgot, I remember testing it with condition @variable = <value>, but in that case @variable must had been sp input parameter.

Hi Venu, if the nonclustered index contains all of the columns being referred to (either because they’re explicitly in the index or because they are columns in the clustered index (which are appended behind the scenes to every non-clustered index) ) then there is no bookmark lookup if SQL is doing a clustered index scan then the if exists ought to be referring to columns that are not in the index itself…? mmarovic, yeah i don’t know where that myth came from. The first time I heard it was on SQLServer 4.2 and I remember testing it then and disproving it… It still comes up quite regularly, glad to have made you a non-believer <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

]]>