Performance Impacts of Partitioning DML Triggers

Continuing on with my Partitioning post series, this is part 3.

The partitioning includes several major components of work (and can be linked below);

  1. partitioning large existing non-partitioned tables
  2. measuring performance impacts of partitioned aligned indexes
  3. measuring performance impacts of DML triggers for enforcing partitioned unique indexes
  4. rebuilding tables that are already partitioned (ie. apply a new partitioning scheme)
  5. implementing partial backups and restores (via leveraging partitions)
  6. implementing partition aware index optimisation procedures
  7. Calculating table partition sizes in advance

This blog post deals with partition aligning non-clustered indexes (unique and non-unique) and then measuring performance impacts of DML triggers for enforcing partitioned unique indexes.

I will blog about the other steps later.

And so, lets get into the nitty gritty of the partitioning details!

Continue reading

Performance Impacts of Partitioned Aligned Indexes

Continuing on with my Partitioning post series, this is part 2.

The partitioning includes several major components of work (and can be linked below);

  1. partitioning large existing non-partitioned tables
  2. measuring performance impacts of partitioned aligned indexes
  3. measuring performance impacts of DML triggers for enforcing partitioned unique indexes
  4. rebuilding tables that are already partitioned (ie. apply a new partitioning scheme)
  5. implementing partial backups and restores (via leveraging partitions)
  6. implementing partition aware index optimisation procedures
  7. Calculating table partition sizes in advance

This blog post deals with deciding whether to align non-clustered indexes to the table (or not) via Measuring Performance Impacts of Partitioned Aligned Indexes.

I will blog about the other steps later.

And so, lets get into the nitty gritty of the partitioning details!

Continue reading

SQL Server 2016 Preview this Year

Quick, short and sharp blog post for those interested in the latest and greatest that the SQL Server mothership has to offer…

It was announced by the Microsoft CEO Satya Nadella at Microsoft Ignite 2015 that SQL Server 2016 will be released for preview summer this year (I assume that means Q3 CY).

The official SQL Blog post is here http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx

The official SQL Server 2016 is here http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/

See the my picks on the feature list below…

Continue reading

New Bug: Change Data Capture (CDC) Fails after ALTER COLUMN

EDIT: Fri 19 Jun 2015 – We received confirmation the Microsoft Support Team the bug can be reproduced however at this point in time there will be NO FIX

EDIT: Thurs 02 Jun 2016 – I have reproduced the bug in SQL Server 2016 (GA release) so unfortunately it now affects all SQL Server versions and all patch levels from SQL Server 2008 to the current version.  Please read below for methods to identify the issue and a work around.


This week we discovered a new SQL Bug that affects all SQL Versions from SQL Server 2008 to SQL Server 2016 (GA Release).  The bug specifically affects the SQL Enterprise feature of Change Data Capture (CDC).  The bug is difficult to identify – however the article below outlines a method to replicate the bug, and a method to remediate it.

If you are not familiar with CDC then have a look here https://msdn.microsoft.com/en-US/library/cc645937.aspx

The bug will occur when you change a data type from TEXT to VARCHAR(MAX) on a table that is marked for CDC, and you then update any row to push the LoB value off page (ie total row size exceeds the page size).  What makes this bug so damn sinister is that you could successfully do the table ALTER and then the problem will sit dormant till whenever you update that LoB column off page.

When the bug occurs it will manifest itself with a broken CDC Log Reader (ie SQL Agent CDC Capture Job) that will NOT move past a specific LSN.

Could not locate text information records for the column "MyColumn", ID 13 during command construction. [SQLSTATE 42000] (Error 18773)
The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {0038a7d9:000172d4:0010}.
Back up the publication database and contact Customer Support Services. [SQLSTATE 42000] (Error 18805)
Log Scan process failed in processing log records.

I have provided a SQL Script at the end of this post which you can use to replicate the error.

As of writing this post there is no fix yet available for this error.  I will post here again once it becomes available and how you can get it.

And so, lets get into the nitty gritty of how to reproduce and fix the error.

Continue reading