Search Results for “parameter sniffing”

Index Fragmentation Findings: Part 1, The Basics

Last Updated July 13, 2017

We asked the community for index fragmentation information for data mining purposes, and the results are in – and interesting. Over the next couple of weeks, I’ll talk about some of the results we found in the field. What Is SQL Server Fragmentation? SQL Server stores data in pages. To make things easy, think of…

What Does Setting the SQL Server Compatibility Level Do?

Last Updated April 19, 2023

Brent Ozar

Configuration Settings, Videos

If you right-click on a database in SQL Server Management Studio, you get an option to set Compatibility Level at the database level: When you upgrade SQL Server or you want to improve performance, which option should you choose? Should you always go with the newest compatibility level? If you have an old application, can…

Office Hours Speed Round, Easy Text Answers Edition

Last Updated April 18, 2023

Brent Ozar

Production Database Administration

Some of the questions y’all post at https://pollgab.com/room/brento have easy one-line answers. Let’s knock ’em out: George: Hi Brent, what recently has been the most challenging/surprising/new-to-you performance issue you have encountered? SQL Server 2019’s slowdowns. I spent days working on that. RoJo: Have you used Distributed AG as a way to upgrade major versions of…

#tsql2sday: Start Your Dynamic SQL with a Comment.

Last Updated October 7, 2022

Brent Ozar

T-SQL

When you write dynamic SQL, start like this: CREATE OR ALTER PROC dbo.MyProc AS BEGIN DECLARE @StringToExecute NVARCHAR(4000); SET @StringToExecute = N'SELECT /* MyProc */ '; ... 12345 CREATE OR ALTER PROC dbo.MyProc ASBEGIN DECLARE @StringToExecute NVARCHAR(4000); SET @StringToExecute = N'SELECT /* MyProc */ '; ... Right after the SELECT (or INSERT or UPDATE or whatever),…

Community Tools Month: Choose Your Character

Last Updated September 5, 2022

Brent Ozar

Professional Development, Tools

This month on the blog is Community Tools Month, and I’m going to be talking about some of the most useful and influential tools out there. You can’t learn them all – *I* can’t learn them all – because there just aren’t enough hours in the day to do your work, keep your skills sharp,…

0.2 Download the Slides and Scripts

Last Updated June 15, 2022

Brent Ozar

The slide deck and demo scripts (zip) is here (last updated 2022-06-15.) The slide deck is just a few slides that I use at the beginning and end of the class, not very useful for note-taking. The real meat is in the demo scripts in each module, and those are exactly the same in the…

To access this incredible, amazing content, you gotta get Mastering Parameter Sniffing – Live Classes + Instant Replay, 1 Year Access, Mastering Parameter Sniffing – Instant Replay Recordings Only, 1 Year Access or Recorded Class Season Pass, or log in if you already shelled out the cash.

9 Signs Your T-SQL Might Fail a Code Review

Last Updated May 17, 2021

Brent Ozar

T-SQL

It’s hard to set absolute rules about, “Feature X should absolutely never be used.” However, there are some features that set off alarm bells when I see them. Usually, when I start asking more questions about when we’re using those particular features, I get answers of, “Oh, I didn’t know that was a problem.” As…

How Bad Statistics Cause Bad SQL Server Query Performance

Last Updated November 30, 2020

Brent Ozar

Statistics

SQL Server uses statistics to guess how many rows will match what your query is looking for. When it guesses too low, your queries will perform poorly because they won’t get enough memory or CPU resources. When it guesses too high, SQL Server will allocate too much memory and your Page Life Expectancy (PLE) will…

Updated First Responder Kit and Consultant Toolkit for September 2019

Last Updated September 22, 2019

Brent Ozar

First Responder Kit Updates

This month’s release has a lot of new feature goodness: the Consultant Toolkit can automatically upload the results to Amazon S3, plus sp_BlitzCache adds a new sort order to better catch unparameterized queries burning up CPU, a new warning for selects that are doing writes (other than >500MB spills, which we’ve been warning you about…

Updated First Responder Kit and Consultant Toolkit for August 2019

Last Updated August 30, 2019

Brent Ozar

First Responder Kit, First Responder Kit Updates

sp_DatabaseRestore can now restore to Azure blobs and change the database owner after a restore, and everything else gets bug fixes. To get the new version: Download the updated FirstResponderKit.zip Azure Data Studio users with the First Responder Kit extension: ctrl/command+shift+p, First Responder Kit: Import. Download the updated Consultant Toolkit in your account EU customers:…

Is It a Code Problem or a Server Problem?

Last Updated November 26, 2020

Brent Ozar

Your users are complaining that your SQL Server is slow – but is it a database problem, or not enough hardware? It’s confusing because the developers always blame the sysadmins, and the sysadmins always blame the developers. In one hour, you’ll learn: The 3 metrics to monitor on every SQL Server: Batch Req/Sec, Data Size,…

Updated First Responder Kit and Consultant Toolkit for April 2019

Last Updated April 28, 2019

Brent Ozar

First Responder Kit Updates

This month, lots of improvements and bug fixes, including checking for paused resumable index operations, Evaluation Edition expiration dates, sp_BlitzCache shows the number of duplicated plans for each query, sp_BlitzIndex will skip a list of databases for you, and more. To get the new version: Download the updated FirstResponderKit.zip Azure Data Studio users with the…

What’s New in SQL Server 2019: Faster Functions

Last Updated June 25, 2020

Brent Ozar

SQL Server 2019, T-SQL

A while back, we talked you through a public whitepaper about how Microsoft was working on making user-defined functions go faster. Now that the preview of SQL Server 2019 is out, you can start getting your hands on Froid, the performance-boosting feature. Here’s the documentation on it – let’s see how it works. Using the…

Why sp_prepare Isn’t as “Good” as sp_executesql for Performance

Last Updated May 26, 2020

Erik Darling

Execution Plans, Parameter Sniffing

sp_prepare For Mediocre You may remember me from movies like Optimize for… Mediocre? and Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)! Great posts, Kendra! Following the same theme, we found this issue while looking at queries issued from JDBC. Specifically, the prepared statement class seems to cause queries to hit the density vector…

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

Last Updated May 26, 2020

Kendra Little

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…

Search Results for “parameter sniffing”

Hi! I’m Brent Ozar.