sql服务器性能提高,sql-server – 禁用超线程将提高我们的SQL Server安装性能

相关：

Current wisdom on SQL Server and Hyperthreading

最近我们将Windows 2008 R2数据库服务器从X5470升级到X5560.理论上,两者的cpu性能非常相似,如果有的话,X5560的速度稍快一些.

但是,sql Server 2008 R2的性能在过去一天左右相当糟糕,并且cpu使用率相当高.

页面预期寿命很长,我们对页面的缓存命中率几乎达到100％,因此内存不是问题.

当我跑：

SELECT * FROM sys.dm_os_wait_stats

order by signal_wait_time_ms desc

我有：

wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms

------------------------------------------------------------ -------------------- -------------------- -------------------- --------------------

XE_TIMER_EVENT 115166 2799125790 30165 2799125065

REQUEST_FOR_DEADLOCK_SEARCH 559393 2799053973 5180 2799053973

SOS_SCHEDULER_YIELD 152289883 189948844 960 189756877

CXPACKET 234638389 2383701040 141334 118796827

SLEEP_TASK 170743505 1525669557 1406 76485386

LATCH_EX 97301008 810738519 1107 55093884

LOGMGR_QUEUE 16525384 2798527632 20751319 4083713

WRITELOG 16850119 18328365 1193 2367880

PAGELATCH_EX 13254618 8524515 11263 1670113

ASYNC_NETWORK_IO 23954146 6981220 7110 1475699

(10 row(s) affected)

我也跑了

-- Isolate top waits for server instance since last restart or statistics clear

WITH Waits AS (

SELECT

wait_type,wait_time_ms / 1000. AS [wait_time_s],100. * wait_time_ms / SUM(wait_time_ms) OVER() AS [pct],ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [rn]

FROM sys.dm_os_wait_stats

WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','sqlTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT','XE_DISPATCHER_JOIN'))

SELECT W1.wait_type,CAST(W1.wait_time_s AS DECIMAL(12,2)) AS wait_time_s,CAST(W1.pct AS DECIMAL(12,2)) AS pct,CAST(SUM(W2.pct) AS DECIMAL(12,2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2 ON W2.rn <= W1.rn

GROUP BY W1.rn,W1.wait_type,W1.wait_time_s,W1.pct

HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

得到了

wait_type wait_time_s pct running_pct

CXPACKET 554821.66 65.82 65.82

LATCH_EX 184123.16 21.84 87.66

SOS_SCHEDULER_YIELD 37541.17 4.45 92.11

PAGEIOLATCH_SH 19018.53 2.26 94.37

FT_IFTSHC_MUTEX 14306.05 1.70 96.07

这显示了大量时间同步涉及并行性的查询(高CXPACKET).另外,有趣的是,许多这些问题查询正在多个内核上执行(我们的代码中没有任何MAXDOP提示)

服务器已经超过一天左右没有负载.我们遇到了与查询执行的大量差异,通常许多查询看起来比我们以前的数据库服务器慢,而且cpu非常高.

禁用超线程是否有助于降低cpu使用率并提高吞吐量？