January 2012 - Posts

• Performance impact: the cost of NUMA remote memory access

  These days if you get a new server-class machine to run SQL Server, you can almost be 100% sure that it’ll be running on NUMA hardware. The recent AMD Opteron and Intel Nehalem-based processors are all built on some form of NUMA architecture. The current Read More... Posted Monday, January 30, 2012 7:17 PM by Linchi Shea | 10 Comments Filed under: Benchmark, Local memory access, Memory speed, NUMA, NUMA affinity, Performance, Remote memory access

• No respect: NUMA affinity meets query parallelism

  What happens when NUMA affinity meets query parallelism? It gets no respect! SQL Server allows you to affinitize a TCP port to a specific NUMA node or a group of NUMA nodes. Books Online has an article on How to: Map TCP/IP ports to NUMA Nodes . And this Read More... Posted Saturday, January 28, 2012 1:32 AM by Linchi Shea | 6 Comments Filed under: bug, NUMA, NUMA affinity, parallelism, port affinity

• T-SQL stored procedure for finding/replacing strings in a text file. Really?

  I know people have been doing all sorts of things with T-SQL, and I have absolutely no issue with people trying to push the limit of what T-SQL can do, or what you can use it to accomplish, especially when it’s for demonstration or pedagogical purposes, Read More... Posted Friday, January 27, 2012 1:33 AM by Linchi Shea | 4 Comments

• Performance impact: SQL2008 R2 audit and trace

  We are told that SQL Server 2008 R2 Audit (and SQL Server 2008 Audit) has much less performance overhead than SQL Trace when we try to capture the same information. Knowing how SQL Server R2 Audit is implemented (i.e. on top of the extended events infrastructure), Read More... Posted Tuesday, January 24, 2012 11:29 PM by Linchi Shea | 4 Comments

• Performance impact: hyperthreading on Intel Westmere-EP processors (X5690)

  Recently, I have been looking into the performance impact of enabling hyperthreading on various platforms with various SQL Server workloads. All the results I have shared so far are from a DL580 G7 with four Westmere-EX  (E7-4870) processors. Overall, Read More... Posted Sunday, January 22, 2012 2:41 AM by Linchi Shea | 3 Comments

• Performance impact: not all is better with hyperthreading

  In the comments to my previous post on the performance impact of enabling hyperthreading on reporting queries, Serguei Tarassov indicated that it might be interesting to try different reporting queries, and suggested a specific parameterized test query. Read More... Posted Tuesday, January 17, 2012 6:57 PM by Linchi Shea | 6 Comments

• Performance impact: driving up context switches/sec

  Too many context switches per second are considered bad for your database performance. But how many is too many has never been clear. With the core count of new servers going up rapidly, it becomes even less clear how we should evaluate this counter to Read More... Posted Thursday, January 12, 2012 1:23 AM by Linchi Shea | 13 Comments Filed under: Benchmark, Context Switches, Testing

• Performance impact: hyperthreading for OLTP queries -- II

  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 Read More... Posted Wednesday, January 11, 2012 2:51 PM by Linchi Shea | 8 Comments Filed under: Benchmark, hyperthreading, Performance, Query Processing, Testing

• Elephant in the room: TPC-E

  Over the past few months in several semi-formal occasions, I ran into folks from your well-known vendors (minus Microsoft). Some of the folks were from the vendors’ performance labs and were involved in conducting benchmark tests and publishing benchmark Read More... Posted Saturday, January 07, 2012 1:21 AM by Linchi Shea | 11 Comments Filed under: Benchmark, Rant

• Performance impact: hyperthreading for OLTP queries

  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 Read More... Posted Friday, January 06, 2012 12:44 AM by Linchi Shea | 24 Comments Filed under: Benchmark, hyperthreading, Performance, Query Processing, Testing, TPC-C Attachment(s): TPC-C-read-queries.zip

• Performance impact: hyperthreading for reporting queries

  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 Read More... Posted Thursday, January 05, 2012 1:36 AM by Linchi Shea | 16 Comments Filed under: Benchmark, HT, hyperthreading, parallelism, Performance, Reporting queries Attachment(s): create_tables_load_data.zip

• Evaluating server hardware: a sign of the times

  For nearly ten years, I have had success in using a specifically modified version of the TPC-C benchmark for evaluating server hardware running SQL Server. Depending on your purpose, you can evaluate computer hardware in numerous ways with numerous benchmarks. Read More... Posted Tuesday, January 03, 2012 1:02 AM by Linchi Shea | 7 Comments Filed under: Benchmark, Many-core, Performance, TPC-C