Performance Tuning
ColumnStore Indexes And Recursive CTEs
Alone Together
When I think about SQL Server features, I often picture a high school cafeteria.
Index Evaluation and Specialized Indexes
Missing-index advice, filtered indexes, columnstore, and over-indexing.
62 associated posts59 primary posts
Performance Tuning
Checking for Strange Client Settings with sys.dm_exec_sessions
In my performance training class, people get really excited when I cover filtered indexes, computed columns, and indexed views.
Then they get a really strange expression on their face when I talk about one of the biggest drawbacks: if your connection options aren't set correctly, your delete/update/insert (DUI) operations will fail. That gave me a really fun time once when I implemented an indexed view, and they called me back the next day screaming because their app was down. My bad. (sigh)
Performance Tuning
Indexed Views And Data Modifications
Love/Hate When indexed views help, they make aggregation queries super fast. There are some kind of horrible limitations and requirements. When they hurt, your modifications slow to a crawl because SQL has to not only keep your horrible indexes synchronized, it also has to keep your indexed view indexes in sync now, too. But SQL…
Performance Tuning
Crappy Missing Index Requests
When you're tuning queries
It's sort of a relief when the first time you get your hands on it, you get the plan and there's a missing index request. Even if it's not a super high-value one, something in there is crying for help. Where there's smoke, there's a bingo parlor.
But does adding missing indexes from requests always make things better?
[Video] Office Hours 2017/02/08 (With Transcriptions)
This week, Brent, Erik, Tara, and Richie discuss compatibility levels, SQL server SPN problems, Always On Availability Groups, failover clustering, capturing DML stuff, disc defragmentation, replication snapshots and reindexing, load testing, PowerShell, and whether you should pursue the path of a DBA.
Here’s the video on YouTube:
Performance Tuning
Filtered Indexes and Variables: Less Doom and Gloom
It Is Known
That when you use filtered indexes, they get ignored when your queries are parameterized. This is a Plan Caching Thing©, of course. The simplest example is a bit column with a filtered index. If your index is on WHERE Bit = 1, it doesn't have data for WHERE Bit = 0. That index would only be suitable for one variation of the query, so caching a plan that uses an index which can't be reused for every variation isn't feasible.
Performance Tuning
Selective XML Indexes: Not Bad At All
Promises, Promises
I said I'd follow up on this, and here it is. I'm by no means the world's foremost authority on these, I just played with a simple example until I figured out what worked the best for my query. So you can all play along at home, I'm using XML we all have access to: Query Plans!
Performance Tuning
Top 5 Overlooked Index Features
I see a lot of indexes Most of them are pretty vanilla. Some are pretty memorable, like one that I saw with 93 INCLUDES. A lot of things that people could do with indexes get overlooked, either because they don't know about them, or they're not sure when they'll help. So, here goes my attempt…
Performance Tuning
Where Do Missing Index Requests Come From?
Be honest, here You don't care about most indexing problems. Duplicates, Borderline Duplicates, Unused, Wide, Non-aligned Partitions... All you're here for are the Missing Indexes. Wham, bam, your query finishes in less than a second, ma'am. Take this quarter, go downtown and have a rat gnaw that thing off your face. And I get it.…
Performance Tuning
Filtered Indexes: Just Add Includes
I found a quirky thing recently While playing with filtered indexes, I noticed something odd. By 'playing with' I mean 'calling them horrible names' and 'admiring the way other platforms implemented them'. I sort of wrote about a similar topic in discussing indexing for windowing functions. It turns out that a recent annoyance could also…
SQL Server Features I’d Like To See, PostgreSQL Edition
BUT FRANCE HAS A FREE PONY That's right. PostgreSQL is basically free. The MIT license is like the Church of England of licenses. They do not care what you do. But I care what they do! If you read the first part of this article, you saw some Oracle features that I wish SQL Server…
Performance Tuning
Filtered Indexes and IS NOT NULL
Filtered indexes can be tricky. Just getting your queries to use the filtered index can be a real pain. Parameterization may mean it's "unsafe" to use the filtered index. The optimizer might want the filtered column in your key or include column list when it doesn't seem like it should have to be there. The weather might be cloudy.
Performance Tuning
Why is This Partitioned Query Slower?
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…
Performance Tuning
Using “OR” and “IN” with SQL Server’s Filtered Indexes
You can't do everything with filtered indexes in SQL Server. For instance, you can't create the following index:
[crayon-6ae6a960053e4390500932/]
If you try, you'll get the error message:
[crayon-6ae6a960053ea930756826/]
Instead, you can use 'IN' and create the index this way:
[crayon-6ae6a960053ed320210675/]
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.
News & Opinion
Brent’s Bad Idea Jeans [Video]
Over the years, I've come up with some rather “interesting” answers to SQL Server problems including:
Warming up SQL Server's buffer pool by selecting all the data from all the tables
Creating indexes with triggers
Adding nonclustered indexes to clustered columnstore indexes
Compressing backups the hard way
Putting databases on a RAM drive
Performance Tuning
How to Add Nonclustered Indexes to Clustered Columnstore Indexes
SQL Server 2012 introduced nonclustered columnstore indexes, but I never saw them used in the wild simply because once created, they made the underlying table read-only. Not a lot of folks like read-only tables. (Bad news, by the way - that limitation hasn't disappeared in 2014.) SQL Server 2014 brings clustered columnstore indexes, and they're…
Performance Tuning
Filtered Indexes vs. Table Partitioning
It was a dark and stormy… Oh, wrong story. It was actually a warm, sunny afternoon in Charlotte, NC. I was presenting “Index Methods You’re Not Using” at PASS Summit. In this talk, I discussed how indexed views, filtered indexes, and compressed indexes can improve your query performance by reducing I/O.
From stage right, an intrepid audience member raised his hand and asked, “Can you think of an example of when you would use filtered indexes instead of partitioning?”
Performance Tuning
What You Can (and Can’t) Do With Indexed Views
Views are logical objects in SQL Server databases that present you with a “virtual table”. Views are typically created for one of three reasons: security, simplification, or aggregation. Security: we create views so that a user can read specific columns out of certain tables, but not all the data. Simplification: sometimes, it’s easier to write…
Performance Tuning
Filtered Indexes and Dynamic SQL
I’ve been told that an attendee at the PASS Summit pre-conference event asked about using dynamic SQL to get around some of the problems with filtered indexes. I’m not entirely sure what the question was, but it did give me the opportunity to play around with filtered indexes and write some simple demo code to illustrate just the kind of shenanigans that you can get up to.
Performance Tuning
What You Can (and Can’t) Do With Filtered Indexes
When you write a query, you (usually) don’t want to return all the rows from the table(s) involved – so you add a WHERE clause to the statement. This ensures that fewer rows are returned to the client - but doesn't reduce the amount of I/O done to get the results. When you create a…
