Linchi Shea

Clustering every SQL Server instance

Linchi Shea — Sat, 11 Feb 2012 03:32:00 GMT

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

Performance impact: the cost of NUMA remote memory access

Linchi Shea — Mon, 30 Jan 2012 23:17:20 GMT

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)

No respect: NUMA affinity meets query parallelism

Linchi Shea — Sat, 28 Jan 2012 05:32:51 GMT

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)

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

Linchi Shea — Fri, 27 Jan 2012 05:33:11 GMT

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)

Performance impact: SQL2008 R2 audit and trace

Linchi Shea — Wed, 25 Jan 2012 03:29:27 GMT

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)

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

Linchi Shea — Sun, 22 Jan 2012 06:41:23 GMT

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)

Performance impact: not all is better with hyperthreading

Linchi Shea — Tue, 17 Jan 2012 22:57:32 GMT

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)

Performance impact: driving up context switches/sec

Linchi Shea — Thu, 12 Jan 2012 05:23:52 GMT

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)

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

Elephant in the room: TPC-E

Linchi Shea — Sat, 07 Jan 2012 05:21:44 GMT

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)

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

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

Evaluating server hardware: a sign of the times

Linchi Shea — Tue, 03 Jan 2012 05:02:20 GMT

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)

Where is SQL Server running in a cluster?

Linchi Shea — Wed, 28 Dec 2011 03:55:00 GMT

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

Performance impact: a little business logic goes a long way

Linchi Shea — Fri, 23 Dec 2011 05:13:21 GMT

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