When moving from SQL Server 2008+ to SQL Server 2012, be aware that the sampling algorithm has changed. For example, I created a partitioned clustered index on two identical tables (same schema, rows and distribution), one in SQL Server 2008 R2 SP1 and the other in SQL Server 2012 RC0. Below shows the partial output […]
Joe Sack
SQL Server Performance Tuning, High Availability and Disaster Recovery Blog
SQL Server 2012 Partitioned Index Default Sampling Algorithm Changes
Posted on: February 25, 2012 1:24 pm
Exploring Columnstore Index Metadata, Segment Distribution and Elimination Behaviors
Posted on: February 25, 2012 5:39 am
I was interested in exploring various questions about columnstore indexing this morning – and I realized I should probably blog about what I observed. This truly was just an exploration and not a formal test (call it semi-structured fiddling). Also, some of my “questions” were really just confirmations. While I believe what I read, I […]
The Transactional Replication Multiplier Effect
Posted on: February 23, 2012 8:49 am
This post idea was prompted by a discussion I had this week with Jonathan Kehayias about an environment that had multiple transactional replication publications defined with overlapping table articles. In other words, a table was defined as an article in more than one publication. While I can think of some cases where you would want […]
SEQUENCE Object versus Modulus Hash Partitioning
Posted on: February 22, 2012 8:19 am
Last month I wrote a post called Hash Partitioning with SQL Server 2012’s SEQUENCE object and CYCLE argument and I was asked a good question by Eric Humphrey (twitter) about whether there was any advantage of using a sequence object for hash partitioning over an identity/modulus/computed-column technique. At that point I hadn’t compared the two […]
What are the Replication Agents waiting on? Accumulating wait stats by agent session
Posted on: February 15, 2012 6:25 am
Consider the following scenario: · You have Transactional Replication deployed · Data is flowing, but just not as fast as you would like · This scenario could apply to local/remote distributors and push/pull subscribers There are several different techniques we can use to narrow down where the replication performance issue is happening. Perhaps you’ve already […]
Workload Group MAX_DOP and the Faux Parallel Plan
Posted on: February 13, 2012 3:37 am
Let’s say you’ve “disabled” parallelism on your SQL Server instance via the ‘max degree of parallelism’ setting as follows: EXEC sp_configure‘max degree of parallelism’, 1 RECONFIGURE Now most folks know (or are finding out) that this doesn’t really prevent parallel plans if you throw in a MAXDOP hint. For example, let’s take the following query: […]
VirtualBox Script to Batch Save and Start Multiple Guests
Posted on: February 6, 2012 10:06 am
I use Windows 7 as my laptop host OS and this limits me from using Hyper-V natively to handle VM guests. Yes, I could do the dual-boot thing, but I haven’t done it yet and I do like the simplicity of not having to switch host OS contexts. Windows 8 will, thankfully, be adding […]
