SQL server CPU usage problem after installing SP3a | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL server CPU usage problem after installing SP3a

Hi, I was using a database on SQL Server 2000 for a long time. Before installing SP3a the average cpu usage was about 30% but after installing sp3a SQL Server is using 100% of cpu and the server is really slow now. Any Idea? —
Truly Yours,
Iman G.M.
Is SQL Server the process eating up the CPU? Have you ran Profiler to see what’s causing it? I have installed sp3a numerous times now and never had this. You might want to run UPDATE STATISTICS. You need to identify if it’s just one or two queries causing this though. If it is, run sp_recompile ‘stored_procedure_name’ on them. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
If you do nothing after install sp3a, I suggest to reinstall sp3a.
Luis Martin
Moderator
SQL-Server-Performance.com
Are you on a clustered environment? If so, there is a patch for this and you need to call Microsoft Tech Support and open a ticket to get the patch. It won’t cost anything. If you aren’t on a cluster, try what I said and if that doesn’t work try reinstalling like Luis said. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Hi, Thanks. I reinstalled the SP3a. But SQL is still using about 100% of cpu resource. I’m sure it’s because of SP3a. Is there any way to remove it?
Thanks. —
Truly Yours,
Iman G.M.
Hi again. I checked it again. When I use update query it takes a long time… Any Idea?

Truly Yours,
Iman G.M.
How about update statistics with fullscan?
Luis Martin
Moderator
SQL-Server-Performance.com
Hi Again! I used SQL Check software. These are the results : Ave. Transaction/Sec : 33
Ave. Memory Pages/Sec : 0
Ave. % Processor Time : 98
Ave. Disk Transfer/Sec : 30
Buffer Cache Hit Ratio : 100
User Connections : 28
Ave. Lock Wait Time (ms) : 0
DB Size : 2114MB (7% free)
Ave. Latch Wait Time : 40 (max is 40) —
Truly Yours,
Iman G.M.
Hi luis what is update statistics with full scan? You know I’m not an expert in sql server. I use ASP and ADO to connect to SQL server I don’t know very much about SQL Server commands. You mean I use "Update statistics" in query analyser? —
Truly Yours,
Iman G.M.
Hi Iman, Yes, I mean UPDATE STATISTICS TableName WITH FULLSCAN, for each table or, at least, for those tables using by slow query. If you don´t use WITH FULLSCAN, then the update is 10% witch is defalut, fullscans means 100%
Luis Martin
Moderator
SQL-Server-Performance.com
]]>