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 consensus is that as the number of processors grows, their shared memory bus can easily get ...

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 ...

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 ...

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. ...

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. Arbitrarily picking a generic benchmark to evaluate a server is of no interest to me. ...

When you deploy a production cluster, you want any failover to be as transparent to your users as possible. It follows that the nodes within the cluster should be identical in terms of their hardware and software configurations. It further follows that the nodes are expected to perform the same or virtually the same. In practice, performance ...

What do you mean? Okay, that does sound like an oxymoron, doesn't it? Let's say you are trying to optimize a stored procedure, and your proposed change results in the stored procedure running not faster, but perhaps a bit slower than it currently does. Can you then declare your optimization effort a success? Just some food (well a question) ...

I posted the following SQL Server challenge yesterday:   Describe a reproducible workload that would see significant throughput improvement when trace flag 1118 is enabled.   In response, Konstantin Korobkov wanted to know whether the number of data files in tempdb would make a difference.   I did test that scenario and did ...

One of the key performance metrics on any computer system is its memory speed (or memory bandwidth).   Memory speed can be measured in many different ways at different levels. At the OS level, one way to measure memory speed is to clock how fast (in terms of megabytes/gigabytes per second) it can read a piece of data from a memory location ...

[2008/08/25] This post has been modified significantly to correct some inaccurate statements because I mis-read Joe Chang's post. Joe Chang posted some interesting results using the TPC-H queries with the scale factor set to 10. I happened to have done something similar, and naturally noticed a rather significant difference between his results ...