Featured

Setup a Simple Transactional Replication lab

At my former employer, one of the key components of our Business Intelligence environment was transactional replication of data between production and the Data Warehouse. Because this replication was critical to our reporting/BI pipeline, not to mention my livelihood, I wanted to dig into to how transactional replication works. As usual for me, the first … Continue reading Setup a Simple Transactional Replication lab

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 … Continue reading How I Learned to Stop Worrying and Add The Nonclustered Index

Can Table Partitioning Improve Query Performance?

At work we have tables containing billions of rows, and are hundreds of GB in size. The business intelligence queries we run against them in the reporting database are often focused around dates and not accountids / productids as they are in PROD.  For these large tables, it is sometimes useful to split the records … Continue reading Can Table Partitioning Improve Query Performance?

How much space will table compression save me?

At work we have a reasonably large table with a NVARCHAR(MAX) column. This column stores HTML and other user generated content. This table is on a SQL Server 2014 instance, so cannot take advantage of new compression technologies in SS 2016. Looking at the estimated benefits via query: Well … bummer!  Looks like no savings … Continue reading How much space will table compression save me?