Every now and then, a client says, “We want to add indexes to this third party application, but if we do, we’ll lose support.”
No problem – enter indexed views.
Say our vendor app is the Stack Overflow database, and the app constantly queries Users by display name:
|
1 2 3 4 5 6 7 8 |
USE StackOverflow; GO DropIndexes; SET STATISTICS IO ON; GO SELECT * FROM dbo.Users WHERE DisplayName = N'Brent Ozar'; |
That query plan scans the Users table because there’s no index on DisplayName:
And our mean ol’ nasty vendor won’t let us add any indexes. No problem – let’s add a view:
|
1 2 3 4 5 |
CREATE OR ALTER VIEW dbo.Users_ByDisplayName WITH SCHEMABINDING AS SELECT DisplayName, Id FROM dbo.Users; GO |
A regular view like that is just syntactic sugar that makes it easier to write queries. You hide the complexity in the view, and then people can easily select stuff from the view without understanding everything that the view is pulling together. A view is just a T-SQL shortcut, but it doesn’t change the database structure.
However, you can turn a regular view into an indexed view (or a materialized view, different terms, same thing) by creating a unique clustered index atop the view.
Normally, when we think about clustered indexes, we would assume that the Id column is the right one to use here, since it’s unique. However, the entire purpose of what we’re about to do is just to give ourselves an index on DisplayName – so let’s cluster on that:
|
1 2 3 |
CREATE UNIQUE CLUSTERED INDEX DisplayName ON dbo.Users_ByDisplayName(DisplayName, Id); GO |
Then run our query again and check out its new execution plan:
SQL Server Enterprise Edition says, “Hey, you’re looking for Users with a specific DisplayName – it just so happens I’ve got a copy of that over here, sorted in order!” (Standard Edition doesn’t know this trick.) It produces a nifty execution plan with a key lookup – without actually having an index operation on the Users table itself.
The real beauty, though, is that when our mean ol’ nasty vendor looks at the indexes on the Users table, they have no idea that we’ve done anything. If we need to open a support case with the vendor or upgrade the software, we can temporarily drop our indexed views, and then recreate ’em after the support call is over. (That’s actually important to do, too – if the vendor tries to alter the table, their scripts can fail due to the presence of the indexed view.)
Indexed views have tons of gotchas, and explaining ’em all is way outside of the scope of this blog post. I demo ’em in the live Mastering Index Tuning class next month, or if you’ve got the recordings, head over here now.
Speaking of which, you know who else has the recordings? Paul White and Michael J. Swart, both of whom had good answers to the challenge in Monday’s blog post. Paul used the indexed view, whereas Michael had a more, shall we say, “creative” solution.
19 Comments. Leave new
This is a very handy trick.
I did an end-run around the requirements for Monday’s challenge. In my defense, we were encouraged to be creative 🙂
“If we need to open a support case with the vendor or upgrade the software, we can temporarily drop our indexed views, and then recreate ’em after the support call is over.”
Isn’t that also true of the (ostensibly less exotic) solution of adding the non-clustered index?
It is – but now it’s your option as to whether to go to the hassle for short support calls.
I think I’m missing something. If any time I call the vendor I’m going to clean up my “enhancements” and add them back when the vendor engagement is over, what is the distinction being drawn between an indexed view and a non-clustered index?
Because you only have to drop em if the ISV finds em. That’s why I cleverly called them invisible indexes – but I guess my idea was so good that you missed the very title of the post.
Invisible indeed, heh!
I’m with Ben on this, Brent. Okay, they’re “invisible”, but the example use case is useless. And, if you’re going to risk leaving it there during a support call, I’d argue a standard index might be less noticeable by support because there’s no schema binding on the table.
They’re useless for folks who don’t read the blog post title. Got it. I’ll keep that in mind for future blog posts. Thanks for stopping by!
We have such a vendor solution (a CMS) that has hundreds of tables and indexed views in an Azure SQL Database. The tables have very few (if any) nonclustered indexes as delivered and supported, and there are lots of queries that join large numbers of them together. Adding a non-clustered indexes as needed to both tables and their indexed views seemed the best way forward 5 years ago, and still seems good today.
We follow a strict naming convention for the indexes we create, and I have one script to create a script that will recreate them all and then drop them. We run that before vendor version upgrades, and then run the generated “re-create” script afterward (and fix any problems due to changes in the data model between versions). So far we’ve been lucky and not needed to go through this fire drill for support calls.
Because the vendor is using indexed views, I think ours is a special case because I don’t think we want to end up with nested indexed views. Would you agree?
By the way, I had an annual live class subscription a couple of years ago, and recommend them to every DBA that wants to get better at performance tuning. I learned a lot, even after having done this kind of work for almost 10 years.
Thanks, glad you like the classes!
Personalized advice on your own indexes is a little beyond the scope of something I’d want to do in a blog post – but definitely feel free to click Consulting at the top of the site if you do need my own personalized advice. (My guess is you probably don’t for a question like this – you can probably run your own experiments quickly and cost-effectively.)
Those good ol’ $7000 a core tricks 🙂
Hmmm… I’ve started investigation with an index creation on the Users.DisplayName column and didn’t delete it all the time…. LOL
And thanks for the lesson Brent!
Indexed views sound nice until base table stop being updatable (not the views but the referenced base tables) because a setting is not being correctly set (like ANSI_WARNINGS) for the user connection by the 3-rd party application.
Yeah, check out the last 2 paragraphs of the blog post.
Or depending on how big of a client you and how much you pay vendor in yearly support, you tell them to fix their code or you will find new vendor!
That sounds like a wonderful world where that happens… I’ve never worked for a big enough customer that can demand that sort of thing. My experience is that you tell them there is an issue, they look at it and say ‘oh, that’s an edge case, we can’t fix it without breaking things for other customers.’ After hearing that multiple times from either the same or different vendors, most of us look for things like Brent talked about to fix it on our own.
(Almost as bad as the comment “wow, I’ve never seen the software do that before…” Which has happened at least once every 3 years at my company.)
I’ve worked for a customer that big, but rather than fixing the software, the vendor just gave our company a break on the licensing fees. “How much more work would you say this problem is adding to your staff’s workload? About one week a month? Okay, we’ll give you a $50K/year discount on your multi-million-dollar annual license fee.”
The executives were happy with that. The tech folks, not so much.
“[…] it just so happens I’ve got a copy of that over here”. How did you find out that Enterprise Edition was behaving that way?
This is a [well] hidden gem. Thanks for posting!
I still feel like I’m looking for an invisible needle in the proverbial haystack though. My database has a table with a generic bigint identity single column clustered index. Queries on that table with a WHERE clause on different columns are crazy fast (~ 2sec. / ~6M rows) and the actual execution plan only shows a Clustered Index Scan which makes zero sense to me.
There is a view that references this table, and another view referencing the first one, but neither of them are indexed.
It’s a complete mystery. FWIW, I’m running Standard Edition.
You’re welcome, glad you enjoyed it.
For personalized help with your particular situation, you can click Consulting at the top of the screen.