Search Results for “execution plan”

The Many Mysteries of Merge Joins

Last Updated April 8, 2018

Not A Single Picture Of A Zipper Image humbly borrowed from https://70srichard.wordpress.com/2014/12/17/beverly-hills-cop/ There are some interesting things about Merge Joins, and Merge Join plans that I figured I’d blog about. Merge joins have at least one interesting attribute, and may add some weird stuff to your query plans. It’s not that I think they’re bad,…

Building SQL ConstantCare®: What Data We Collect (and What We Avoid)

Last Updated April 10, 2018

Brent Ozar

SQL ConstantCare

Next up in my series of behind-the-scenes posts about our new SQL ConstantCare®, let’s talk about the kinds of SQL Server diagnostic data we’re gathering, why, and what data we’re steering clear of for now. (Psst – read through to the end for a huge, crazy discount today, too.) We – you and I – are…

Of Windowing Functions And Where Clauses

Last Updated March 2, 2018

Erik Darling

Execution Plans, SQL Server

Seen One, Seen’em All This isn’t about indexing! I promise. In fact, I’ve dropped all my indexes for this. It’s just that, well, I’ve seen this happen with two clients in a row, so maybe it’s time to blog about it. If you run this query, you get a fairly obvious plan. Transact-SQL WITH nonsense…

First Responder Kit Release: SQLBits was pretty awesome but now I have to do work again

Last Updated May 9, 2018

Erik Darling

First Responder Kit Updates

APRIL FOOLS! IT’S ONLY MARCH FIRST! Tomorrow. Butthead. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #1407: @parlevjo2 gave us a check to make sure databases are owned by an existing user. Existence is everything, so I hear. #1416: After existence is probably location. After that is collation. @ktaranov let us know that our collation wasn’t all that. Especially…

Adaptive Joins, Memory Grant Feedback, and Stored Procedures

Last Updated May 26, 2020

Erik Darling

Adaptive Joins, Execution Plans, Memory Grants, Parameter Sniffing

Not Exactly The Catchiest Name There’s a TL;DR here, in case you don’t feel like reading the whole darn thing. Batch mode memory grant feedback works with stored procedures It takes several runs to adjust upwards to a final number It seems to only adjust downwards once (in this case by about 40%) This isn’t…

Using WITH (NOEXPAND) to Get Parallelism with Scalar UDFs in Indexed Views

Last Updated February 12, 2018

Brent Ozar

T-SQL

Scalar functions are the butt of everybody’s jokes: their costs are wrong, their STATS IO results are wrong, they stop parallelism when they’re in check constraints, their stats are wrong in 2017 CU3, they stop parallelism in index rebuilds and CHECKDB, I could go on and on. Recently, we ran across yet another scenario where…

SQL Server 2017 CU3 adds tempdb spill diagnostics in DMVs and Extended Events

Last Updated May 26, 2020

Erik Darling

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2017

Where have you been all my life? Whenever I’m teaching people about query tuning, two things come up almost without fail: parameter sniffing and tempdb spills. They’re not mutually exclusive — some queries will seemingly always spill to tempdb, whether parameter sniffing is present of not, and some queries experiencing issues with parameter sniffing will never spill…

Adaptive Joins

Last Updated December 7, 2017

Erik Darling

NEW NEW NEW This is an informational message. These types of joins are new in SQL Server 2017, and we don’t have enough experience with them to do any analysis. Right now we’re only flagging them, but in the future we’ll add some detail around the estimated join choice and average rows, most likely, unless…

sp_BlitzQueryStore: A Gentle Introduction

Last Updated May 26, 2020

Erik Darling

Execution Plans, First Responder Kit, Parameter Sniffing, Query Store (QDS), sp_BlitzQueryStore, SQL Server 2016, SQL Server 2017

Odds and ends During the precon, we asked how many people were on 2016, how many people were aware of query store, how many people were using it, and how many people were using sp_BlitzQueryStore. About 1/3 of the hands went up at first, then for each successive question, fewer and fewer hands stayed up.…

sp_BlitzQueryStore

Last Updated February 22, 2021

Brent Ozar

When I look at a SQL Server that’s in trouble, I wanna know what was going on when things were bad. When you run sp_BlitzQueryStore, it basically: Looks at your workloads over the last 7 days (that’s configurable) Finds the highest resource consuming periods of time Finds the top 3 queries that used the most…

First Responder Kit Release: Precon Precogs

Last Updated May 9, 2018

Erik Darling

First Responder Kit, First Responder Kit Updates

This release comes to you from a hotel room in Chicago: The land of fiscal insolvency and one shooting per hour. It’s pretty nice, otherwise. This release is to get the pre-precon important stuff in. As much as I’d like to push all the recent contributions through, between travel, speaking, and uh… what do the…

New sys.messages Entries in SQL Server 2017

Last Updated October 2, 2017

Brent Ozar

SQL Server 2017

I’m only showing the English (1033) ones here, message ID and then text. Some of the new messages are very Azure-specific, and while I love to think that we’d get some of these features in the boxed product, I think Microsoft is just getting down to a standard shared list of message IDs. However, some…

New Wait Stats and DMV Columns in SQL Server 2017

Last Updated October 2, 2017

Brent Ozar

SQL Server 2017, Wait Stats

SQL Server 2017 is officially out, and here’s what I’ve found so far. New Wait Stats in 2017 BACKUP_INMEM_DIFFLIST_READ_ACCESS BACKUP_INMEM_DIFFLIST_WRITE_ACCESS BLOB_CONTAINER_TABLE COLUMNSTORE_MIGRATION_BACKGROUND_TASK EXTERNAL_SCRIPT_NETWORK_IO FOREIGN_REDO GHOSTCLEANUP_UPDATE_STATS GLOBAL_QUERY_CANCEL GLOBAL_QUERY_CONSUMER GLOBAL_QUERY_PRODUCER HADR_THROTTLE_LOG_RATE_LOG_SIZE HADR_THROTTLE_LOG_RATE_SEEDING HADR_THROTTLE_LOG_RATE_SEND_RECV_QUEUE_SIZE MEMORY_GRANT_UPDATE MIGRATIONBUFFER PREEMPTIVE_OS_BCRYPTIMPORTKEY PREEMPTIVE_OS_NCRYPTIMPORTKEY PREEMPTIVE_XE_PROXY_ADDSESSION PREEMPTIVE_XE_PROXY_PROCESSBUFFER PREEMPTIVE_XE_PROXY_REMOVESESSION PREEMPTIVE_XE_PROXY_SESSIONCOMMIT PWAIT_AUDIT_CLOSE_EXPIRED_LOGS_MGR_RWLOCK PWAIT_AUDIT_SESSIONS_LOCK PWAIT_EXTERNAL_SCRIPT_LIBMGMT_DIR_PERMS PWAIT_PREEMPTIVE_APP_USAGE_TIMER PWAIT_PREEMPTIVE_XE_STUB_LISTENER PWAIT_SBS_FILE_OPERATION QDS_HOST_INIT RBIO_WAIT_VLF REMOTE_BLOCK_IO RESTORE_MSDA_THREAD_BARRIER SBS_DISPATCH SBS_RECEIVE_TRANSPORT SBS_TRANSPORT SECURITY_CNG_PROVIDER_MUTEX SLEEP_SETUP SNI_WRITE_ASYNC…

First Responder Kit Release: Still Totally Lacking A Servicing Model

Last Updated September 29, 2017

Erik Darling

First Responder Kit

A lot of people would think we’re crazy for releasing a FRK update a few days before a new version of SQL Server drops, but we’ve been adding 2017 compatible features for months Also none of you are on 2017 Most of you won’t live to be on 2017 If you’re lucky, your kids3000 will…

SQL Server 2017: Potentially Interesting New Extended Events

Last Updated August 21, 2017

Erik Darling

SQL Server 2017

You still won’t use them And I predict that Extended Events will remain as niche-only use for specific troubleshooting scenarios. Very few monitoring tools use them, and the ones that do still rely on traces and DMV queries for the bulk of their collection. That’s not a good sign, in 2017, considering how long Extended…

A Better Way To Select Star

Last Updated November 20, 2018

Erik Darling

Development, Execution Plans, Indexing, SQL Server

Mindless Self Promotion I liked writing this blog post so much that I wrote an entire presentation on it. If you’d like to see it at GroupBy, click the link and vote. Update: You can watch the video for this session here. Much has been written about this It’s probably one of the lowest hanging…

sp_AllNightLog

Last Updated December 13, 2022

Brent Ozar

You manage a SQL Server instance with hundreds or thousands of mission-critical databases. You want to back them all up as quickly as possible, and one maintenance plan job isn’t going to cut it. The basic concept: backing up and restoring databases You also want to protect those databases by restoring them as quickly as…

Introducing sp_BlitzQueryStore

Last Updated November 16, 2017

Erik Darling

Execution Plans, First Responder Kit, Query Store (QDS), sp_BlitzQueryStore

Oh, so you’re the one While SQL Server 2016 adoption is still pretty low, and 2017 still quite isn’t out yet, that’s no reason to not invest a few days of dev time into a stored procedure that is only usable by the smallest portion of SQL Server users. Right? Look, someone around here has…

[Video] Office Hours 2017/05/24 (With Transcriptions)

Last Updated May 29, 2017

Brent Ozar Unlimited Team

SQL Server, Videos

This week, Brent, Erik, and Richie discuss disabling telemetry in SQL Server 2016, proper place for log files, Availability Groups, measuring data returned from a single query, whether CHECKDB should be done on tempdb, limiting tempdb usage for each session, transferring large amounts of data from on-prem to the cloud, and Iron Chef spoilers. Here’s…

[Video] Office Hours 2017/05/03 (With Transcriptions)

Last Updated May 7, 2017

Brent Ozar Unlimited Team

SQL Server, Videos

This week, Brent, Tara, and Richie discuss choosing batch sizes, SQL 2017 on Linux, how much memory to leave on the OS for the SQL server, parameter sniffing, allocation unit size of SQL Server, triggers, X-Files, aliens and much more! Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to…

Search Results for “execution plan”

Hi! I’m Brent Ozar.