Search results matching tags 'Performance' and 'hyperthreading'

Performance impact: hyperthreading for OLTP queries -- II

Linchi Shea — Wed, 11 Jan 2012 18:51:17 GMT

This is in part a response to a comment by Paul White (@SQL_Kiwi) to my previous post on the performance impact of enabling hyperthreading (HT) on OLTP queries, and in part due to my desire to capture a more complete set of test data for future investigation on this very topic. I’m posting below the results of re-running the same exact test as described in the previous post but with the SQL Server instance bumped up to build 10.50.2500 from 10.50.1600. The former is SQL Server 2008 R2 with Service Pack 1, whereas the latter is SQL Server 2008 R2 RTM.

In addition, I have included the core count as a formal test parameter, and tested the performance impact of enabling HT at the following core counts: 16, 20, 24, 32, and 40. The core count was controlled through the BIOS on machine reboot. The exact BIOS feature for controlling the number of cores is primarily under System Options –> Processor Options –> Enhanced Processor Core Disable (Intel Core Select). This allows one to enter the number of cores to be enabled per socket.

The results are as follows:

In the previous post, enabling HT is reported to increase the throughput by 5~7.5%. In the above chart, the increase in the throughput varies between 9% and 14%. So it may appear that SQL Server 2008 R2 SP1 responds slightly better to enabling HT than does SQL Server 2008 R2 RTM. However, because there is always a margin of error in any test, I’d assign more significance to the overall trends and patterns than the exact numbers. And in both cases, the over trends and patterns are similar in that this particular OLTP workload responded positively to enabling HT on the E7-4870 processors.

Performance impact: hyperthreading for OLTP queries

Linchi Shea — Fri, 06 Jan 2012 04:44:00 GMT

My previous post focuses on the performance impact of enabling hyperthreading (HT) on a machine with four Intel Westmere-EX processors on reporting queries. Let’s turn our attention to OLTP queries.

To oversimplify it, reporting queries are generally processed by scanning a large number of pages, whereas quick index seeks are the hallmark of OLTP queries.

The OLTP queries used to check out the hyperthreading impact are the two TPC-C read-only transactions (Order Status and Stock Level), slightly modified to work properly in the test environment. The need to modify these two TPC-C transactions is discussed in an early post. Briefly, it’s because in their original forms they don’t scale properly on this 80-cpu test machine running SQL Server 2008 R2. You can find the modified code of these two transactions in the attachment of this post.

The benchmark described in the same post is used to drive the tests whose results are reported here. If you want to find out more about the benchmark, please check out that post, so I won’t duplicate its description here.

Essentially, with this OLTP benchmark (or workload) I want to see how it behaves when I increase the load level by increasing the number of concurrent users, with and without hyperthreading on a DL580 G7 with four E7-4870 processors (10 cores per processor) and 264GB of RAM. The OS is Windows 2008 R2 Enterprise x64 Edition with SP2 and DBMS is SQL Server 2008 R2 Enterprise x64 Edition (10.50.1600). There is no intra-query parallelism; maxdop is set to 1 in all the cases.

Four test configurations are examined:

40 core with HT. That is 80 logical cpus. HT is enabled in the BIOS.
40 cores without HT. That is 40 logical cpus. HT is disabled in the BIOS.
20 cores with HT. That is 40 logical cpus. HT is enabled in the BIOS.
20 cores without HT. That is 20 logical cpus. HT is disabled in the BIOS.

The following chart shows the results when 200 simulated users are driving the workload concurrently. Note that with 200 users, all the processors on this test system are pushed to ~100%.

Compared with the results for the reporting queries (see the previous post), the performance gain from enabling HT on the 40 cores is very much more marginal with this OLTP benchmark. We see an increase of about 5% from ~7900 batch requests per second to ~8300 batch requests per second. Enabling HT on the 20 cores produces an improvement of similar magnitude (about 7.5%).

Going from 20 cores with HT to 40 cores without HT, however, gives the OLTP queries a dramatic throughput gain of about 67% from ~4730 batch requests per second to ~7900 batch requests per second. We see similar behavior with the reporting queries. Knowing how HT works, I think it is intuitive we expect a big gain, whether or not it should be 67%.

So for both the reporting queries and the OLTP queries that I have tested, the hyperthreading results are positive. So far so good!

Performance impact: hyperthreading for reporting queries

Linchi Shea — Thu, 05 Jan 2012 05:36:00 GMT

There are a lot of questions on hyperthreading, but not a lot of answers. There is no shortage of opinions, but very few are based on significant first hand experience or solid test data.

We know that the hyperthreading technology in the older generations of the Intel processors was not well received by the SQL Server community. Hyperthreading in the newer generations of the Intel’s Nehalem-based Xeon processors, however, is decidedly better implemented, and it appears to be much better received by the SQL Server community.

I have done some tests with the Intel Westmere-EX processors, exploring how its hyperthreading feature may influence various types of SQL Server workloads. And my experience from these tests is generally positive. In fact, I have not seen any significantly negative performance impact that could be directly attributed to enabling hyperthreading. I plan to post some of my empirical observations. The intent is not to settle any issues. Rather in keeping with my own tradition, it is to contribute some data points to this very important subject.

In this post, I focus on the performance impact of enabling hyperthreading on reporting queries. Since there is a large number of variables that could heavily influence the results of any hyperthreading related test, quite a bit of attention is paid to ensure repeatability of the test results.

The tests are set up as follows:

The tests use a single common query (shown below). The focus is not on the single query performance, but how streams of this query perform.
There are two key test variables:

Degree of parallelism. It is controlled with the option (maxdop) clause appended to the query.
Number of concurrent query streams. It is controlled through a C# program by spawning as many threads, each of which drives the query in a serial loop for a configurable amount of time.

There are three hyperthreading (HT) test configurations:

40 core with HT. That is 80 logical cpus. HT is enabled in the BIOS.
40 cores without HT. That is 40 logical cpus. HT is disabled in the BIOS.
20 cores with HT. That is 40 logical cpus. HT is enbaled in the BIOS.

The query execution plans are logged by the test driver and verified to be constant across each test run and each query execution.
The performance statistics of each query execution, regardless of its maxdop setting or which stream it is in, is captured by the test driver and loaded into a table for analysis and overall metric calculation.
The test database consists of a 200-million-row main table tran_stats, exactly as described in this previous post. The SQL scripts for creating and populating this table are in the attached zip file. The test database also includes a dimension table which basically is the #tmp table in this same previous post. The scripts to create and populate this dimension table are also included in the attached zip file.
The test results are summarized with a query processing throughput metric, Queries per Hour, representing the number of queries processed in an hour for the given test configuration.
All tests are run while the data are fully cached. The storage subsystem is not a factor in these tests.

Essentially, with this test workload we want to see how it behaves when we increase the load level by increasing the number of concurrent query streams for a given maxdop setting, with and without hyperthreading on a DL580 G7 with four E7-4870 (10 core) processors and 264GB of RAM. The OS is Windows 2008 R2 Enterprise x64 Edition with SP2 and DBMS is SQL Server 2008 R2 Enterprise x64 Edition (10.50.1600).

The main test query (without the maxdop option clause) is as follows:

select COUNT(*) from (
   select id_server_config,id_thread_name,id_tran_type,id_tran_status,id_users,id_cpus,
             COUNT(*) as cnt
from tran_stats t1 join dimension t2
      on t1.server_config = t2.server_config and
           t1.thread_name = t2.thread_name and
           t1.tran_type   = t2.tran_type and
           t1.tran_status = t2.tran_status and
           t1.users       = t2.users and
           t1.cpus        = t2.cpus
group by id_server_config,id_thread_name,id_tran_type,id_tran_status,id_users,id_cpus
) as v

I pick this particular query because it is a rather plain reporting query. Nothing exotic and something you’d see often in the real world. I also happen to be playing with it recently for other purposes.

By the way, in the spirit of full disclosure, if there is interest in the actual test driver program, I can post it here.

So, how do the results look like?

40 cores with HT vs. 40 cores without HT

For maxdop = 2, the query throughput results are summarized in the following chart.

Most notably, when the load level is stressed with 64 concurrent streams, the system manages to push through ~478 queries per hour with HT. Compare that to ~374 queries per hour without HT at the same load level. That is about 28% increase in the processing power (for this specific configuration, always remember that!). That’s a substantial improvement.

Also note that for streams 1 through 16, there is little to no difference whether or not HT is enabled. That is expected. After all, if the queries are not competing for processors, whether you have 40 or 80 processors should not make a big difference. This also means that if you just run your test with a single query, you probably won’t see any difference, one way or another. And if you draw a conclusion based on that single query result, the conclusion is likely to be misleading.

The next three charts show the results for maxdop=4, 8, and 20, respectively. The results look similar to that for maxdop=2 in terms of the general trend and pattern, though the extent of performance improvement varies. The improvement is generally between 8% and 25%.

40 cores without HT vs. 20 cores with HT

In this case, they both appear as 40 logical processors at the OS level and to the SQL Server instance except that one has 40 cores and the other has 20 cores. So which configuration should have better performance capacity?

You probably would pick 40 cores without HT. And you’d be correct. The following charts are the supporting evidence.

When pushed, 40 cores without HT outperforms 20 cores with HT substantially by as much as ~48%.

So overall in these tests, the performance impact of hyperthreading on the query throughput is positive, and in some cases very substantial.

To reiterate, I’d be careful--if at all--extrapolating the observations here. Hopefully, though, as the evidence accumulates from the community, we can have a better comfort level in how we use this feature effectively.

Poll: Hyperthread or not on 2 x 4 core Nehalem SQL Server?

merrillaldrich — Thu, 14 Jan 2010 22:05:00 GMT

Common wisdom with previous generations of server hardware was that SQL Server might not benefit from hyperthreading or could be degraded at high CPU usage; does anyone have definitive data about Nehalem (non-EX) servers and SQL Server 2005 or 2008?

Here are some resources:

http://support.microsoft.com/kb/322385

http://sqlblog.com/blogs/kevin_kline/archive/2007/08/18/the-perils-of-hyperthreading-for-sql-server.aspx

http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx

http://technet.microsoft.com/en-us/magazine/2007.10.sqlcpu.aspx

http://serverfault.com/questions/33515/current-wisdom-on-sql-server-and-hyperthreading

I am wondering whether Nehalem changes this story at all.

[Follow up 2/12/2010: I tested, and did some further reading, and decided to split the difference on this: transactional systems that have high numbers of connections and small queries have HT enabled, and our reporting/warehouse system that does fewer but more complex queries has it turned off.]