How SQL Server compiles, optimizes, and executes individual queries.
573 associated posts292 primary posts
It's tempting to think that table partitioning will improve query performance. After all, it's an Enterprise Edition feature-- it must have a lot of magic, right? Table partitioning does have magic for the right situations. It shines when you want to add a large amount of data to a table or remove a large amount…
“Throughout history, slow queries are the normal condition of man. Indexes which permit this norm to be exceeded — here and there, now and then — are the work of an extremely small minority, frequently despised, often condemned, and almost always opposed by all right-thinking people who don't think bit columns are selective enough to…
SQL server loves unique indexes
Why? Because it's lazy. Just like you. If you had to spend all day flipping pages around, you'd probably be even lazier. Thank Codd someone figured out how to make a computer do it. There's some code below, along with some screen shots, but...
TL;DR
i've seen this happen
Especially if you've just inherited a database, or started using a vendor application. This can also be the result of inexperienced developers having free reign over index design.
Unless you're running regular health checks on your indexes with something like our sp_BlitzIndex® tool, you might not catch immediately that you have a heap of HEAPs in your database.
During some testing with SQL Server 2014's new cardinality estimator, I noticed something fun: the new CE can give you different index recommendations than the old one. I'm using the public Stack Overflow database export, and I'm running this Jon Skeet comparison query from Data.StackExchange.com. (Note that it has something a little tricky at the…
It's not glamorous And on your list of things that aren't going fast enough, it's probably pretty low. But you can get some pretty dramatic gains from indexes that cover columns you're performing aggregations on. We'll take a quick walk down demo lane in a moment, using the Stack Overflow database. Query outta nowhere! [crayon-6a70a953d8c99430068784/]…
When you migrate to SQL Server 2014, rather than turning on the new cardinality estimator right away, I recommend sticking with the old cardinality estimator initially by leaving your database in SQL Server 2012 compatibility mode. Let the database settle down for a few weeks, let folks air out their grievances, and give yourself some time to make sure the system is working at least as well as the old system.
Update 2016: Microsoft recognized this problem, and added a new way to add powerful query hints to SQL Server 2016.
The QUERYTRACEON hint can be particularly useful for testing the new cardinality estimator in SQL Server 2014 on individual queries using Trace Flag 2312, if your database is still using compatibility level 110:
We had some great questions about trivial execution plans in SQL Server in our Advanced Querying and Indexing class a few weeks ago. Here's a little glimpse into what we talked about.
For really simple queries, SQL Server can use "trivial optimization". If there's a very limited number of ways to run the query, why do a bunch of fancy, CPU burning cost-based optimization? Just chuck the plan at the query and let it go!
When you work with the same database server day in and day out, you can get a bit blinded by your assumptions. You might just assume that the way SQL Server does it is how every database does it.
But check out these features from other database platforms:
Brent buys lunch for the ladies The purpose of this post is to show a bit of syntax that often gets overlooked in favor of using query hints to force joins to occur in a particular order. We'll start by creating three tables. One for employees, one for orders, and one for items in the…
When you create an index in SQL Server with multiple columns, behind the scenes it creates a related multi-column statistic for the index. This statistic gives SQL Server some information about the relationship between the columns that it can use for row estimates when running queries.
But what if you use 'included' columns in the index? Do they get information recorded in the statistics?
We've all had this problem before - the production server is significantly more powerful than the development server. You tune a query and you're pretty sure you've made it faster, but there's that doubt in the back of your mind. There are so many things that can change performance - memory, CPU count, disk performance…
By now, you have probably heard of CTEs And you may have even heard them referred to as Inline Views. Really, an Inline View can be any type of derived table. It's very easy to illustrate when one may turn into a performance problem with CTEs, if you aren't careful. A lot of people think…
Learn the basics of Foreign Keys in SQL Server: trust, join elimination, cascading deletes, and indexing child keys. Limited on time? Check out the Table of Contents below.
https://www.youtube.com/watch?v=BkNOQgV5Zv8
Can I upgrade an existing instance without migrating?
This is nothing against SQL Server 2014, but I can't stand in-place upgrades. Over the years I've had in-place upgrades work flawlessly on a few instances, and then had an install issue cause it to fail in the middle on other instances. Usually the critical instances, just because I'm not always lucky. And when upgrade fails, it doesn't always roll back completely, or allow you to just re-run it. You may be down for a good long time.
You can't do everything with filtered indexes in SQL Server. For instance, you can't create the following index:
[crayon-6a70a953ddd0d535383833/]
If you try, you'll get the error message:
[crayon-6a70a953ddd1c169332533/]
Instead, you can use 'IN' and create the index this way:
[crayon-6a70a953ddd20693256293/]
That works-- and good news, even queries written with 'OR' can use that filtered index, because SQL Server is clever like that. Here's an execution plan that shows it in action.
I've written a terrible query. It might be the worst query in the world. Sure, there are other queries that may be slower, but this query is terrible and elegantly short.
The query is gloriously bad for a two reasons:
Parallelism can be confusing. A single query can have multiple operators that run at the same time. Each of these operators may decide to use multiple threads. You set SQL Server's "max degree of parallelism" to control the number of processors that can be used, but it's not immediately obvious what this means. Does this setting…
"Should a query get to use more than one CPU core?" That's an important question for your SQL Server. If you're not sure what parallelism is, get started by exploring the mysteries of CXPACKET with Brent. He'll introduce you to the the setting, 'Cost Threshold for Parallelism'.
Let's test Cost Threshold for Parallelism
I generate an estimated execution plan for the following query. I'm running against a copy of the StackOverflow database that doesn't have many indexes.
