SQL Server

PASS 2015 Session Report – Optimize “All Data” with a Modern Data Warehouse Solution

Mr. Fox SQLLeave a comment

PASS 2015 has kicked off in Seattle, well the precon’s have anyway on Mon & Tue.  The actual conference starts on Wed-Fri!

I attended a precon session today called Optimize “All Data” with a Modern Data Warehouse Solution held by Bradley Ball and Josh Luedeman of Pragmatic Works.

The session had a focus on moderising the corporate data warehouse via focusing on Data Lifecycle Optimisation.

What does that mean?  

Well – It means focusing on a define set of critical technology and business areas around the corporate data warehouse and strategically implementing a managed approach to improving the corporate data warehouse via introduction of technologies and processes.  Specifically this looked at 6 areas around the corporate data warehouse to consider in your approach to modernisation;

  1. Architecture and Configuration
  2. Availability and Continuity
  3. Maintenance and Optimisation
  4. Enterprise BI
  5. Big Data Architecture and Deployment
  6. Business and Predictive Analytics

Continue reading →

House Price Predictions @ PASS 2015

Mr. Fox SQLLeave a comment

The PASS speakers were announced a quite a number of weeks ago now for PASS 2015 in October – and I felt pretty damn honored to be one of the 160 or so selected to speak at the conference (…and as a quick aside I also note a reasonably large contingent of Aussie & Kiwi presenters too!).

It will be my 2nd conference, and first time speaking at it.

Exciting?  Absolutely!  Nervous?  Absolutely!  

So what is my topic I hear you *think* – and why might it be interesting to you? (read on!)

Continue reading →

Experiences of Submitting a PASS Session

Mr. Fox SQL4 Comments

[read this post on Mr. Fox SQL blog]

The PASS speakers were announced a quite a number of weeks ago now for PASS 2015 in October – and I felt pretty damn honored to be one of the 160 or so selected to speak at the conference (…and as a quick aside I also note a reasonably large contingent of Aussie & Kiwi presenters too!).

It will be my 2nd conference, but first time speaking at it.

It actually came as a (welcome!) surprise having attended my first PASS in 2014 (actually on recommendation of fellow Aussie Rob Farley) and then to go-on and submit a session for PASS 2015 (on suggestion of yet another fellow Aussie Victor Isakov).

Both guys are also presenting at PASS 2015, checkout their sessions.

So what was my experience of submitting a PASS session, and how did the process unfold? (read on!)

Continue reading →

Implementing Partial Backups and Restores (via Leveraging Partitions)

Mr. Fox SQL10 Comments

[read this post on Mr. Fox SQL blog]

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

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 implementing partition aware partial backup and restore procedures.

I will blog about the other steps later.

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

Continue reading →

Pie Charts Aren’t THAT Evil!

Mr. Fox SQLLeave a comment

[read this post on Mr. Fox SQL blog]

Its time to take a well deserved 1/2 time break in my 8 part post series on SQL Partitioning and so I have decided to take a slight “light-hearted” tangent and talk about visualisations, or more specifically Pie Chart visualisations.

In all seriousness, this actually came up as I overheard a conversation at a client site debating the usage of this very visualisation.

Now – If you believe everything you read on the Internet about Pie Charts you may begin to think they are the proverbial trouble-maker of the BI World, but I believe that they deserve a chance to prove themselves!

So to prop up my rickety case, this post will explore Pie Chart Visualization Best Practices and then compare the default Pie Chart visualization from 10x industry leading BI/Reporting Tools to see how they stack up against this Best Practice list.

And so, lets get into the nitty gritty of creating and eating pie charts!

Continue reading →

Rebuilding Existing Partitioned Tables to a New Partition Scheme

Mr. Fox SQL9 Comments

[read this post on Mr. Fox SQL blog]

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

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 rebuilding tables that are already partitioned (ie. apply a new partitioning scheme).

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 Partitioning DML Triggers

Mr. Fox SQL9 Comments

[read this post on Mr. Fox SQL blog]

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

Mr. Fox SQL9 Comments

[read this post on Mr. Fox SQL blog]

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

Mr. Fox SQLLeave a comment

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

Mr. Fox SQL15 Comments

[read this post on Mr. Fox SQL blog]

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

———-

EDIT #2: Thurs 02 Jun 2016 
Bug reproduced in SQL Server 2016 (GA release).

—–

EDIT #3: Sat 21 Oct 2017
Bug reproduced in SQL Server 2017 (GA release) (Windows)
(As at writing CDC is not supported on SQL 2017 Linux)

Unfortunately this issue now affects all SQL Server versions and patch levels from SQL Server 2008 to the current GA 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 →