SQL Server Database Maintenance Performance and Hyperthreading

Question

I have two IBM machines with the same exact hardware 32 cores, 256GB RAM for each machine. One machine has Hyperthreading enabled and the other does not. When I run maintenance jobs (dbcc checkdb, and rebuild index) for the database that is 1TB the maintenance job takes 8 hours on the machine that has hyperthreading enabled and 22 hours on the machine that does not have hyperthreading enabled. I have read all over the internet to disable hyperthreading as a best practice. Am i doing the right thing here by disabling hyperthreading?

Answer 1

Well, it depends...but it is certainly possible that you may see poorer performance when Hyperthreading is enabled.

The simple explanation for this is because under the hood, it is still actually 1 physical core that is doing the work. Hypethreading give the "illusion" of there being an additional core split. This is why you don't actually see double the performance when using the technology but instead more like a 70% uplift, for the right type of workloads. 

What are the CPU models you are working with.

---

John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter

Answer 2

SQL Server is rarely CPU dependent.  Are the databases exactly the same?  I would suggest looking at the disk IO subsystem.

Answer 3

4 Intel Xeon 2266 MHz, 64-bit, 2 Threads, 1.125000 Voltage

Answer 4

Yes the databases are exactly the same.

Answer 5

• What does the maintenance job do exactly?
• Are both servers using the same storage system?
• What is the storage system being used?

---

John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter

Answer 6

The maintenance job does a DBCC CHECKDB (MYDATABASE) that takes 9 hours, after the checkdb finishes then it rebiulds all indexes in the database.

Both Servers are using the same storage system.

The storage system is a XIOTECH Emprise 7000 SAN.

Answer 7

Hi Rick,

What is version of your SQL Server on both Servers? Did they get the same SP or Hot fix. The performance of hyper-threaded environments really varies. Please refer to this online article for more details.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 8

As recommended by Tom, I'd preferred to see the disk performance counters.

Avg disk sec/transfer
Avg. disk queue length
disk transfers/sec

from both the machines

Also, some bit of blocking/latch waits caused by these operations can slow down the process. you may also want to monitor the waits and waittime in select * from sys.dm_os_waiting_tasks whilst the job is running.

---

Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
|Team Blog:- Team Blog

Answer 9

Please post the results of SELECT @@VERSION on both machines.

Answer 10

Are the workloads the same on both servers?

Answer 11

Version for both servers:

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)

Sep 16 2010 19:43:16

Copyright (c) 1988-2008 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Answer 12

this is the version for both servers

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)

Sep 16 2010 19:43:16

Copyright (c) 1988-2008 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Answer 13

the workloads were the same on both servers, i said were because one of the servers was taken out from production.