After migrating from Xeon X5470 to X5560 processors, we observed significant performance degradation in SQL Server 2008 R2 despite similar processor specifications. The key symptoms include:

-- Top wait stats showing CXPACKET dominance
SELECT TOP 5 wait_type, wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY signal_wait_time_ms DESC

The wait statistics reveal excessive CXPACKET waits (65.82% of total waits) indicating parallelism-related bottlenecks. This manifests when:

• Queries default to parallel execution without MAXDOP hints
• Hyperthreading creates logical processors that may confuse SQL Server's query optimizer
• Thread scheduling overhead exceeds benefits from parallel execution

Our testing showed hyperthreading can hurt performance in OLTP workloads with these characteristics:

-- Check current scheduler utilization
SELECT scheduler_id, cpu_id, is_online, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'

Key findings when hyperthreading is enabled:

• Increased context switching (visible in SOS_SCHEDULER_YIELD waits)
• False perception of available CPU resources leading to excessive parallelism
• Reduced per-thread cache efficiency

We implemented these changes which improved performance by 37%:

-- Recommended configuration for parallel-heavy workloads
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'max degree of parallelism', 4; -- Half physical cores
RECONFIGURE;
sp_configure 'cost threshold for parallelism', 50; -- Higher than default
RECONFIGURE;

-- Disable hyperthreading at BIOS level
-- (No T-SQL command - must be done in server hardware configuration)

Before disabling hyperthreading completely, consider these intermediate steps:

-- Use query hints for problematic queries
SELECT * FROM LargeTable WITH (MAXDOP = 2)

-- Configure Resource Governor to limit parallelism
CREATE WORKLOAD GROUP LimitedParallelism
WITH (MAX_DOP = 2);

After implementation, monitor these key metrics:

-- Post-implementation validation query
SELECT 
    wait_type,
    wait_time_ms,
    wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS wait_percentage
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('CXPACKET','SOS_SCHEDULER_YIELD','LATCH_EX')
ORDER BY wait_time_ms DESC;

After migrating from X5470 to X5560 processors (both 3.33GHz quad-core Xeons) with hyperthreading enabled, we're seeing:

• Consistently high CPU utilization (80-90%)
• Query performance degradation (2-3x slower execution times)
• Significant CXPACKET waits (65% of total wait time)
• Elevated SOS_SCHEDULER_YIELD waits (4.45%)

The key diagnostic query revealing our core problem:

SELECT 
    wait_type,
    wait_time_ms AS total_wait_time,
    wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS pct,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP')
ORDER BY wait_time_ms DESC;

Our results show CXPACKET accounting for 65.82% of wait time, suggesting:

• Excessive parallelism due to default MAXDOP settings
• Potential thread oversubscription from hyperthreading
• Unbalanced workload distribution across cores

Modern Intel Xeon processors (like our X5560) present two logical cores per physical core. For OLTP workloads like ours:

• Hyperthreading can increase context switching
• May exacerbate CXPACKET waits due to scheduler pressure
• Can cause misleading CPU utilization metrics

Option 1: Disable Hyperthreading (Recommended First Step)

-- Verify logical vs physical cores before change
SELECT scheduler_id, cpu_id, status, is_online 
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE';

Option 2: Adjust Parallelism Settings

-- Reduce MAXDOP from default (0) to match physical cores
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

-- Set cost threshold to prevent trivial parallel plans
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

After implementing both changes:

Essential queries to verify improvements:

-- Check active parallel queries
SELECT 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.logical_reads,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'running';