Search Results for &#8220;parameter sniffing&#8221;

Execution Plans, Parameter Sniffing, SQL Server

Say you’ve got an application that has tons of OPTIMIZE FOR UNKNOWN hints in the T-SQL, and you’re getting bad query plans. We’re going to use the same StackOverflow query (and the same index on Reputation) that I demoed in the post Why Is This Query Sometimes Fast and Sometimes Slow? This technique produces a…

Why Is This Query Sometimes Fast and Sometimes Slow?

Last Updated May 26, 2020

Execution Plans, Parameter Sniffing, SQL Server

You swear you didn’t change anything, but all of a sudden the SQL Server is going doggone slow. What happened? Parameter sniffing might be the problem, and to explain it, let’s see how it works. I’m going to use the StackOverflow database – particularly, the Users table that I demo in How to Think Like the…

Unused Memory Grants

Last Updated December 7, 2017

Erik Darling

Unused Memory Grants sp_BlitzCache warns about unused memory grants because they can harm concurrency and performance. If many queries ask for large memory grants and don’t use them, they still hold onto that memory until the query is complete. Symptoms You can end up seeing some nasty poison waits in sp_Blitz or sp_BlitzFirst like RESOURCE_SEMAPHORE,…

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

Last Updated May 20, 2018

This week, Brent, Richie, Tara, and Erik discuss their learning styles, Always On Availability Groups, memory leaks, Windows updates, how to transition from developer to DBA, upgrading SQL server from 2008 to 2014, temp tables vs table variables, and how to become an MVP. Here’s the video on YouTube: You can register to attend next week’s Office…

[Video] Office Hours 2016/09/28 (With Transcriptions)

Last Updated October 1, 2016

First Responder Kit, sp_BlitzFirst, SQL Server

This week, Brent, Richie, Tara, and Erik, discuss non-clustered indexes, dynamic SQL, replication, setups for servers dedicated to reporting services and warehouse databases, cross-cluster migrations, employment opportunities in the current market, and much, much 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…

What To Do If sp_BlitzFirst Warns About High Compilations

Last Updated November 16, 2017

Erik Darling

Compiles Aren’t The Same As Recompiles If you’re seeing high RECOMPILES, this post isn’t for you. We’ll talk about the differences between compiles and recompiles, but not how to troubleshoot recompiles. Recompiles mean one of two obvious things: You have a RECOMPILE hint at the stored procedure or statement level, or SQL found a reason…

[Video] Office Hours 2016/09/21 (With Transcriptions)

Last Updated September 24, 2016

This week, Brent, Richie, Tara, and Erik discuss enhancing queries, database corruption, availability groups, parameter sniffing, sending mail via SQL server, whether SQL 2016 is solid or not, other versions of SQL, physical servers vs virtual servers, and much more! Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to…

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

Last Updated September 9, 2016

This week, Brent, Richie, Erik, and Tara discuss parameter sniffing, database backups, referential integrity in databases, clustering, creating and using indexes, in-place upgrades, bench marking tools, 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 Podcast? Don’t miss…

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

Last Updated September 9, 2016

This week, Brent, Richie, and Tara discuss corruption, resetting query stats, fixing parameter sniffing issues, how to suggest features for our scripts, and why Brent is sporting an Oracle sweatshirt. 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 Podcast?…

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

Last Updated August 28, 2016

This week, Richie and Erik discuss in-place instance upgrades, availability groups, the cloud, job security, business intelligence and user adoption, and much 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. Office Hours Webcast – 2016-08-24 Erik Darling: First one,…

Query Tuning Week: How to Run sp_BlitzCache on a Single Query

Last Updated August 29, 2020

Execution Plans, Parameter Sniffing, sp_BlitzCache

The most popular way of using sp_BlitzCache® is to just run it – by default, it shows you the top 10 most CPU-intensive queries that have run on your server recently. Plus, it shows you warnings about each of the queries – if they’re missing indexes, experiencing parameter sniffing issues, running long, running frequently, doing…

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…

[Video] Office Hours 2016/06/22 (With Transcriptions)

Last Updated June 27, 2016

This week, Brent, Richie, Doug, and Tara discuss Growing databases, most useful SQL certifications, replication issues, group discounts, backup software 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. Office Hours Webcast – 2016-06-22 Brent Ozar: All right, we…

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

Last Updated June 10, 2016

This week, Angie, Erik, Doug, Jessica, and Richie discuss DB migration, rebuilding large indexes, recommendation for SQL dev ops tools, best practices for disabling SA accounts, compression, 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. Office Hours Webcast –…

SQL Interview Question: “Tell me what you see in this screenshot.”

Last Updated May 28, 2016

Interviewing, SQL Server

You’re a data professional working with (or at least applying to work with) a company using the StackOverflow database (I’m using the March 2016 version today). Your users are complaining that this stored procedure is slow: usp_GetPostsByOwnerUserId They didn’t give you parameter 26837 – I’m just giving you that so you can see an execution plan.…

Expensive Key Lookups

Last Updated December 7, 2017

Erik Darling

Expensive Key Lookups We warn about these when a single Key Lookup is >=50% of the total plan cost. A key lookup occurs when SQL uses a nonclustered index to satisfy all or some of a query’s predicates, but it doesn’t contain all the information needed to cover the query. This can happen in two…

Creating Tables and Stored Procedures in TempDB – Permanently

Last Updated November 4, 2020

Bad Idea Jeans, Humor, Parameter Sniffing, SQL Server, TempDB

No, not #tables – actual tables. Here’s how: Transact-SQL USE tempdb; GO /* This one is only available during my session: */ CREATE TABLE #myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* This one is global, meaning it's available to other sessions: */ CREATE TABLE ##myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* You can…

Updated the StackOverflow SQL Server Database Torrent to 2016-03

Last Updated April 9, 2017

SQL Server

The StackOverflow XML Data Dump was recently updated with 2016-03 data, so I’ve updated our torrent of the SQL Server database version of the Stack Overflow data dump. Fun facts about the database and its real-world-ness: 95GB in size 29,499,660 posts spanning 2008-07-31 to 2016-03-06 5,277,831 users spanning ages from -972 to 96 (just like real world…

Breaking News: Query Store in All Editions of SQL Server 2016

Last Updated February 13, 2017