Execution Plans Archives - Page 4 of 11

Performance Tuning

Getting Sneaky With Forced Parameterization

November 27, 2018

Silly Rules
I've blogged about some of the silly rules about where Forced Parameterization doesn't work.

One rule that really irked me is this one:
The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO, or FOR XML clauses of a query.
TOP and FOR XML, get used, like, everywhere.

Read more Be the first to comment

Performance Tuning

Not So Forced Parameterization

November 26, 2018

Asking The Wrong Question
Sometimes, when you wanna turn on a feature, you spend so much time wondering if you should, you don't bother asking if it'll even work when you do.

There are a long list of things that are incompatible with Forced Parameterization, on a page that's pretty hard to find.

Read more Be the first to comment

Performance Tuning

Adventures In Foreign Keys 5: How Join Elimination Makes Queries Faster

November 16, 2018

FINALLY...
This is the last post I'll write about foreign keys for a while. Maybe ever.

Let's face it, most developers probably find them more annoying than useful, and if you didn't implement them when you first started designing your database, you're not likely to go back and start trying to add them in.

Performance Tuning

Adventures In Foreign Keys 4: How to Index Foreign Keys

November 15, 2018

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 to abide by, depending a bit on how they're used.

Performance Tuning

Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly

November 14, 2018

Legendreary
In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up.

I didn't even get to the point where I wanted to test all those relationships.

Performance Tuning

Adventures In Foreign Keys 2: Common Foreign Key Errors

November 13, 2018

This week, we're all about foreign keys. Yesterday's post covered scripts to set up the Stack Overflow database to get ready, eliminating data that would violate FK relationships.
You Had Two Jobs!
Let's say I wanted to implement Foreign Keys to do two things

If a user deletes their account, all of their badges, comments, and posts will also get deleted
If a user deletes their post, all of the comments and votes will also get deleted

Performance Tuning

Adventures In Foreign Keys 1: Setting Up Foreign Keys in Stack Overflow

November 12, 2018

In A Foreign Key, In A Foreign Table Much of what people want from foreign keys, like referential integrity and join elimination, are only as guaranteed as much as SQL Server can trust your constraints (and even then...). The same goes for check constraints, too. Thankfully, things like Primary Keys and Unique Constraints are sort…

Performance Tuning

The Curse of Cursor Options

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:…

Performance Tuning

How Check Constraints MIGHT Improve Your Queries and Missing Index Requests

October 31, 2018

The more SQL Server knows about your data, the better your query plans can get.

Say you've got an app that's designed to store multiple companies in a single database - but you don't actually use it that way. All of the data in a given database is actually for the same company.

Performance Tuning

Filtered Indexes vs Parameterization (Again)

October 26, 2018

At First I Was Like... This won't work at all, because parameters are a known enemy of filtered indexes. But it turns out that some filtered indexes can be used for parameterized queries, whether they're from stored procedures, dynamic SQL, or in databases with forced parameterization enabled. Unfortunately, it seems limited to filtering out NULLs…

Performance Tuning

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

October 25, 2018

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.

Performance Tuning

Batch Mode For Row Store: What Does It Help With?

October 24, 2018

SQL Server 2019

Introduces something pretty cool: the ability for row store indexes to be processed in batch mode. Batch mode is, of course, the preferred processing realm of column store indexes.

Read more Be the first to comment

Performance Tuning

Is Cost Threshold for Parallelism Measured in Seconds?

October 19, 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: [crayon-6a6ed259d4eb5719383400/] And with my server set at SQL Server's default settings (Cost Threshold of 5, MAXDOP of 0), count how many…

Performance Tuning

Skewing Parallelism For Fun And Profit

October 17, 2018

What Is Skewed Parallelism?
When queries go parallel, some assumptions get made:

There's enough work to keep multiple threads busy
Each thread will get an equal amount of work to do

Performance Tuning

Index Tuning Week: Missed Missing Index Opportunities

October 12, 2018

A Reminder
With a new baby in the house, I often find myself comparing her to various things.

Barnacles. Ham. Pugs.

Performance Tuning

Making The Query Plan Profile Hint More Useful

October 5, 2018

Way Back When I blogged about the new USE HINT that logs query plans that use it to a new XE session. The use hint and session both share the same name -- query_plan_profile. There are currently some rather unfortunate limitations to the event in the GUI. Right now, there's no query plan tab so you…

Performance Tuning

SQL Server Management Studio 18’s Execution Plans Will Change The Way You Look At Plan Tuning

October 4, 2018

The preview of the next version of SQL Server Management Studio is out, and it has a radical improvement to query plans that will shock and amaze you. It's best to just show you: You can see the estimated and actual number of rows right there on the query plan just like live query plans!…

Performance Tuning

We Need to Talk About the Warnings In Your Query Plans.

October 3, 2018

SQL Server, thanks for coming today. Have a seat.

Yes, this might seem odd - there are a lot of us here.

Performance Tuning

The New Lightweight Query Plan Profile Hint

October 1, 2018

Recent Updates
To SQL Server 2016 and 2017 introduced a new USE HINT that lets you direct an actual execution plan to a new Extended Event, called query_plan_profile.

The hint by itself doesn't do anything, and the XE by itself doesn't do anything. You need to have both.

Read more Be the first to comment

Performance Tuning

When You Need to Tune A View, Don’t Just Get Its Plan

September 28, 2018

Say your database has a view, and everybody's queries use it. Let's take the Stack Overflow database and create this view:
[crayon-6a6ed259d6248717502334/]
Not pretty, but that's why you're here, right? Real world code is ugly.

And say my users are running queries like these:
[crayon-6a6ed259d624d359839126/]
Notice that the select, where, order by etc all have variations in them. They're not all asking for the same fields, which means SQL Server can make different decisions about: