How I Learned to Stop Worrying and Add The Nonclustered Index

In my post about transactional replication we configured the dbo.Badges table for replication. Recall that this is a table that lives in the StackOverflow database.   Let’s pretend that over time the production table we replicated has grown to billions of rows, and our reporting relies on hitting the dbo.Badges subscriber table in the SO_small database.  In our scenario, as is often the case IRL, our production table is clustered on the “Id” field, and we are not allowed to add any additional indexes in production to support our query. But our query results are returning slower than molasses running up hill in winter because we are querying on the Date column, and there is no index present for this.

What we ARE allowed to do is add indexes to our replicated tables in the data warehouse.  What are the impacts of adding a nonclustered index on the “Date” column to one of our subscriber tables? The plan is to add a nonclustered index on SO_small.dbo.Badges as a test. We are interested in:

• What is the impact to transaction log growth?
• Do we have to reinitialize our subscription?

My initial concern was how the transaction log on production (StackOverflow) might grow based on having to store transactions while replication is paused. Further research showed this concern was unfounded. As it turns out, the Log Reader Agent continues to monitor the transaction log of our StackOverflow database, and loads any relevant transactions into the distribution database. Recall we called the distribution database “distDB” in our initial setup from the last post.  In the distribution database, the commands are queued until the Distribution Agent can copy them to the subscribers.  In our case, the transactions can be up to 72 hours old before they would expire and cause the need to re-snapshot our data.

A demo will be instructive. We will use the StackOverflow and SO_small databases from the last blog post on Transactional Replication.  Let’s check our database file sizes before we do this:

USE SO_small;
SELECT [file_id],
   [type],
   substring([physical_name],1,1) as DriveLetter,
   [name] LogicalFileName,
   [physical_name] PhysicalFileName,
   CAST([size] as DECIMAL(38,0))/128. as SizeMB, 
   CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128. AS SpaceUsedMB, 
   (CAST([size] as DECIMAL(38,0))/128) - (CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128.) FreeSpaceMB,
   (CAST([max_size] as DECIMAL(38,0))/128) as MaxSizeMB,
   [is_percent_growth] as [Is%Grow],
   [growth],
   GETDATE()
FROM [sys].[database_files];

Filespace Before Adding Our Nonclustered Index

StackOverflow Database:

SO_small database:

To make this all a bit more realistic, I’ve started running a load against the StackOverflow database. The tool I use for this is SQLQueryStress. You can visit https://github.com/ErikEJ/SqlQueryStress for documentation and updates. The .sql script I have modified for generating queries/inserts/updates/deletes is available at BrentOzar.com.

Let’s go ahead and pause replication while we run the index build. Bring up the Replication Monitor, click the “W2K12-SQL” node, and go the the Subscription Watch window.  Right click the subscription:

Choose “Stop Synchronizing” and the status will change to “Not running; performance critical”.

Now let’s create our index. We are choosing to do it OFFLINE, in hopes that the index build will run faster. In my system at work, this made a huge difference in testing:

USE SO_small
GO
  CREATE NONCLUSTERED INDEX idx_NC_Badges_Date
  ON dbo.Badges ([Date])



WITH (PAD_INDEX = ON, 
      STATISTICS_NORECOMPUTE = OFF, 
      SORT_IN_TEMPDB = ON, 
      DROP_EXISTING = OFF, 
      ONLINE = OFF, 
      ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON, 
      FILLFACTOR = 90, 
      MAXDOP = 4)
  GO

Now let’s take a look at replication, particularly our Undistributed commands shown in the Subscription Watch window.

You can see that a number of commands stacked up in the distributor database while we had replication synchronization paused for the index build.  No worries though! We simply bring up the Replication Monitor, click the “W2K12-SQL” node, right-click and select “Start Synchronizing”.

This will enable the Distributor to synchronize the commands waiting in the distDB to the SO_small database.  Basically the Log Reader Agent continued to monitor the transaction log of our StackOverflow database even while replication was paused. When we start synchronizing the Distribution Agent can copy those stored commands to the subscriber. When the synchronization catches up, we’ll see the record counts in range between the dbo.Badges tables match once again. We have to add the appropriate filter to our query, as we created our dbo.Badges article with a filter (WHERE Date >= ‘20160101’)

USE SO_small;
GO



select count(*)
from dbo.Badges
WHERE Date >= '20160101';



USE StackOverflow;
GO



select count(*)
from dbo.Badges
WHERE Date >= '20160101';

A quick look at the difference in IO/time stats and plans for this query between StackOverflow and SO_small show the positive impact of our new index.  Notice the lower reads and overall execution time between StackOverflow and the SO_small queries. The query plans show that the StackOverflow query had to do a scan on the cluster (more expensive!) versus a much more selective seek on our new nonclustered index for the SO_small query:

StackOverflow

SO_small

As it turns out, we win! Our nonclustered index speeds up our query. We did NOT blow up transaction logs. And we did not have to re-snapshot our publication table and wait for that. Great job! I hope this helps you out. See you next time!