Search Results for &#8220;execution plan&#8221;

Execution Plans, Indexing

To warn you ahead of time, this post is an exploration without an answer (yet). There’s some interesting stuff in here, but no conclusions. If that’s not your kind of post, feel free to skip it. If it is, well, here goes nothin’… Wild, Wild Life Creating indexes is kind of a funny thing. By…

Query Store And Cruel Defaults

Last Updated December 19, 2018

Execution Plans, Query Store (QDS)

Gosh Darn Now, look, it’s no secret that I think Query Store is cool. Heck, I went and wrote sp_BlitzQueryStore so you can examine it similarly to how sp_BlitzCache looks at the plan cache. For a variety of reasons, though, we run into people who can’t or won’t turn it on, or who had to turn…

Never Judge A Query By Its Cost

Last Updated December 12, 2018

Signs and Numbers When tuning queries, or even finding queries to tune, there’s a rather misguided desire to look for queries with a high cost, or judge improvement by lowering query cost. The problem is that no matter what you’re looking at, costs are estimates, and often don’t reflect how long a query runs for…

“Surely this one will get a clustered index scan.”

Last Updated December 7, 2018

I love building demos for our training classes because I’m constantly in a battle of the minds with SQL Server. I try to guess what he’s going to do next, and I love it when he surprises me. I started by building a new parameter sniffing demo with the Stack Overflow database, and in this…

Indexed View Creation And Underlying Indexes

Last Updated November 29, 2018

Brent Ozar Unlimited Team

Indexing

Accidental Haha While working on some demos, I came across sort of funny behavior during indexed view creation and how the indexes you have on the base tables can impact how long it takes to create the index on the view. Starting off with no indexes, this query runs in about six seconds. Transact-SQL DECLARE…

[Video] Office Hours 2018/11/21 (With Transcriptions)

Last Updated November 24, 2018

Videos

This week, Brent, Tara, and Erik discuss update statistics, how soon it is safe to use a new version of SQL Server, VMs vs physical servers, backups, disabling TCP Chimney Offset in VMware, SQL Server on Linux, performance troubleshooting, most valuable tools to have in your SQL Server DBA toolkit, nolock and read uncommitted, and…

Adventures In Foreign Keys 4: How to Index Foreign Keys

Last Updated December 29, 2021

Execution Plans, Foreign Keys

This week, we’re all about foreign keys. So far, we set up the Stack Overflow database to get ready, then tried to set up relationships, and encountered cascading locking issues. Dawn Of The Data I don’t know how long the recommendation to index your foreign keys has been a thing, but I generally find it useful…

The Curse of Cursor Options

Last Updated November 2, 2018

Red Skies At Night I know it’s hard to believe, but I still see a lot of people using cursors when they shouldn’t. Other times, there’s some scary dungeon part of the code that someone wrote eons ago that no one wants to go anywhere near to fix. Sometimes there’s a decent reason, something like:…

Batch Mode For Row Store: Does It Fix Parameter Sniffing?

Last Updated May 26, 2020

Batch Mode, Execution Plans, Indexing, Parameter Sniffing

Snorting The Future SQL Server 2019 introduced batch mode over row store, which allows for batch mode processing to kick in on queries when the optimizer deems it cost effective to do so, and also to open up row store queries to the possibility of Adaptive Joins, and Memory Grant Feedback. These optimizer tricks have…

Is Cost Threshold for Parallelism Measured in Seconds?

Last Updated October 5, 2018

SQL Server automatically chooses when to divide your query’s work across multiple CPU cores. It makes that decision based on your query’s cost. To see it, let’s throw 1,000,000 tiny rows in a table: Transact-SQL CREATE TABLE dbo.Timeless(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing VARCHAR(20)); INSERT INTO dbo.Timeless (Stuffing) SELECT TOP 1000000 'Stuff' FROM sys.all_columns…

What’s New in SQL Server 2019 System Tables

Last Updated September 24, 2018

SQL Server 2019

The free SQL Server 2019 preview download is out, and here are quite a few things to check out that aren’t documented yet: New System Objects Starting with new stored procedures: sys.sp_add_feature_restriction sys.sp_autoindex_cancel_dta sys.sp_autoindex_invoke_dta sys.sp_cloud_update_blob_tier sys.sp_configure_automatic_tuning sys.sp_diagnostic_showplan_log_dbid sys.sp_drop_feature_restriction sys.sp_execute_remote sys.sp_force_slog_truncation sys.sp_internal_alter_nt_job_limits sys.sp_rbpex_exec_cmd sys.sp_set_distributed_query_context sys.sp_set_session_resource_group sys.sp_showinitialmemo_xml sys.sp_xa_commit sys.sp_xa_end sys.sp_xa_forget sys.sp_xa_forget_ex sys.sp_xa_init sys.sp_xa_init_ex sys.sp_xa_prepare sys.sp_xa_prepare_ex sys.sp_xa_recover sys.sp_xa_rollback…

What’s New in SQL Server 2019’s sys.messages: More Unannounced Features

Last Updated September 24, 2018

SQL Server 2019

The system table sys.messages holds error messages that SQL Server needs to return to end users. When a new build comes out, I love checking this table to see what features Microsoft has coded, but isn’t quite ready to announce yet. When SQL Server 2017 came out, that’s how I unmasked snapshot materialized views, a…

Locks Taken During Indexed View Modifications

Last Updated September 2, 2018

Locking, Blocking, and Isolation Levels, SQL Server

Frankenblog This post has been nagging at me for a while, because I had seen it hinted about in several other places, but never written about beyond passing comments. A long while back, Conor Cunningham wrote: This same condition applies to indexed view maintenance, but I’ll save that for another day :). AFAIK he hasn’t…

[Video] Office Hours 2018/8/1 (With Transcriptions)

Last Updated August 4, 2018

Brent Ozar Unlimited Team

SQL Server, Videos

This week, Erik and Richie discuss monitoring tools, finding all unused tables across databases, query tuning, deleting vs hanging on to indexes, sharding databases, query editors, aggressively-locked indexes, why a plan would not be in the plan cache, and Richie’s current housing situation. Here’s the video on YouTube: You can register to attend next week’s Office Hours,…

How I Configure SQL Server Management Studio

Last Updated August 2, 2018

SQL Server Management Studio

Ever go into Tools-Options? SSMS has a stunning number of options these days. Here are some of my favorites: Documents options On the Documents options, I uncheck “Check for consistent line endings on load” because I constantly get scripts with all kinds of wacko line endings. That warning is a pain in the butt. On…

Indexing Strategies For Cross Apply

Last Updated June 8, 2018

Execution Plans, Indexing

Slicked Brad The apply operator remains one of my favorite tools, but much like CTEs and windowing functions, they’re not totally magical unless you look at your indexes. Starting with a dead simple query on the Users table, it’d be easy to come up with an ideal index for this one thing. Transact-SQL SELECT u.Id,…

[Video] Office Hours 2018/6/6 (With Transcriptions)

Last Updated June 8, 2018

Brent Ozar Unlimited Team

Videos

This week, Brent, Erik, Tara, and Richie discuss index maintenance, stats updates, the acquisition of GitHub, using linked servers to move tables across, talking to developers as a DBA, the best cloud option, setting the number of tempdb files, and their favorite SQL Server features. Here’s the video on YouTube: You can register to attend next week’s…

The Many Mysteries of Merge Joins

Last Updated April 8, 2018

Execution Plans, Indexing

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