May 18, 2005 at 2:10 am
Hi there,
I experience a massive performanceproblem with a monthly DB-process involving 100 mlj of records. All of a sudden the process takes 6 to 8 times longer to run, which makes it running for days instead of 12 hours. There are many things not efficient in the SP's used in the process, but 1 thing I noticed is the CXPacket wait type, which seems to be continuously xausing problems, which it did not 2 months ago. Now I have googled the internet for solutions, and I can't seem to find any. I can find explainations on paralellism, hints on applying the latest servicepacks, lower the paralellism config, check indexing and-so-on, but no step-by-step solution on how to handle this wait type.
How do other ppl solve these problems? Suppose I lower the paralellism value, what consequences are there? I can imagine, that using more processors is a good thing; if I 'turn off' the surpluss processors, I can imagine, that other queries, where there are no problems, will run slower.
Any hints apreciated.
Greetz,
Hans Brouwer
May 18, 2005 at 7:45 am
First, you have to find out which queries are running slow and parallel in those SPs that are used by monthly DB-process.
You can then add query hint "maxdop number" in those queries for force queries to run with low paralellism number.
How much memory do you have in server and how much has allocated to SQL Server?
Longer queries usually benefit from parallel plans but not always be that way.
May 18, 2005 at 8:38 am
Hey Allen,
Tnx for your response. This process is running on SQL Server 2000 Standard Edition sp3A with 3Gb internal RAM. SQL Server takes as much as it needs, max 2Gb for SE.
Since there are a lot of virtual tables involved, I don't think maxdop can help me out here. I know, not a good solution to use with the massive number of data involved. Anyhow, that is how it is, changing the virtual tables will be a headache and a lot of redtape.
What I am looking for is a strategy on how to act, what steps to take to minimize or neutralize this issue. I am aware of paralellism and what it does, how to chance it. What I have not found, for instance, is what overall effect the lowering of the number of CPU used will have. And, for instance, is there some general check to find out, if queries will cause CXPacket problems?
Tnx again,
Greetz,
Hans Brouwer
May 18, 2005 at 9:20 am
For OLTP system, We always suggest not to use parallel execution, in this way, server will has more available CPU resources to serve many concurrent transactions requests.
By select * from sysprocesses, you should be able to notice which active SPIDs are running and waitting for what type resources. If you see many CXPacket waittype, you have to either decrease the number of CPUs for paralleism or increase the cost of threshold for parallel to reduce the contention of processors usage.
Overall system performance need to be monitored, the slow performance of parallel query could also be caused by bottleneck on I/O subsystem and lack of sufficient memory. You should see high CPUs usage from either tasks manager or performance monitor.
Did the monthly process run fast in parallel before or actually it ran fast without parallel?
May 19, 2005 at 12:14 am
Tnx again for your info, Allen.
On I/O problem: that surely is the case. The server in question is configured with RAID 5, not the best config for bulk data actions. I have advised to use RAID 1 or 10, but the organization managing the infrastructure have only 1 serverconfiguration, nomatter what impact a specific RAIDconfig has. This specific system is not vulnarable for dataloss: the monthly run is the only time major chances on data are done. The run recreates a complete dataset, so even a database is completely lost and if there is no backup, we can have a new dataset up and running within a day, which is acceptable to the users.
However this may be true, Untill 2 months ago this run took only 12 hours, which was acceptable and within specs.
Now however I run into this problem. I will take your suggestion in advise.
Am I to understand that lowering the number of CPUs for paralellism has no significant effect on overall performance? Also, paralellism has it's advantages, and this monthly process consists of a lot of queries. Only in a specific SP I notice the CXPacket problem, which is also to most heavily used SP. Currently I am busy trying to optimize what is happening in this SP, hopefully it will help solving the issue. I wonder if lowering the CPUs for paralellism will degrade performance of other queries?
As you may have guessed, I have no previous experience with this specific problem. Tnx again,
Greetz,
Hans Brouwer
May 19, 2005 at 2:47 am
Just my experience from a couple of years ago. I had problem with CXPACKET waittype in my DWH processing (loading data to Analysis services from 100 million row table). It got stuck and never completed. I tried unsuccessfully many things, what in the end solved the problem was updating statistics on the tables involved just before processing began. Maybe you have a different problem but it might be worth trying.
May 19, 2005 at 6:22 am
I think you are in right direction to look into the SP.
Adjust number of CPUs for paralleism could affect some of queries, You need to monitor them clolsely. But in overall, if you already high resources usages in CPU and disk I/O, lowering it will help the performance.
May 20, 2005 at 12:44 am
Tnx for the response guys. Martin, I will surely try your suggestion.
Greetz,
Hans Brouwer
December 31, 2008 at 8:39 am
Hi,
I have gone through the discussion and was great to know many things in it.
I am new to this parallelism and cxpacket and would like to know basics about that.
what it does and its advantages and dis-advantages?
how to change parallelism and anything crusial to know about parallelism.
any notes will be appreciated.
thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply