Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2005 64-bit 100% CPU Expand / Collapse
Author
Message
Posted Thursday, August 30, 2012 2:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:28 PM
Points: 10, Visits: 84
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.
Post #1352495
Posted Thursday, August 30, 2012 3:06 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
Next time try just updating statistics.

You've given just about nothing to go on there, so it's impossible to say a cause. Fragmentation won't cause that kind of performance degradation, so it's not the fixing of fragmentation that solves the problem. Rebuilding an index also updates statitics causing cached plans to be invalidated. So it's likely either poor stats leading to bad plans, or a bad plan getting into cache which gets cleared out when the stats update invalidates it. No way to tell which though.

Can you at least identify the query(s) that are running slowly? Execution plans would be awesome too.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1352513
Posted Friday, August 31, 2012 12:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:28 PM
Points: 10, Visits: 84
Hey,

Thanks for the quick answer, 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.
Post #1353034
Posted Friday, August 31, 2012 1:52 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
vladimir.guerreiro (8/31/2012)
Actually, my ideia of comment here, is to see if anyone already had this crazy problem,


Sure, many times. All with very different causes.

You've described a very common symptom, there's just about no way to identify a cause from that, there are many, many possible reasons

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.


Query text at a minimum, execution plan and wait stats are highly desired.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1353077
Posted Tuesday, September 4, 2012 12:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:28 PM
Points: 10, Visits: 84
Ok,
The problem not occurred any more time.
But I will wait and comment.

Thanks.
Post #1354107
Posted Friday, October 5, 2012 5:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:28 PM
Points: 10, Visits: 84
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.
Post #1368958
Posted Friday, October 5, 2012 6:17 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 30, 2013 2:09 AM
Points: 3,131, Visits: 1,058
Did you tried using table hints on the tables? and what is MAXDOP value now?


Post #1368972
Posted Friday, October 5, 2012 6:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
Please post new questions in a new thread. Thanks


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1368986
Posted Friday, October 5, 2012 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:28 PM
Points: 10, Visits: 84
SSCrazy,

MAXDOP is MAX DEGREE OF PARALLELISM (http://msdn.microsoft.com/en-us/library/ms181007(v=sql.105).aspx)
A parameter that says to use just the number of seted cores on the server for each query, 0 is no limit, and 4 is 4 cores.

I created a new topic.
http://www.sqlservercentral.com/Forums/Topic1368992-377-1.aspx
Post #1368995
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse