Linchi Shea : Performance

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)

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)

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)

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)

Performance impact: diminishing marginal return on the degree of parallelism

Linchi Shea — Fri, 16 Dec 2011 17:24:00 GMT

In commenting on my previous post , Greg Linwood and GrumpyOldDBA raised questions about various implications of parallelism. In this post, I’ll look at the impact of different degrees of parallelism on the query performance. I’ll limit my examination...(read more)

Performance impact: Cartesian product comes to the rescue

Linchi Shea — Thu, 15 Dec 2011 06:11:00 GMT

Although Cartesian product as a concept is essential to the relational database theory, it is often a dirty phrase – something that is associated with bad performance and therefore should be avoided. But there are cases where a Cartesian product is highly...(read more)

Performance impact: synchronous audit can trash your database performance – III

Linchi Shea — Wed, 14 Dec 2011 02:52:00 GMT

This is the third installment of my posts on the performance impact of enabling SQL Server 2008 R2 Audit. My previous two posts ( part I and part II ) show that whether it’s on a two-processor machine or an eight-processor machine, enabling an audit with...(read more)

Performance impact: synchronous audit can trash your database performance – II

Linchi Shea — Tue, 13 Dec 2011 02:58:00 GMT

In my previous post on this subject, I showed the test results from a two-processor machine. Naturally, you may wonder: do we expect to see the same dramatic throughput drop when we enable a synchronous audit (i.e. with the queue_delay property set to...(read more)

Multiple independent results from a single SELECT statement

Linchi Shea — Mon, 12 Dec 2011 20:39:00 GMT

You cannot output multiple independent results from a single SELECT statement. But sometimes I wish that could be done. An extremely simple case is when you need to build the initial dimensions from a very large table. Typically, these dimension columns...(read more)

Performance impact: synchronous audit can trash your database performance

Linchi Shea — Sun, 11 Dec 2011 22:26:00 GMT

The SQL Server Audit feature introduced in SQL Server2008 is useful in many scenarios. Although it still has much room for improvement , it has made SQL Server auditing easier to manage, and in particular, it has reduced performance impact as compared...(read more)

Transactional replication and massive data updates

Linchi Shea — Thu, 06 Oct 2011 04:19:00 GMT

SQL Server transactional replication has been a rather solid feature and works well for delivering data to reporting servers (among other things) in near real time. That said, it may not work too well when you need to perform massive updates on a published...(read more)

Performance impact: Don’t assume they are the same

Linchi Shea — Sat, 01 Oct 2011 15:42:00 GMT

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

Performance impact: stored procedures, SQL batches, and CPU usage

Linchi Shea — Fri, 22 Jul 2011 03:47:00 GMT

What is the simplest way to drive a CPU to 100% using T-SQL? If it’s a language like C++ or C#, the simplest way is to create a tight loop and perform some work that uses CPU cycles inside the loop. For instance, the following tight loop in C# can peg...(read more)