Part 0 - Introduction and All That Stuff.... Hello, wonderful coders! Are you ready for another thrilling adventure into the world of technology? Today, we're diving deep into the mystifying and magical world of SQL Server performance tuning. That's right, my intrepid readers, we're about to unravel the secrets behind a faster, smoother, and more … Continue reading SQL Server Performance Tuning: A Far From Almost Comprehensive Guide for the Curious Coder
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
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
Sarcasm and ChatGPT Simple Sentiment Analysis
I had fun with some sentiment analysis testing using the OpenAI gpt-3.5-turbo model. This came as a tangent as I was working through the Prompt Engineering for Developers class offered by deeplearning.ai. The idea is to scan through prompts on a product review site - think eBay or customer generated listings on eCommerce sites. Without … Continue reading Sarcasm and ChatGPT Simple Sentiment Analysis
Python Decorators: Adding Magic to Your Functions
In the enchanting world of Python programming, decorators work like spells, allowing you to modify the behavior of functions or methods without the need for altering their code. Essentially, these higher-order functions accept another function as input, and in return, conjure up a new function with enhanced or altered behavior. With the power of decorators, … Continue reading Python Decorators: Adding Magic to Your Functions
The Importance of Updating Statistics in SQL Server and the Challenges with Large Tables
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”
Part 1: SQL Server Performance Tuning: A Far From Almost Comprehensive Guide for the Curious Coder
Section 1: Why Performance Tuning Matters – A Deeper Dive with Examples Ah, my knowledge-thirsty friend, you're in for a treat! Let's delve deeper into the significance of performance tuning and explore some examples to illuminate the benefits of this enigmatic art. Performance tuning is like casting a magic spell on your SQL Server, transforming … Continue reading Part 1: SQL Server Performance Tuning: A Far From Almost Comprehensive Guide for the Curious Coder
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!
JOIN versus WHERE filtering
Intellectually I knew that query results can vary depending on where we decide to "filter" our data. In the JOIN condition, or in the predicate WHERE clause? The following example of this revealed itself recently. It struck me as such a simple and clear case I thought others might benefit from it. The situation arose … Continue reading JOIN versus WHERE filtering
Justin Onstot 