SQL Server

This week, Brent, Richie, Erik, and Tara discuss partitioning tables, native backups vs 3rd party agent backups, page life expectancy, query tuning, deadlocks, drawbacks of RCSI, triggers, replication, Always On Availability Groups, and forest fires. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on…

Sliding Scale Parallelism: Why Stop At DOP?

Last Updated July 7, 2017

SQL Server

This is a thought experiment Just fair warning, you’re probably not going to learn anything new here. This also isn’t hidden in a CTP of SQL Server 2017, and as far as I know, it’s not a consideration outside of this post. Seriously, lawyers, I know nothing. I was just thinking (while sober, mind you),…

[Video] Office Hours 2017/07/05 (With Transcriptions)

Last Updated July 11, 2017

This week, Brent and Tara discuss separating data and logs on separate drives, statistics time, scalar UDFs, licensing, encryption, gathering SQL error logs and agent job history, replication, upcoming training courses from Brent Ozar Unlimited, and what DBA topics they struggle with the most. Here’s the video on YouTube: You can register to attend next week’s Office…

A Better Way To Select Star

Last Updated November 20, 2018

Development, Execution Plans, Indexing, SQL Server

Mindless Self Promotion I liked writing this blog post so much that I wrote an entire presentation on it. If you’d like to see it at GroupBy, click the link and vote. Update: You can watch the video for this session here. Much has been written about this It’s probably one of the lowest hanging…

[Video] Office Hours 2017/06/28 (With Transcriptions)

Last Updated July 2, 2017

This week, Erik, Tara, and Richie discuss performance and load testing tools, sysadmin update SQL server restart, defragmenting column store indexes, Amazon Redshift, installing Microsoft updates, Always On Availability Groups, Redgate SQL Clone, as well as their thoughts on the current DBA job market. Here’s the video on YouTube: You can register to attend next week’s Office…

Why Is Tracking Restores So Hard?

Last Updated June 21, 2017

Backup and Recovery, SQL Server

Phantom of the Nopera Let’s say you have a server. Let’s go one step further and say SQL Server is running on it. Crazy, I know. But hey, we specialize in crazy, here. If you want to track CREATE, ALTER, or DROP DATABASE commands, that’s easy enough with DDL triggers. But guess what? None of…

There’s Something Funny About Variable Assignment

Last Updated June 21, 2017

Humor, SQL Server

Guess who I’m going to give you three queries, and you have to guess what the output will be before you run them. Here they are: Transact-SQL DECLARE @DatabaseName NVARCHAR(256) = N'' SELECT @DatabaseName = d.name FROM sys.databases d WHERE d.name = 'master' AND compatibility_level = 130 ORDER BY d.database_id PRINT @DatabaseName SELECT @DatabaseName =…

[Video] Office Hours 2017/06/21 (With Transcriptions)

Last Updated June 25, 2017

This week, Brent, Erik, Tara, and Richie discuss cross-database transactions in AGs, agent job owners, column store indexes, linked servers, migrating SQL Server databases to AWS cloud, synchronous readable secondary, rebuilding indexes, backups using SANs, licensing, Always On Availability Groups on CDC, Azure Cosmo DB, compression, and encryption. Here’s the video on YouTube: You can…

[Video] Office Hours 2017/06/14 (With Transcriptions)

Last Updated June 19, 2017

This week, Brent, Erik, Tara, and Richie discuss setting up an archive process, CTEs vs temp tables, deadlocks, alternatives to replication, Erik and Brent’s pre-con for this year’s PASS Summit, and more. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go. Enjoy…

Query Plans: Trivial Optimization vs Simple Parameterization

Last Updated June 16, 2017

Execution Plans, Indexing, SQL Server

Facemaking You know when you think you know something, and the obviousness of it makes you all the more confident that you know it? That’s usually the first sign that there’s a giant gotcha waiting for you. And that’s what happened to me over the weekend. The setup I answered a question. And after I…

[Video] Office Hours 2017/06/07 (With Transcriptions)

Last Updated June 11, 2017

This week, Brent, Erik, and Richie discuss running SQL Server 2017 CTP on Linux, receive side scaling on servers and VMware, query tuning, nested roles vs. assigning roles to users, hierarchical data type, apps and scripts for automating restores, and learning Azure and AWS. The audio’s kinda rough this week because Brent & Erik were…

Hyper-V: Getting Your Host And Guest Connected

Last Updated June 3, 2017

SQL Server, Virtualization

Because I always forget stuff like this I have a confession to make, dear readers: I am a horrible VM admin. I’m also not great at networking. When I go to set up a VM to do something like install SQL vNext, I want to be able to talk to it. Why bother? Well, SSMS…

Automated Tuning And The Future Of Performance Tuners

Last Updated June 7, 2017

Consulting, SQL Server

Before you get too excited This isn’t a dive into any of the new automated tuning features of SQL Server 2017. I’m interested in them because Microsoft thinks they can put us out of business with them, but… Fat chance. See, a lot of the automated performance tuning features assume you EVER HAD GOOD PERFORMANCE…

[Video] Office Hours 2017/05/31 (With Transcriptions)

Last Updated June 3, 2017

This week, Brent and Richie discuss sp_Blitz Backups, change history tracking, exams and certifications, using Azure for DR-only, which version of SQL server to use for a new DB, adding additional data files, load balancing, trace flag 2371, dealing with reporting services, and much more. Here’s the video on YouTube: You can register to attend next week’s…

[Video] Office Hours 2017/05/24 (With Transcriptions)

Last Updated May 29, 2017

This week, Brent, Erik, and Richie discuss disabling telemetry in SQL Server 2016, proper place for log files, Availability Groups, measuring data returned from a single query, whether CHECKDB should be done on tempdb, limiting tempdb usage for each session, transferring large amounts of data from on-prem to the cloud, and Iron Chef spoilers. Here’s…

[Video] Office Hours 2017/05/17 (With Transcriptions)

Last Updated May 21, 2017

This week, Erik, Tara, and Richie discuss enforcing password expiration for SA logins, keeping things in sync between servers, development/prototyping missions of on-premise vs. cloud, ways of backing up databases, and more. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go. Enjoy…

What Do You Want to See in a Free Query Store Tool?

Last Updated May 19, 2017

Execution Plans, Monitoring, Query Store (QDS), SQL Server

I love Query Plans No seriously, I love digging into query plans. Even writing XQuery to dig into them in sp_BlitzCache. I was both honored and horrified to take over development of it from Jeremiah. It’s a beast, but it’s my kind of beast. At around 4500 lines of code, when you want to make…

Do SQL Server 2017’s Adaptive Joins Work with Cross Apply or Exists?

Last Updated May 19, 2017