August 30, 2012 at 2:39 pm
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.
August 30, 2012 at 3:06 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2012 at 12:13 pm
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.
August 31, 2012 at 1:52 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2012 at 12:11 pm
Ok,
The problem not occurred any more time.
But I will wait and comment.
Thanks.
October 5, 2012 at 5:57 am
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.
October 5, 2012 at 6:17 am
Did you tried using table hints on the tables? and what is MAXDOP value now?
October 5, 2012 at 6:36 am
Please post new questions in a new thread. Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2012 at 6:46 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply