SQL Server

Locking, Blocking, and Isolation Levels, SQL Server

If Erik takes out a lock on a row, and no one else is around to hear it, that’s just locking. There’s nothing wrong with locking by itself. It’s perfectly fine. If you’re the only person running a query, and you have a lot of work to do, you probably want to take out the…

How to Start Troubleshooting Parameter Sniffing Issues

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

When a query is sometimes fast and sometimes slow, for the same input parameters, and you swear nothing else in the environment is changing, that’s often a case of parameter sniffing. After SQL Server starts up and you run a stored procedure, SQL Server builds an execution plan for that proc based on the first set…

Query Tuning Week: How to Start Troubleshooting a Slow Stored Procedure

Last Updated April 9, 2017

Execution Plans, SQL Server

When you need to find out why a stored procedure is running slow, here’s the information to start gathering: Check to see if the plan is in the cache. Run sp_BlitzCache® and use several different @sort_order parameters – try cpu, reads, duration, executions. If you find it in your top 10 plans, you can view…

[Video] Office Hours 2016/08/10 (With Transcriptions)

Last Updated August 14, 2016

This week, Brent, Richie, Erik, and Tara get together in Round Rock, TX, for Dell DBA Days, to talk through your SQL questions and answers. They discuss what they are working on at Dell during the event, as well as Windows Kerberos and NTLM Authentication, Always On Availability Groups, Microsoft’s Document DB, automating restore databases…

What kind of hardware can you buy for one core’s worth of Enterprise Edition?

Last Updated April 9, 2017

Licensing, SQL Server

Dell R730 Microsoft charges $14,256 for a 2-core pack of SQL Server 2016 Enterprise Edition. So how much hardware can we get for $7,128? I started with one of the more common 2-socket server models, a Dell PowerEdge R730, and then optioned it with: Chassis – up to 8 2.5″ drives (because 2.5″ drives aren’t…

Bad Idea Jeans: Dynamically Generating Ugly Queries and Task Manager Graffiti

Last Updated April 1, 2019

Bad Idea Jeans, Humor, SQL Server

Say you’re at Dell DBA Days, and you want to build a really ugly query, fast. You want to generate a StackOverflow database query that will take a long time to compile, and maybe demand a huge amount of memory to run, but not actually take any time to execute. You might start by building…

Announcing Training Class Instant Replay, Plus New Classes in Philly, San Diego, and Online

Last Updated April 9, 2017

SQL Server

Graduation Day in Philly When we ask students for feedback about our in-person classes, they said one thing over and over: “It’s just so much information. Can we get an easier way to refer back to everything?” Now you can, with Instant Replay: starting with classes this month, you can watch an online recorded version…

[Video] Office Hours 2016/08/03 (With Transcriptions)

Last Updated August 6, 2016

This week, Richie, Erik, and Tara discuss availability groups, sharding databases, using DNS entries to mask DB server names, and Erik’s passion for internet buttons. 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. Office Hours Webcast – 2016-08-03 How can…

Windows Update Now Delivers SQL Server Cumulative Updates

Last Updated August 6, 2016

SQL Server

When the SQL Server Release Services team said they were going to start treating cumulative updates just like service packs, you may not have expected this part: Windows Updates This means if your Windows admins approve and install patches, they may also be patching your SQL Server a little more frequently than you’re used to. Cumulative…

An Introduction to Query Memory

Last Updated May 26, 2020

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server

Microsoft has been quietly making some amazing improvements for performance tuners in SQL Server 2012, 2014, and 2016. This week, we’re going to introduce you to just how awesome they are. (They being the improvements, not Microsoft. You already knew they were awesome.) Using the freely available StackOverflow database, let’s start with a simple query…

Cardinality Estimator Trace Flags and Links

Last Updated August 28, 2016

Erik Darling

SQL Server

This comes up during Office Hours once a week Which pretty much gives it a 100% hit rate. I’m writing this mostly to have something to reference when people ask. Trace flags If you restore a database on a SQL Server 2014 or greater instance, you have choices to make. You can either set the…

Dell DBA Days Prep: Country Songs About Databases

Last Updated April 9, 2017

Humor, SQL Server

I travel a lot, and I’ve seen a lot of things, but even I was surprised this week when I sat down in a Cracker Barrel Restaurant that had the oddest soundtrack. It turns out there’s an entire sub-genre of country music that specializes in database songs. While I was sitting there eating my Uncle…

Using Stack Overflow Queries to Generate Workloads

Last Updated May 19, 2019

Load Testing, SQL Server

One of my favorite questions is, “How can I generate workloads to run against SQL Server for testing?” Step 1: get the StackOverflow.com database. This database has a relatively simple schema, just a few tables, real-world data distributions, and enough rows that you can generate seriously slow queries. You can use any size, small medium…

[Video] Office Hours 2016/07/27 (With Transcriptions)

Last Updated July 30, 2016

Index Maintenance, SQL Server

This week, Brent, Richie, Erik, and Tara discuss speeding up performance, how to make SSRS highly available, what to do with SSIS packages when making upgrades, Windows Core, and how to hide data from DBAs when they have SA access. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to…

How To Fix Forwarded Records

Last Updated May 4, 2020

Tara Kizer

Some of our clients have very high forwarded record counts and aren’t aware of it until they run sp_BlitzFirst and get an alert about high Forwarded Records per Second. Some of these clients are using Ola Hallengren‘s IndexOptimize stored procedure to maintain their indexes. This brought up a question of whether or not rebuilding a…

HOLY COW. Amazon RDS SQL Server Just Changed Everything.

Last Updated February 13, 2017

Amazon Web Services (AWS), Microsoft Azure, SQL Server

Platform-as-a-Service users (Azure SQL DB, Amazon RDS) often ask me: OMG How can I move my data into the cloud? Can I just take a backup on-premises, and restore up in the cloud? How can I use the cloud as inexpensive disaster recovery? Once I go to PaaS, why can’t I just get a backup file…

Logging Activity Using sp_WhoIsActive – Take 2

Last Updated March 29, 2018

Tara Kizer

Monitoring, SQL Server

We’ve already covered how to log activity using sp_WhoIsActive, but I wanted to share how I’ve seen it done in the wild. The difference between the two methods is that this version logs everything to the same table and is for continuous activity logging (scheduled job), whereas the other version uses a new table each time…

sp_BlitzFirst @SinceStartup = 1 Shows You Wait Stats Since, Uh, Startup

Last Updated February 13, 2017

SQL Server, Wait Stats

It’s this easy to get a cumulative recap of wait stats, file stats, and Perfmon counters: sp_BlitzFirst @SinceStartup = 1 In the above example, here’s what the wait stats columns mean: Sample Ended – when it ran Hours Sample – the number of hours your SQL Server instance has been up wait_type – the name of…

[Video] Office Hours 2016/07/20 (With Transcriptions)

Last Updated July 25, 2016