SQL Server

Execution Plans, Parameter Sniffing, SQL Server

What could go wrong? Sometimes you end up in a good plan / bad plan situation: an important query runs just fine most of the time. The query is parameterized, a good execution plan gets re-used, everything is cool. But sometimes, a “bad plan” gets compiled and starts to be reused. This is “bad” parameter sniffing. “Bad plans” can…

Oracle Terminology for the SQL Server DBA

Last Updated January 14, 2015

Jeremiah Peschka

Oracle, SQL Server

The translation team hard at work. At some point you’re going to be confronted with an Oracle installation or even just an Oracle DBA. Communicating with a DBA who works on a different product can be difficult, it’s like speaking US English and having a conversation with a native English speaker from Scotland. The words…

Our Senior DBA Training Class: Attendee Feedback

Last Updated August 15, 2024

When we finish our courses, we ask attendees what they thought. Here’s what they said about our How to Be a Senior DBA class in Chicago: “I’ve attended many trainings. This is the most valuable I’ve ever attended.” – Tim Costello, Consultant “ROI for training is always a gamble. However, I felt like I received…

New sp_Blitz® v35 Checks for Unsupported and Dangerous SQL Server Builds

Last Updated February 13, 2017

First Responder Kit, SQL Server

News broke recently of a dangerous data loss bug in SQL Server 2012 and 2014, and Aaron Bertrand explained which patch levels are affected. It’s kinda tricky, and I’m afraid most people aren’t even going to know about the bug – let alone whether or not they’re on a bad version. I added build number checking into…

Our Sessions at the #SQLPASS Summit 2014

Last Updated June 25, 2014

#SQLPass, SQL Server

The session list has been published, and we’re excited to say all of us have been selected to speak at the PASS Summit again this year. Here’s our sessions: Are Your Indexes Hurting You or Helping You? – Jes Schultz Borland – Queries need your help! Your mission, should you choose to accept it, is to…

Performance Tuning SQL Server Change Tracking

Last Updated August 11, 2015

Change Tracking is a developer tool introduced in SQL Server 2008 to help you sync data between SQL Servers, or between SQL Servers and devices. It’s designed to help you answer the request, “I need to get the most recent value for rows that have been modified since I last grabbed data.” First, I’ll give you…

Trace Flags 1117, 1118, and Tempdb Configuration

Last Updated November 4, 2020

SQL Server, TempDB

Unevenly sized tempdb files can cause uneven use – and contention on some critical pages SQL Server has a few options for configuring tempdb. It’s great that Microsoft gives some advice for how to battle some kinds of contention that can impact tempdb in KB 2154845, but things can still be a little confusing. Trace…

Announcing the SQL Server Setup Checklist: Free eBook Download

Last Updated May 5, 2017

Locking, Blocking, and Isolation Levels, SQL Server

One of the most popular posts on this site of all time is a SQL Server Setup Checklist that Brent started way back in March 2008. Over the years, we’ve updated the post and kept it fresh for installing and configuring SQL Server 2005, 2008, 2008R2, 2012, and 2014. Now we’ve taken it one step further:…

In-Memory Analytics: What’s Different Between SQL Server and Oracle

Last Updated June 16, 2014

Jeremiah Peschka

Oracle, SQL Server

Pick a memory, any memory. Both Microsoft and Oracle have released what they call “in-memory analytics” features. Microsoft started two years ago with SQL Server 2012 and Oracle is following suit next month (July 2014) with the Oracle In-Memory Analytics feature. Most of us won’t pick our database platform based on just one feature, but…

Capturing Deadlocks in SQL Server

Last Updated February 13, 2017

Jes Schultz

What’s a deadlock? Well, let’s say there’s a fight going on between Wonder Woman and Cheetah, and, in the same room, a fight between Batman and Mr. Freeze. Wonder Woman decides to help Batman by also attempting to throw her lasso around Mr. Freeze; Batman tries to help Wonder Woman by unleashing a rope from…

Our SQL Server Performance Troubleshooting Class: Attendee Feedback

Last Updated August 15, 2024

We’ve already told you what’s in our SQL Server Performance Troubleshooting class, but what did the attendees in Chicago think? “Great information. If I had to learn this myself, I would have taken years to understand these concepts.” – Kishore P., Application Admin “So, you’re pretty good at performance tuning? Yeah? Well, this course will…

Temp Tables vs Table Variables vs Memory Optimized Table Variables [Video]

Last Updated February 13, 2017

SQL Server, TempDB

Should you use temp tables or table variables in your code? Join Microsoft Certified Master Kendra Little to learn the pros and cons of each structure, and take a sneak peek at new Memory Optimized Table Variables in SQL Server 2014. This video is a recording of a live webcast, so please excuse any audio…

A Manager’s Guide to Tuning Code

Last Updated May 23, 2014

In your business application, you have a slow screen or function. Your team is pointing fingers between the application code, the database, and the hardware. To make it go faster, here are your main options: Tuning Options – Mark All That Apply Check all of the options you’re willing to consider, and X out all…

Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)

Last Updated May 26, 2020

Parameter Sniffing, SQL Server

There’s an important rule for tuning stored procedures that’s easy to forget: when you’re testing queries from procedures in SQL Server Management Studio, execute it as a stored procedure, a temporary stored procedure, or using literal values. Don’t re-write the statemet you’re tuning as an individual TSQL statement using local variables! Where it goes wrong…

Do Lots of Connections Slow Down Your SQL Server?

Last Updated May 23, 2014

Threads are important. I sometimes hear database administrators say, “This database must be involved in our performance problem: it has too many connections.” Lots of connections might cause a problem for your SQL Server, or it might not. The good news is that there’s a way to clearly tell if they’re dragging down performance or…

How to Tell if You Need More Tempdb Files

Last Updated November 4, 2020

Backup and Recovery, CHECKDB and Corruption, SQL Server

SQL Server, TempDB

You may have read that you need to have more than one data file in SQL Server’s tempdb. This can happen even if you’re using blazing fast storage. If you create a lot of tiny objects in tempdb you may hit a bottleneck on special pages that SQL Server uses internally to allocate all those objects. For certain workloads, adding…

Backups, Restores, Corruption, and You: How Strong are You?

Last Updated February 13, 2017

Jes Schultz

You and your databases have a strong relationship. You back them up, and check them for corruption. They provide the business with information day after day. Don’t let your relationship come to this! Then, one day, your relationship is rocked. The backups are too slow. The restores are too slow. There is…gasp…corruption. Are you prepared…

Curious About the SQL Server 2014 Cardinality Estimator?

Last Updated May 21, 2014

Jeremiah Peschka

If you’ve been following the blogosphere, or heading to SQL Saturdays, you’ll know that many folks are excited about the brand new SQL Server cardinality estimator (CE for short). As Kendra showed in her article The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast, the new CE provides dramatically improved query plans. What if…

New High Availability Planning Worksheet

Last Updated August 15, 2024