February 10, 2012 at 8:48 am
Good Question, I guessed right as I haven't worked wtih 2012 at all.
February 10, 2012 at 9:22 am
interesting question - tks
February 10, 2012 at 9:29 am
thanks for the question.
Hugo, thanks for the discussion points.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 10, 2012 at 9:38 am
sknox (2/10/2012)
Hugo Kornelis (2/10/2012)
I hate questions where I have to second-guess the author. Did the author mean that the optimizer would choose to use the indexed view automatically? Or did he mean that you could make the optimizer use it by using hints? I took a 50% chance gamble, and I chose wrong. 🙁If I'd meant automatically, I'd have said automatically. 😛
You might do that. However, there have been several examples of questions where a detail like that was left out of the question. I would have liked the question more if it had been clear that the "automatically" was intentionally left out. I guessed at the meaning right but still got it wrong because I thought that indexed views weren't supported at all in Express, both by initial guess and after research. Which means that I learned something from from it so thank you. I'm looking forward to the article.
February 10, 2012 at 9:42 am
Hugo
And I have never seen a real-world case where there was more than a single index on a view
Does that mean that there couldn't be more than one? Does that also mean that the index hint will n ot work?
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 10, 2012 at 10:08 am
sjimmo (2/10/2012)
Hugo
And I have never seen a real-world case where there was more than a single index on a view
Does that mean that there couldn't be more than one? Does that also mean that the index hint will n ot work?
In the tests I did, I created a view with more than one index, and the query optimizer would use the second (nonclustered) index in Enterprise editions, or with the NOEXPAND hint. However, my tests were to prove the capability, and were not what I would consider a real-world case.
February 10, 2012 at 10:40 am
sknox
I created a view with more than one index, and the query optimizer would use the second (nonclustered) index in Enterprise editions, or with the NOEXPAND hint
The point I was trying to get Hugo to say was that you could have more than one index, in which case the index hint or NOEXPAND would work mostly as described.
He ignored the index hint in the question that he was responding to, and insuating that based upon his experience that there is only one index to an indexed view.
In my opinion, he should have addressed the whole question/comment.
But that is only my opinion.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 10, 2012 at 11:31 am
sjimmo (2/10/2012)
Hugo
And I have never seen a real-world case where there was more than a single index on a view
Does that mean that there couldn't be more than one? Does that also mean that the index hint will n ot work?
There can definitely be more than one. Indexing a view always has to start with a clustered index, but you can then add as many nonclustered indexes as you want (well, up to some insanely high maximum that I'm too lazy to look up right now; it used to be 250, but it has increased in SQL 2008, I think).
If you use NOEXPAND, you force the use of any index on the view; the optimizer is free to pick which one.
If you use an index hint without NOEXPAND, I'm not sure what would happen. It might depend on the edition, but I have no time to test this right now. Sorry. Maybe someone else can test this and post the results here? If you do, please include edition you used, as this might influence the results.
February 10, 2012 at 11:38 am
Hugo Kornelis (2/10/2012)
If you use an index hint without NOEXPAND, I'm not sure what would happen. It might depend on the edition, but I have no time to test this right now. Sorry. Maybe someone else can test this and post the results here? If you do, please include edition you used, as this might influence the results.
Maybe that's something that can go into the article that sknox is writing.
February 10, 2012 at 3:43 pm
I'm afraid I have to agree with Hugo on this one. The wording of the question left me to believe it was intended to mean "without using specific hints". Ah well. It's a good question, just would have benefitted from better phrasing.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 10, 2012 at 4:37 pm
sknox (2/10/2012)
Hugo Kornelis (2/10/2012)
I hate questions where I have to second-guess the author. Did the author mean that the optimizer would choose to use the indexed view automatically? Or did he mean that you could make the optimizer use it by using hints? I took a 50% chance gamble, and I chose wrong. 🙁If I'd meant automatically, I'd have said automatically. 😛
Maybe "force the query optimizer to use it" instead of "have the query optimizer use it" would have made your intensions clearer. Because like Hugo in the end I took a guess at what you meant. 😀
February 10, 2012 at 5:33 pm
Agree it's a good question, but poorly worded.
February 10, 2012 at 9:56 pm
The answer included this statement:
"The query optimizer in lower editions will only consider the indexed view if you use the NOEXPAND table hint."
In SQL Server 2008 Standard with no SP, wouldn't the INDEX(0) hint allow for the optimizer to scan the indexed view?
February 10, 2012 at 10:27 pm
BREAK
Is this worth of logging a documentation bug?
If it is, please help me with the wording and I will make sure it will get triaged.
Thanks.
February 11, 2012 at 3:55 am
Rowles (2/10/2012)
The answer included this statement:"The query optimizer in lower editions will only consider the indexed view if you use the NOEXPAND table hint."
In SQL Server 2008 Standard with no SP, wouldn't the INDEX(0) hint allow for the optimizer to scan the indexed view?
I don't have Standard Edition, so I could not test this. But I did get around to testing the use of index hints with a view on Developer Edition. Here's my script:
USE tempdb;
GO
CREATE TABLE dbo.t1 (a int, b int,
CONSTRAINT PK_t1 PRIMARY KEY (a));
INSERT dbo.t1 (a, b)
VALUES (1,1), (2,2), (3,1)
GO
CREATE VIEW dbo.v1 WITH SCHEMABINDING
AS SELECT b, SUM(a) AS SumA, COUNT_BIG(*) AS Cnt
FROM dbo.t1
GROUP BY b;
GO
CREATE UNIQUE CLUSTERED INDEX ix1 ON dbo.v1(b);
GO
SELECT * FROM dbo.v1; -- No index hint
SELECT * FROM dbo.v1 WITH (INDEX = 0); -- Clustered index scan (by number)
SELECT * FROM dbo.v1 WITH (INDEX = 1); -- Clustered index scan or seek (by number)
SELECT * FROM dbo.v1 WITH (INDEX = 5); -- Nonexisting index
SELECT * FROM dbo.v1 WITH (INDEX = ix1); -- Index hint by name
SELECT * FROM dbo.v1 WITH (INDEX = ix2); -- Nonexisting index
GO
I executed this code with the actual execution plan turned on. All five queries in the last batch used the index on the view, whereas I would have expected the two that attempt to force a non-existing index to produce errors.
Since Developer Edition picks the indexed view anyway, even without hinting, I can't assess if the index hints that properly specify the clustered index by number or name had any effect or not. Since the incorrect hints did not result in any errors, my guess is that index hinits after a view name are simply ignored, regardless of whether the view is indexed or not. But I'll have to wait until someone with Standard Edition is prepared to run this same code to confirm or deny this assumption.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply