Greg; I might have misunderstood you, but you seem to see a dichotomy between "convenience of support staff" vs. "user experience". I happen to think making the life of the support staff easier enhances the user experience. In addition, if we don't consider the impact on user experience, the convenience of the support staff ...

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

As mentioned, I was interested in the worst case scenario. For that, I didn't want any other resource bottleneck that may obscure the impact of remote memory access. For instance, if SQL Server has to wait for a page to be retrieved from disk, that would be an eternity compared to retrieving a page from a remote node, and in such as scenario you ...

Thanks Greg! I actually also tried OLTP workloads (e.g. TPC-C) in the same test setup, and didn't see any difference at all. It's more intutive with OLTP than with reporting in that the time it takes to get a page from another node accounts for an extremely small fraction of the total response time in an OLTP query. But for a reporting workload ...

You can't disable NUMA at the hardware level. However, many machines have a BIOS setting that allows node interleaving, which effectively presents memory as if it were on a SMP machine. By default, this setting is disabled, and I don't know any real-world scenario where disabling NUMA at BIOS provides a performance advantage. But I have not played ...

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

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

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

Greg; It's actually quite arguable whether the audit setup and the trace setup I tested are in fact similar. And I'm ready to concede that the test was kind of 'rigged' in favor of the audit setup. Just I wanted to get a feel for some sort of worst case scenario. I'm repeating the test with the trace setup using one of the security audit event, ...

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