Category: Query Tuning

Paul White continues a series on simple parameterization:

The details of how each data type is guessed are complex and incompletely documented. As a starting point, SQL Server infers a basic type from the textual representation of the value, then uses the smallest compatible subtype.

For a string of numbers without quotation marks or a decimal point, SQL Server chooses from tinyint, smallint, and integer. For such numbers beyond the range of an integer, SQL Server uses numeric with the smallest possible precision. For example, the number 2,147,483,648 is typed as numeric(10,0). The bigint type isn’t used for server-side parameterization. This paragraph explains the data types selected in the prior examples.

Read the whole thing to see what’s in and what’s out, as well as what this all means.

Comments closed

Monica Rathbun does the math:

Remove CONVERT/CAST from your WHERE clauses and JOINS when comparing to variables of different data types. Set their data types to match your table definitions before using them as a filter. Optimizing your queries this way will greatly reduce the amount of CPU time, reads, and I/O generated in your queries and allow your code to take better advantage of indexes.

This can quietly be a major performance issue.

Comments closed

Tibor Karaszi thinks about plan ages:

So what if your oldest plan is 3 only days old? Or 3 hours? Or 3 minutes?

Finding your most expensive queries based on such short “tail” is pretty meaningless and you end up wasting time tuning a set of queries that in the end wasn’t the most important ones, after all.

This isn’t necessarily something we always think about but Tibor makes a great point here.

Comments closed

Erik Darling plugs in the variables:

I’m a really big fan of using operator properties for a lot of things, at least visually. Where things sort of fall down for that is copying and pasting things out.

For some stuff, you still need to head down to the XML.

Click through to see how. Also read the comments for more information.

Comments closed

Paul White begins a new series:

This is the first part of a series about simple parameterization and trivial plans. These two compilation features are closely connected and have similar goals. Both target performance and efficiency for workloads frequently submitting simple statements.

Despite the “simple” and “trivial” names, both have subtle behaviours and implementation details that can make how they work difficult to understand. This series doesn’t dwell too long on the basics but concentrates on less well-known aspects likely to trip up even the most experienced database professionals.

In this first part, after a quick introduction, I look at the effects of simple parameterization on the plan cache.

Read on for more detail.

Comments closed

Erik Darling continues a series on understanding execution plans. First up, what some of those warnings mean:

The good news is that SQL Server’s query plans will attempt to warn you about problems. The bad news is that most of the warnings only show up in Actual Execution Plans. The worse news is that a lot of the warnings that try to be helpful in Estimated Execution plans can be pretty misleading.

Next, Erik reviews some interesting properties:

There’s a lot of stuff flying around in a query plan. Data and what not.

Sure, you can hover over operators and arrows and see some stuff, but if you really wanna see stuff — I mean REALLY wanna see stuff — you gotta get into the properties.

Check out both posts.

Comments closed

Erik Darling doesn’t need your costs:

Costs are okay for figuring out why SQL Server’s cost-based optimizer:

– Chose a particular query plan

– Chose a particular operator

Costs are not okay for figuring out:

– Which queries are the slowest

– Which queries you should tune first

– Which missing index requests are the most important

– Which part of a query plan was the slowest

Yep. And read on for more information.

Comments closed

Grant Fritchey implores us not to forget the SELECT/INSERT/UPDATE/whatever operator:

I’ve watched several people recently go straight to XML when reading execution plans because they didn’t know about the execution plan properties in the first operator. Now, don’t get me wrong. If going straight to the XML is working for you, that’s fine. Keep doing it. I’m absolutely not questioning how anyone does things. I just want people to know that “hidden” information isn’t so much hidden as much as it’s not too obvious.

Read on to see what this contains.

Comments closed

Erik Darling goes scalar hunting:

This is awful. It really is. It’s so awful. These operators skated by undetected for years. Acting so innocent with their 0% cost.

Subprime operators, or something.

In this post, I’m going to show you how compute scalars hide work, and how interpreting them in actual execution plans can even be tricky.

I’m now imagining Erik as the Steve Irwin of the query tuning world. And it’s kind of working for me.

Comments closed