SQL goes to 100% CPU

[DebiJo]

I have a 3rd party package, that usually takes about 30% of my 2 processor SQL server. On occasion, the server will go to 100%. The vendor has told me to go into query analyzer to this one stored procedure, click edit, then the green arrow (does that recompile it?) and then go to the current processes and kill every instance of this stored procedure running. I do this, and the utilization returns to normal. They say they have no idea why it does this, but the recompile will always fix it. Does this make sense to anyone? Is this for real, that something happens to the stored proc and a recompile is necessary or it will lock up?

 

[Catadmin]

The green arrow executes whatever code is in the QA window. It does not recompile a stored procudure unless the SP has been saved with the "With Recompile" option. SPs only recompile if they have that option set, if you shutdown and restart SQL Server or if you use the sp_recompile System Proc.

I don't know why the vendor is having you start the SP manually and then kill all instances of it. Unless other instances of it are already running and the vendor has no clue how to kill those previous instances without re-running it.

What does the SP do? Is it a vendor related SP or a Proc created by your own employer?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[DebiJo]

It's a vendor provided SP. It uses the largest database we have. I hit the green arrow today and then killed 9 instances of it and the cpu returned to normal. This is just a pain in the rear to do when you server is pegged at 100%. Usually takes me 30+ minutes of watching the screens paint.

It just looks like a big select statement with inner joins and left joins and where's, case, group by and sort by. Where would I see the "with compile" option?

Thanks!
Debi

 

[Catadmin]

It should actually be listed at the end of the PROC definition.

Syntax for Create Procedure

CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

If it doesn't say it, it doesn't do it. When things get slow like this, run sp_who2 to see what ProcessID the SP is (and you can use DBCC InputBuffer to verify if it is the SP in question). Then you can just run a Kill command on that process without having to restart the SP all over again.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???