Development

For this week’s Query Exercise, your challenge is to find out who keeps messing up the rows in the Users table. Take any size version of the Stack Overflow database, and the Users table looks like this: People are complaining that from time to time, the contents of the AboutMe column in some – but…

Troubleshooting Mysterious Blocking Caused By sp_getapplock

Last Updated April 24, 2024

Locking, Blocking, and Isolation Levels

I’m kinda weird. I get excited when I’m troubleshooting a SQL Server problem, and I keep hitting walls. I’ll give you an example. A client came to me because they were struggling with sporadic performance problems in Azure SQL DB, and nothing seemed to make sense: sp_BlitzFirst @SinceStartup = 1 showed very clearly that their…

Using ChatGPT for T-SQL Code Reviews

Last Updated April 9, 2024

T-SQL

For this month’s T-SQL Tuesday, Pinal Dave asked us if AI has helped us with our SQL Server jobs. For me, there’s been one instant, clear win: code reviews. I usually keep a browser tab open with ChatGPT 4, and I paste this in as a starting point: You are an experienced, diligent database developer…

Does Your GROUP BY Order Matter?

Last Updated March 1, 2024

Execution Plans

Sometimes when you do GROUP BY, the order of the columns does matter. For example, these two SELECT queries produce different results: Transact-SQL CREATE INDEX Location_DisplayName ON dbo.Users(Location, DisplayName); SELECT TOP 100 Location, DisplayName, COUNT(*) AS Duplicates FROM dbo.Users GROUP BY Location, DisplayName ORDER BY Location, DisplayName; SELECT TOP 100 DisplayName, Location, COUNT(*) AS Duplicates…

Option Recompile is a Magic Turbo Button That Actually Works.

Last Updated March 1, 2024

T-SQL

I didn’t say that – Guy Glantser did. Guy Glantser is an Israeli SQL Server guru with a ton of great presentations on YouTube. I’ve had the privilege of hanging out with him in person a bunch of times over the year, and I’ll always get excited to do it again. He’s not just smart,…

What Happens When Multiple Queries Compile at Once?

Last Updated February 28, 2024

Execution Plans

An interesting question came in on PollGab. DBAmusing asked: If a query takes 5-7s to calculate the execution plan (then executes <500ms) if multiple SPIDS all submit that query (different param values) when there’s no plan at start, does each SPID calc the execution plan, one after the other after waiting for the prior SPID…

Finding Sister Locations to Help Each Other: Answers & Discussion

Last Updated March 4, 2024

This week’s query exercise asked you to find two kinds of locations in the Stack Overflow database: Locations populated with users who seem to be really helpful, meaning, they write really good answers Locations where people seem to need the most help, meaning, they ask a lot of questions, but they do not seem to…

Query Exercise: Finding Sister Locations to Help Each Other

Last Updated May 1, 2024

For this week’s query exercise, let’s start with a brief query to get a quick preview of what we’re dealing with: Transact-SQL SELECT TOP 100 Location, COUNT(*) AS Population, AVG(Reputation) AS AvgReputation FROM dbo.Users GROUP BY Location ORDER BY COUNT(*) DESC; 123456 SELECT TOP 100 Location, COUNT(*) AS Population, AVG(Reputation) AS AvgReputationFROM dbo.UsersGROUP BY LocationORDER…

Find Recent Superstars: Answers & Discussion

Last Updated February 22, 2024

Your query exercise for this week was to write a query to find users created in the last 90 days, with a reputation higher than 50 points, from highest reputation to lowest. Because everyone’s Stack Overflow database might be slightly different, we had to start by finding the “end date” for our query. I’m working…

Query Exercise: Find Recent Superstars

Last Updated February 28, 2024

For this week’s Query Exercise, we’re working with the Stack Overflow database, and our business users have asked us to find the new superstars. They’re looking for the top 1000 users who were created in the last 90 days, who have a reputation higher than 50 points, from highest reputation to lowest. In your Stack…

Finding Long Values Faster: Answers & Discussion

Last Updated February 15, 2024

In last week’s Query Exercise, our developers had a query that wasn’t going as fast as they’d like: Transact-SQL CREATE INDEX DisplayName ON dbo.Users(DisplayName); GO SELECT * FROM dbo.Users WHERE LEN(DisplayName) > 35; 12345 CREATE INDEX DisplayName ON dbo.Users(DisplayName);GOSELECT * FROM dbo.Users WHERE LEN(DisplayName) > 35; The query had an index, but SQL Server was refusing to…

#TSQL2sday Roundup: The Most Recent Issues You Closed.

Last Updated February 14, 2024

Development

For this month’s TSQLTuesday, I asked y’all to describe the most recent issues you closed. If you ask someone in IT, “What do you do for a living?” they struggle with job titles. They say things like: “Well, my job title is sysadmin, but I spend most of my time in data” “They call me…

Query Exercise: Finding Long Values Faster

Last Updated February 23, 2024

Our developers have come to us with a problem query that isn’t as fast as they’d like. Using any Stack Overflow database: Transact-SQL CREATE INDEX DisplayName ON dbo.Users(DisplayName); GO SELECT * FROM dbo.Users WHERE LEN(DisplayName) > 35; 12345 CREATE INDEX DisplayName ON dbo.Users(DisplayName);GOSELECT * FROM dbo.Users WHERE LEN(DisplayName) > 35; It has an index, but SQL Server…

Improving Cardinality Estimation: Answers & Discussion

Last Updated February 11, 2024

Your challenge for last week was to take this Stack Overflow database query to show the top-ranking users in the most popular location: Transact-SQL CREATE INDEX Location ON dbo.Users(Location); GO CREATE OR ALTER PROC dbo.GetTopUsersInTopLocation AS SELECT TOP 200 u.Reputation, u.Id, u.DisplayName, u.WebsiteUrl, u.CreationDate FROM dbo.Users u WHERE u.Location = (SELECT TOP 1 Location FROM…

The Last Ticket/Issue I Closed #TSQL2sday

Last Updated January 23, 2024

T-SQL

For this month’s T-SQL Tuesday, I asked y’all to write about the most recent ticket or issue that you closed. (If you want to see other peoples’ posts, you can check out the comments on that invite post, or wait til next week and I’ll publish a wrap-up of everyone’s answers.) A past client emailed…

Query Exercise: Improving Cardinality Estimation

Last Updated February 23, 2024

Your challenge for this week is to tune a query. Say Stack Overflow has a dashboard that shows the top-ranking users in their most popular location. It’s even got an index to support it: Transact-SQL CREATE INDEX Location ON dbo.Users(Location); GO CREATE OR ALTER PROC dbo.GetTopUsersInTopLocation AS SELECT TOP 200 u.Reputation, u.Id, u.DisplayName, u.WebsiteUrl, u.CreationDate…

Database Changes to Find Tagged Questions Faster: Answers and Discussion

Last Updated February 6, 2024

In the last Query Exercise discussion, we hit a performance wall when we were trying to quickly find questions with a specific tag. We threw up our hands and said it’s time to make database changes, but we had a few restrictions: We have a lot of existing code that uses the Posts.Tags column That…

Finding Tagged Questions Faster: Answers & Discussion

Last Updated February 23, 2024

Your query exercise was to take this Stack Overflow query to find the top-voted questions for any given tag: Transact-SQL SELECT TOP 100 * FROM dbo.Posts WHERE Tags LIKE '%<sql-server>%' ORDER BY Score DESC; 1234 SELECT TOP 100 *FROM dbo.PostsWHERE Tags LIKE '%<sql-server>%'ORDER BY Score DESC; That’s currently using this index in its execution plan:…

Free Video Training: Estimation and Statistics by Dave Ballantyne

Last Updated January 30, 2024