In case I needed a reminder of how cool columnstore indexes are for analytic queries, I made this example to jog my memory. I used the StackOverflow 180 GB sized database you can download from Brent Ozar's site. I have it running in VMWare Workstation pro, but these days you can run your SQL Server … Continue reading Columnstore Index Goodness
The scenario is there should only be one AccountID/CreateDate combo in our table1. But some process ran and has duplicated some rows and we want to get rid of them. Dedupe time! Here is an example of deduping: Now let’s see what lives in our table1: Now let’s look at the Query to group the … Continue reading Dedupe it or lose it!
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
Too often in my career I have heard "SQL Server doesn't scale as well as DBMS x" or "We don't need a DBA. You just install SQL Server and it runs itself". Both of these are misconceptions that are reinforced by a lack of people who truly understand the deep magic that is SQL Server … Continue reading The Path to Enlightenment – SQLSkills style!
At one point in my career I inherited a database that had suffered from neglect (to be kind). The logfiles had be set to autogrow 1MB at a time, and this had happened ALOT! Interestingly this rather small database was taking a surprisingly long time to restore, Knowing that a high number (>1000) of VLFs … Continue reading Virtual logfile (VLF) hell…
If you need to know the size of all your tables, here's a simple query to get you where you need to go: