Instantly CPU on TOP, just returns with rebuild of AN index

Question

Hi people,

I have a Windows Cluster with 3 instances of SQL Servers, conected true a Fiber Channel network to a HP Storage 4400 and 6400.

All my instances are running ok, with a normal e acceptable CPU, but in one instance we have a big problem.

In one instance, that runs a relational application, this server is exclusive to this instance and 95% of utilization is for just one base that have 200gb.

My server is a HP blade with 132gb RAM, 2 Intel Xeon E5540 2,53 Gb, 16 cores.

Normally, this server is has 15% CPU average, every sunday I run a rebuild and update statistics of all index.

We have an application on a Weblogic server running against this base and 3 Webmethods server with many services connecting in this base, but, despite this, the applications run very well, but, eventually, the CPU goes on top of server, and stays on 100% for ever.

This base have one table with around 3 billions of lines of the last 6 months that is related with other table with registration data of companies with around 160k lines.

Normally, when the problem happens, the only solution we have is to rebuild the primary key index of the data of companies table, doing this action, the CPU turns back to 10% and the applications back to running normally.

Have anyone a problem relative with this? This problem are causing a big nuisance to our company.

Thanks.

Answer 1

Possibly because you "suddenly" get a bad execution plan, consuming lots of resources, and rebuilding the index gives you new statistics and from that a better plan. See this for tips: http://www.sommarskog.se/

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 2

Hey,

Thanks for the answer, but what you mean with "suddenly", in the last occurrence I try to run the update statistics command on the exclusive table, but the problem was not solved, OK, maybe I waited very little time, because, 2 minutes later I already run the rebuild index on the PK of the table.

We have a great control about the querys running, and in the moment that the CPU is high, is always the same querys, I run a profiler, and a take the same querys that are running normally.

Actually, my ideia of comment here, is to see if anyone already had this crazy problem, my servers have 3 years of use, and along of this time we have made a lot of changes on hardware, cpu, hard drivers, network, a lot o SQL and Windows updates.

I will try to capture in the next event the times and querys that are running slowly and compare the time of the same querys running when the systems is OK.

Thanks.

Answer 3

It most certainly is an executiom plan problem (unless it is a blocking problem, of course), especially since rebuild index solves it. Only way to know is to catch the actual execution plan in a trace and compare a "good" one to a "bad" one.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 4

Ok,
The problem not occurred any more time.
But I will wait and comment.

Thanks.

Answer 5

Hey Guys,

I keep having the same problem, but now I have some useful informations, see below:

First, we have changed the MAXDOP parameter from 0 to 4, but we have some problems in maintenance then we return to 0, but when this parameter was on 4, we had the same CPU problema.

Second, we find the "auto update statistics asynchronously" option that was on default "false", we change to "true", because we had the suspect that when a lot of queries are using the same tables, the SQL was unable to update the statistics, but 1 hour after change this parameter, we had the same CPU problem.

But, when we was looking the SQL, we find the same query a lot of times, this query is used multiple times by a lot of threads from the Weblogic server in the same time, like 150 executions simultaneously on the same table.

So, we start to question about the OUTPUT and INSERTED impact.

UPDATE nextEntryIn SET STATUS = @P1 , DATE_TIME_PROCESSING = getDate()  

OUTPUT  INSERTED.COD_QUEUE_IN,  INSERTED.PRIORITY,  .... few more fields  

FROM (SELECT TOP 5 COD_QUEUE_IN,  PRIORITY,  .... the same few more fields 
      FROM QUEUE_IN 
      WHERE STATUS = @P2   AND CONSUMER = @P3   AND PRIORITY = @P4  
      ORDER BY COD_QUEUE_IN ASC) 
AS nextEntryIn

Again, when the CPU goes high, we make the rebuild on this tables and the data of companies table.

Thanks for any help.