October 17, 2003 at 12:13 pm
Have a Compaq-Hp Dual processor system running as Virtual Quad <blah, blah, blah> has anyone experienced or noticed excess resource blocking related to a Virtual Quad Server running parallelism?
Jbabington
Jbabington@hotmail.com
October 20, 2003 at 8:00 am
This was removed by the editor as SPAM
October 20, 2003 at 11:48 am
Aside from my moronic misspelling of "Virtual", I have found the following information. Conditions:
·Slow Query Performance
·Deadlocking due to resource
Some of these problems traced to when the optimizer decides to parallelize (instead of serialize) the process, in this case the billing cycle. I believe we've encountered a bug where the parallel processing with PAGEIOLATCH waits cause the process to go into a deadlock, but the disk usage and CPU usage for the process continues to be used with the disk usage at about an 4:1 ratio <on RAID 0+1 it would be around 8:1 ratio> slowing the entire system dramatically thus causing users/ connections to be dropped. I am certain that parallelism is the cause and is in part due to Hyper Threading flaking out on large updates, inserts and could be even selects since the aggregate stream. Since SQL server sees a virtual Quad Server as 4 CPU’s available when technically only two physical CPU’s exist. In some instances Force setting the option could make a slow heavy hashing query take from 1 hour to less than 1 Minute.
Fix – Solutions:
1.If continued issues present themselves with a parallel execution plan, try it with OPTION (MAXDOP 1) to restrict the plan to 1 CPU.
2.Reset the HTT Server Settings (Database Servers Only)
a.There are some serious issues with Hyper-threading and parallel execution plans, if this is applicable, disable HT <server level configuration change>. Also, in high row count operations, the plan may spool to the tempdb; verification of a high-performance disk system for both the main Vision & tempdb data files, and separate disk drive for each heavy use log file. (NOTE: Current RAID 5 Performance Consideration / Limitation). Deadlocks involving parallelism always involve thread or lock resources. Since Parallelism alone cannot cause the deadlock. The information reported by Trace Flags allows trace ability the separate parallel workers and find the other resource contention issues that are causing the deadlock. Treat all parallel threads (for example, threads with the same SPID) as a single unit when tracing the deadlock.
Jbabington
Jbabington@hotmail.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply