Hyper-threading is Intel’s implementation of simultaneous multithreading technology. The performance benefits of using hyper-threading are dependent upon workload. For PeopleSoft applications, it is recommended that you disable hyper-threading for the database server via the BIOS as our lab testing has shown little or no improvement.
Great! The experiment confirms the theory. So does it mean you have to disable HT when using SQL Server? The answer is it really depends on the load and hardware you are using. You have to test your application with HT on and off under heavy loads to understand HT's implications. Keep in mind that not only lazywriter thread can cause slowdown but any thread that performs large memory scan - for example a worker thread that scans large amount of data might be a culprit as well. For some customer applications when disabling HT we saw 10% increase in performance. So make sure that you do your home work before you decide to hyper on not to hyper :-)
Hyperthreading allows a CPU to execute multiple threads simultaneously. Consequently, the CPU throughput increases. A CPU that supports hyperthreading contains two architectural states on a single physical core. Each state acts as a logical CPU for the operating system. However, the two logical CPUs use the same execution resources, so you do not get the performance benefits of using two physical CPUs. 以及以下的描述： 4. Is hyperthreading beneficial for a database server? Why or why not? Answers will vary. Under certain conditions, the performance of a database server may improve if hyperthreading is enabled. However, a hyperthreaded CPU is not as effective as a dual core CPU or two individual CPUs.
今天看到 MVP 的 mailing list 中討論，就微軟的回答，似乎 Hyperthreading 只適用於 OLTP 大量使用者存取，但其語法都很簡單的情況。 若 OLTP 單一 batch 很複雜，或是 DW/DSS/OLAP 以分析為主，需要大量平行運算的的工作，最好都使用實體的 CPU 數(不是主機板上的 socket 數)，其理由為：SQL Server 以所有的 CPU 運算力都相同為前提，設計平行計畫。但 Hyperthreading 的 CPU 並非如此。其建議是： •在語法上採用 MAXDOP <實體 CPU 數>。 •可以針對連接透過 Resource Governer 設定 CPU 數，但非所有版本的 SQL Server 都能使用這項功能。 當然，也可以透過執行個體的 max degree of parallelism 設定
Performance The performance of hyper-threaded environments varies. Conservative testing has shown 10 to 20 percent gains for SQL Server workloads, but the application patterns have a significant affect. You might find that some applications do not receive an increase in performance by taking advantage of hyper-threading. If the physical processors are already saturated, using logical processors can actually reduce the workload achieved. For example, applications that cause high levels of contention can cause decreased performance in a hyper-threaded environment. We recommend that you test your application thoroughly to make sure that a hyper-threaded environment provides you the performance gain that you want versus the purchase of equivalent physical CPUs. Hyper-threading can be very helpful but hyper-threading cannot replace the full power of an additional physical CPU.
步驟01. 在「物件總管」上，連線到指定的執行個體。 步驟02. 展開「安全性」\「登入」。 步驟03. 在「登入」節點上，滑鼠右鍵，選擇「報表」\「標準報表」\「登入失敗」。請參考下圖所示：
步驟01. 在報表上，滑鼠右鍵。 步驟02. 選擇「匯出」\「Excel」。請參考下圖所示：
declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @indx int ;
declare @temp_trace table (
Error int
,StartTime datetime
,NTUserName nvarchar(128)collate database_default
,NTDomainName nvarchar(128) collate database_default
,HostName nvarchar(128) collate database_default
,ApplicationName nvarchar(128) collate database_default
,LoginName nvarchar(128) collate database_default
,SPID int
,ServerName nvarchar(128) collate database_default
,TextData nvarchar (max) collate database_default );
select @curr_tracefilename = path FROM sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename)
select @indx = PATINDEX('%\%', @curr_tracefilename)
set @curr_tracefilename = reverse(@curr_tracefilename)
set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';
insert into @temp_trace
select Error, StartTime, NTUserName, NTDomainName, HostName, ApplicationName, LoginName, SPID, ServerName, TextData
from ::fn_trace_gettable( @base_tracefilename, default )
where EventClass = 20 --signifies login failed
select dense_rank () over (order by S.loginname) loginrank, (dense_rank () over (order by S.loginname))%2 as l_loginrank , T.Error
, convert(nchar(20), T.StartTime,120) "HitDate", T.NTUserName, T.NTDomainName, T.HostName, T.ApplicationName
, T.LoginName, T.SPID, T.ServerName, T.TextData
, case when S.loginname is null then 'InvalidLoginName' else S.loginname end as loginname_1
, case when T.Error in (18462,18463,18464,18465,18466,18467,18468,18471,18487,18488)
then 4 --''Password Related Problems''
else case when T.Error in (18458,18459,18460)
then 2 --''Licensing Related Problems''
else case when T.Error in (18452,18450,18486,18457)
then 1 --''Authentication Related Problems''
else case when T.Error in(18451,18461)
then 5 --''Server''''s Mode of Operation''
else case when T.Error in (17197)
then 6 --''Slow Server Response''
else 3 --''Others''
end
end
end
end
end "Type"
, (dense_rank() over ( order by (case when T.Error in (18462,18463,18464,18465,18466,18467,18468,18471,18487,18488)
then 4 -- ''Password Related Problems''
else case when T.Error in (18458,18459,18460)
then 2 --''Licensing Related Problems''
else case when T.Error in (18452,18450,18486,18457)
then 1 --''Authentication Related Problems''
else case when T.Error in(18451,18461)
then 5 --''Server''''s Mode of Operation''
else case when T.Error in (17197)
then 6 --''Slow Server Response''
else 3-- ''Others''
end
end
end
end
end )))%2 as l1
, (row_number() over ( order by (case when T.Error in (18462,18463,18464,18465,18466,18467,18468,18471,18487,18488)
then 4 --''Password Related Problems''
else case when T.Error in (18458,18459,18460)
then 2--''Licensing Related Problems''
else case when T.Error in (18452,18450,18486,18457)
then 1 --''Authentication Related Problems''
else case when T.Error in(18451,18461)
then 5 --''Server''''s Mode of Operation''
else case when T.Error in (17197)
then 6 --''Slow Server Response''
else 3-- ''Others''
end
end
end
end
end ),T.StartTime desc))%2 as l2
from @temp_trace T
left outer join sys.syslogins S on(T.LoginName = S.loginname)
SELECT DB_NAME(database_id) N'資料庫', physical_name N'實體檔案', type_desc N'檔案類型', state_desc N'檔案狀態', size*8.0/1024 N'檔案大小(MB)' FROM sys.master_files
