Linchi Shea

Clustering every SQL Server instance

2012-02-11T03:32:00Z

You may disagree, but I believe it is a good practice to cluster all the SQL Server instances. That is, even when you plan to run a SQL Server instance on a single machine, you should install it in a single node cluster. The primary advantage is that you only need a single standard SQL Server build instead of one for the stand alone and one for the clustered. This results in simplified configurations such as when you configure network aliases, Kerberos, and multiple instances. If you need to add...(read more)

Performance impact: the cost of NUMA remote memory access

2012-01-30T23:17:20Z

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 congested and becomes a major impediment to scalability. NUMA hardware solves this scalability challenge by dividing the processors into groups, with each...(read more)

No respect: NUMA affinity meets query parallelism

2012-01-28T05:32:51Z

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 BOL article discusses various NUMA affinity scenarios. Recently, I have been playing with NUMA affinity on various servers with hardware NUMA, such as those with Intel X5690 and Intel E7-4870, running SQL Server 2008 R2 RTM (10.50.1600) and SQL Server...(read more)

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

2012-01-27T05:33:11Z

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, or as an intellectual exercise. But then I bumped into an article on writing a T-SQL stored procedure to find and replace strings in a text file. That really unsettled me! Sure, when you are in a hurry, you need to grab a tool--any tool--to get the...(read more)

Performance impact: SQL2008 R2 audit and trace

2012-01-25T03:29:27Z

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), I’ve always taken that as a given and never bothered to check it out. Recently, I had to capture some object access information, and it turned out that SQL Audit was not the most convenient tool for the task. I had to go back to SQL Trace....(read more)

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

2012-01-22T06:41:23Z

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, the results of enabling hyperthreading have been positive for both the tested reporting queries and the tested OLTP queries, although I did run into one exception where a reporting query workload performed better without hyperthreading . In all...(read more)

Performance impact: not all is better with hyperthreading

2012-01-17T22:57:32Z

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. I happened to have some free time over this past long weekend, and I was curious to see how a different workload might behave, differently, similarly or the same. So I modified the workload driver client I wrote earlier to work with Serguei’s query,...(read more)

Performance impact: driving up context switches/sec

2012-01-12T05:23:52Z

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 help understand the SQL Server behavior in the environments we support. Recognizing that any attempt to rehash what is already said/recommended out there will more likely be a disservice than a service, I’d like to look at it from a different angle,...(read more)

Performance impact: hyperthreading for OLTP queries -- II

2012-01-11T18:51:17Z

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...(read more)

Elephant in the room: TPC-E

2012-01-07T05:21:44Z

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 results. So naturally they were very happy and eager to talk about performance, I mean any database performance, until the topic of TPC-E came up. They might not be squirming. But it’s hard to be mistaken that they really didn’t want to talk about...(read more)

Performance impact: hyperthreading for OLTP queries

2012-01-06T04:44:00Z

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...(read more)

Performance impact: hyperthreading for reporting queries

2012-01-05T05:36:00Z

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...(read more)

Evaluating server hardware: a sign of the times

2012-01-03T05:02:20Z

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. Rather, I’m interested in a piece of hardware only in how SQL Server runs on that hardware or that specific configuration of hardware. Now in this day and...(read more)

Where is SQL Server running in a cluster?

2011-12-28T03:55:00Z

There are numerous ways to find where a SQL Server instance is running in a cluster. The most convenient tool was cluster.exe . Unfortunately, I have to say it was the most convenient tool, and no longer is because no single cluster.exe works with all versions of Windows. You could also use the PowerShell cluster cmdlets such as Get-ClusterGroup. But it’s not ubiquitous. These days, if I have to quickly find out which node a SQL Server instance may be running, I generally run the following command...(read more)

Performance impact: a little business logic goes a long way

2011-12-23T05:13:21Z

I’m running into this little performance tuning pattern enough number of times that it is worth a special mention. As it often happens, the app folks complain about a proc call being very slow, and I track it down to a specific line in the proc. The line appears to be harmlessly simple, as simple as the following: SELECT MAX(BusinessDate) FROM BusinessTransactions; But it takes a long time to complete. Upon further inspection, it turns out that BusinessTransactions is actually a very complex view...(read more)