SQL Server

This week, Erik, Tara, and Richie discuss tempdb etiquette for developers, elastic search vs full-text search, server CPU spiking issues, source control in a database project, querying transaction logs, 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 the…

Guess the SQL Server 2017 Release Date Contest

Last Updated October 1, 2017

SQL Server 2017

286

They used to release doves, but that’s so John Woo We’ve been doing this a while now, and time to kick it off again. Leave one – and only one – comment here with your guess of the date. If you leave multiple comments, only the first/earliest one is going to count. “The date” is…

Simulating Workload With ostress And Agent Jobs

Last Updated February 14, 2017

Load Testing, SQL Server

This question comes up a lot Especially during Office Hours, and the answer is usually… not great. You can spend a lot of money on people and complicated software to design, run, and monitor workloads against test environments, or you can throw together tests with some free tools like SQL Query Stress or Microsoft’s RML…

[Video] Office Hours 2017/02/08 (With Transcriptions)

Last Updated February 11, 2017

This week, Brent, Erik, Tara, and Richie discuss compatibility levels, SQL server SPN problems, Always On Availability Groups, failover clustering, capturing DML stuff, disc defragmentation, replication snapshots and reindexing, load testing, PowerShell, and whether you should pursue the path of a DBA. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or…

The Blog Posts You Loved to Talk About: Top-Commented Posts

Last Updated February 9, 2017

SQL Server

In WordPress, I happened to sort our posts on the comment count column and got a huge laugh. I figured you’d like to see this list too, dear reader: 20. SQL Server 2014 Standard Edition Sucks, and It’s All Your Fault – 137 comments 19. How to Set Up Standard Edition Always On Availability Groups…

Inline Table Valued Functions: Parameter Snorting

Last Updated May 26, 2020

Parameter Sniffing, SQL Server, T-SQL

You’ve probably heard about parameter sniffing But there’s an even more insidious menace out there: Parameter Snorting. It goes beyond ordinary parameter sniffing, where SQL at least tried to come up with a good plan for something once upon a compile. In these cases, it just plain gives up and throws a garbage number at…

Using Trace Flag 2453 to Improve Table Variable Performance

Last Updated March 2, 2018

Tara Kizer

Execution Plans, SQL Server, TempDB

I recently saw a server with trace flag 2453 configured. I hadn’t come across this trace flag before, so I did a little research. Microsoft says it allows “a table variable to trigger recompile when enough number of rows are changed”. This can lead to a more efficient execution plan. Trace flag 2453 is available…

[Video] Office Hours 2017/02/1 (With Transcriptions)

Last Updated February 6, 2017

This week, Brent, Erik, and Richie discuss AlwaysOn Availability Groups, execution plans, speeding up log shipping, applying wrong service packs, why servers “page wildly” when copying mid to large-size files from one SQL server to another, their favorite high availability solutions, and much more! Here’s the video on YouTube: You can register to attend next week’s Office…

Memory Grants and Data Size

Last Updated January 8, 2019

Execution Plans, Memory Grants, SQL Server

What does memory do? In SQL Server, just about everything. We cache our data and execution plans in it (along with some other system stuff), it gets used as scratch space for temporary objects, and of course queries use it for certain operations, most notably sorting and hashing. And of course, now Hekaton comes along…

SQL Server DBA’s Guide to the Gitlab Outage

Last Updated February 2, 2017

SQL Server

This week, developer tools company GitLab had a serious database outage. The short story: An admin was trying to set up replication The site had an unusual load spike, causing replication to get behind While struggling with troubleshooting, the admin made a lot of changes After hours of work, the admin accidentally deleted the production…

Updating the Stack Overflow Demo Database

Last Updated January 26, 2017

SQL Server

StackOverflow.com shares your questions, answers, comments, votes, users, badges, etc by doing a public data dump that you can download via BitTorrent. I take that data, and I turn it into a SQL Server database that you can query. It’s so much better than the Microsoft sample databases because: It’s just a handful of easily-understood tables It has…

[Video] Office Hours 2017/01/25 (With Transcriptions)

Last Updated January 29, 2017

This week, Brent, Erik, Tara, and Richie discuss whether an antivirus can corrupt a database or remove data files, the biggest node clusters they’ve worked with, whether cursors are a good idea for reporting queries, authogrowth setting for new servers, memory-optimized tables, index rebuilds and more. Here’s the video on YouTube: You can register to…

First Responder Kit Updates: So Many Updates, Brent Quit

Last Updated February 13, 2017

First Responder Kit, SQL Server

I got 128 GB of RAM just to develop sp_BlitzCache with! Also, thanks to @RichBenner @BrentOzar and @douglane for code, and @LitKnd and @HenrikStaunPoulsen for great ideas! Grab the newly updated First Responder Kit, and here’s what’s in it: sp_Blitz Improvements #677 Finally! A bug that isn’t ours! Microsoft forget to let SE clean up…

High Compiles and Multi-Statement Table Valued Functions

Last Updated February 14, 2017

SQL Server, T-SQL, TempDB

Way back in 2016 I wrote about what to do if sp_BlitzFirst warns about high compiles. During GroupBy, Adam Machanic gave a great talk on new features in SQL Server 2016. It reminded me of a blog post I wanted to write about one common culprit of high compiles: Multi-Statement Table Valued Functions. Leaving aside…

[Video] Office Hours 2017/01/18 (With Transcriptions)

Last Updated January 23, 2017

This week, Brent, Erik, Tara, and Richie discuss sp_Blitz capabilities, what’s best to use for resolving SQL performance issues, CDC, tools for capturing wait stats, their favorite monitoring tools, temp tables vs table variables, partitioning with constraints, and whether you should get any certifications. Here’s the video on YouTube: You can register to attend next week’s Office…

Indexes For Kids

Last Updated February 13, 2017

Indexing, SQL Server

Kid Tested When you work with something a lot, you tend to extrapolate concepts from it out to everything you see. That one person collecting tickets is a serial zone. That food truck line is a HEAP. The empty subway car that smells like a toilet is Replication. Stuff like that. If you have one…

Your Favorite Bugs & Enhancement Requests: #TSQL2sday 86 Roundup

Last Updated February 14, 2017

SQL Server, T-SQL

I dunno about you, but I got a big stocking full of coal. Next year, I’m gonna be better, and I plan on asking Santa for a whole bunch of Connect requests. For T-SQL Tuesday, I asked you to name your favorite SQL Server bugs & enhancement requests, and here’s what you want in your…

[Video] Office Hours 2017/01/11 (With Transcriptions)

Last Updated January 14, 2017

This week, Brent, Erik, Tara, and Richie discuss restoring databases from Enterprise Edition to Standard Edition servers, their favorite way of collecting SQL server metrics, extended events, career advice, how to tell what processes SQL uses memory for, and much more! Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe…

Do DDL Triggers in TempDB Survive Restarts?

Last Updated February 13, 2017