As a database enthusiast and SQL Server aficionado, I've come across countless scenarios where performance was subpar and the underlying issues were obscured. Today, I want to shed some light on the importance of updating statistics in SQL Server and discuss why large tables with billions of rows might not be suitable candidates for auto-updating … Continue reading The Importance of Updating Statistics in SQL Server and the Challenges with Large Tables
Getting Started in “Data”
Here I’m talking to someone trying to break into “Data” as a career field in a small to medium sized company. This might mean moving from help desk or marketing or customer service to DBA, Data Analyst, or Data Engineer. These data jobs reflect a LOT of overlap when you work in smaller companies, as I … Continue reading Getting Started in “Data”
Columnstore Index Goodness
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
Dedupe it or lose it!
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!
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
The Path to Enlightenment – SQLSkills style!
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!
Virtual logfile (VLF) hell…
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…
Just how big ARE those tables?
If you need to know the size of all your tables, here's a simple query to get you where you need to go: