Search Results for “parameter sniffing”

  1. Home
  2. Search Results
  3. (Page 3)

1.5 Bad Branching Causes Sniffing, Good Branching Reduces It

Index tuning and query hinting give us one plan that works well enough for some situations, but what if we really need two different plans? For example, we’ve been dodging around the problem of two different input parameters that really require two different approaches: maybe it’s small data versus big data, maybe it’s two different…

Read More

2.6 Lab 4 Setup: Track Down Sniffing Problems with Query Store and usp_PlanCacheAutopilot

Now, it’s your turn: enable Query Store with some overly-aggressive capture options just to make sure you get a glut of data quickly inside the lab: Transact-SQL ALTER DATABASE [StackOverflow] SET QUERY_STORE = ON GO ALTER DATABASE [StackOverflow] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, DATA_FLUSH_INTERVAL_SECONDS = 60, INTERVAL_LENGTH_MINUTES = 1, QUERY_CAPTURE_MODE = AUTO) GO 12345 ALTER…

Read More

2.3 Lab 3 Setup: Track Down Sniffing in the Plan Cache History

Now, it’s your turn: we’re going to run a workload in your lab while sp_BlitzFirst collects data. You’ll query the plan cache history in DBAtools.dbo.BlitzCache and BlitzWho, find queries whose plans are changing, and gather parameters & plans to use that will help you design a better plan tomorrow. You don’t have to fix the…

Read More

3 How Parameters Influence Cached Plans

When you’re tuning queries, picking the right parameters is crucial. So often, you can tune for one particular set of parameters – only to result in different parameters performing poorly. We’ll start with the query we used in the first module, searching user comments by location & date range, and then start changing the location…

To access this incredible, amazing content, you gotta get Fundamentals of Query Tuning or Recorded Class Season Pass, or log in if you already shelled out the cash.
Read More

Parameter Fluid Optimization

SQL Server
0
Optimizer Studies I don’t have a real job, which means that if something strikes my fancy, like staring at a spot on the wall for an hour, I can do it. Occasionally things are a bit more inspired, and so I go poking around what SMRT PPL might be up to. All you nice people…
Read More
Pocket Square

Unparameterized Queries

sp_BlitzCache™ Result: Unparameterized Queries One or more queries are effectively being run as if they were unparameterized which can lead to unpredictable execution times. This can be caused by applying functions to parameters or by using OPTIMIZE FOR UNKNOWN. Whatever the reason, this can cause problems for SQL Server. This is effectively the opposite of parameter sniffing –…
Read More

Registration is Open Now for My SQLBits Workshop!

SQLBits
2 Comments
Registration just opened up for my SQLBits workshop on Mastering Parameter Sniffing! Move fast – due to COVID, they’re spacing out the attendees, so there are even less seats available than usual. My workshops usually sell out quickly, so if you want a seat, you’ll need to hustle in. Here’s the session abstract: You’re a…
Read More

The SQL Server Posts You Read the Most in 2021

SQL Server
0
I think of my blog posts in a few different categories: Posts that will only be read at the moment they’re published (like upcoming webcasts, humorous stuff, and career advice) Posts that will show up in search history over time and continue to be evergreen (like how a feature works) Posts that provoke discussion and…
Read More

[Video] Office Hours on the Balcony Part 2

Videos
0
Picking up right where we left off yesterday, we continue down through the not-so-highly-upvoted questions from https://pollgab.com/room/brento. Here’s what we covered: 00:00 Introductions 00:22 Andrew from Canada: To Null or not to Null? That is the question. On tables with 100M to 1B rows and with several columns that contain data 20% of the time, should…
Read More

[Video] Office Hours in Malibu: Ask Me Anything About SQL Server

Videos
5 Comments
I’m up bright and early in Malibu, so let’s talk through your highly upvoted questions from https://pollgab.com/room/brento. 00:00 Introduction 01:04 JD: Have you ever encountered a very weird case during query / performance tuning that you just couldn’t get the query across the finish line, specifically just because SQL Server didn’t want to behave nicely?…
Read More

Free Classes Start Next Week. Register Now.

Company News
8 Comments
This month & next, I’m going to teach you the fundamentals of SQL Server database performance tuning, live, for free. The first class is my How to Think Like the Engine class. It’s already free online in both video and blog post formats, but this fall I’m updating and expanding it to a 3-hour version.…
Read More

Announcing My 2021/2022 Training Class Schedule

Company News
0
If you want to attend my live online classes, it’s really easy: just buy a Live Class Season Pass and you can drop in on any class that works for your schedule. I recommend attending ’em in this order: Fundamentals, required, in this order: Index Tuning, Query Tuning, Parameter Sniffing Fundamentals, optional: Columnstore, TempDB (can be…
Read More

Announcing Fundamentals Week: May 10-14, $295

Company News
0
You love learning from me. You’ve attended some of my free online streams, read my blog posts, and you use the First Responder Kit. You enjoy my laid-back, humorous approach to sharing what I’ve learned over the years. You love live classes. You’ve tried watching recordings or stepping through demos yourself, but…you just can’t block…
Read More
Menu