July 6, 2012 at 4:40 am
On the server
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) ... Enterprise Edition (64-bit) on Windows NT 6.1 < X64 >
I have created simple table
CREATE TABLE dbo.T (a int)
GO
and fill it with some data:
declare @i int
SET @i = 0
BEGIN TRANSACTION
WHILE @i < 25000 --loop counter
BEGIN
IF @i%2=0 insert into dbo.T VALUES (100000+@i)
ELSE insert into dbo.T VALUES (-100000+@i)
set @i += 1
END
COMMIT TRANSACTION
GO
Next, the view was created:
CREATE VIEW V WITH SCHEMABINDING
AS
SELECT a
FROM dbo.T
GO
and index on that view:
CREATE UNIQUE CLUSTERED INDEX [IX_v] ON V (a)
What I want: I want for query
SELECT a FROM T WHERE a > X
query optimizer use seek by index IX_v. It's looks logically, doesn't it? Lets try:
SELECT a FROM T WHERE a > 110000
and we have Table T scan.
SELECT a FROM T WITH (INDEX(IX_v)) WHERE a > 110000
and we get error
Index 'IX_v' on table 'T' (specified in the FROM clause) does not exist.
Just for interest I check what happen, if data will be retrieved from view, not from table:
SELECT a FROM V WHERE a > 110000
and again Table T scan!
SELECT a FROM V WITH (INDEX(IX_v)) WHERE a > 110000
and again Table T scan, our hint just ignored, and no error/warning!!
SELECT a FROM V WITH (NOEXPAND) WHERE a > 110000
Phew, at last! Seek by IX_v index.
So, after all I have only 2 questions:
1. Why query optimizer resist to use IX_v index so hard?? :blink:
2. Does it all mean that I just CAN'T force to use IX_v index if my query reference table, not view? But why?? What's the problem if I, as DBA, pretty sure that index seek better than scan?
P.S. > in code above mean "more than" sign, just HTML problem in forum's engine.
July 6, 2012 at 4:51 am
Did you use the set options properly while creating the table,as well as indexed view and whiel querying the data?
Read in the end of the below whitepaper.
http://msdn.microsoft.com/en-us/library/cc917715.aspx
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 6, 2012 at 5:45 am
Shcherbunov Neil (7/6/2012)
So, after all I have only 2 questions:1. Why query optimizer resist to use IX_v index so hard?? :blink:
2. Does it all mean that I just CAN'T force to use IX_v index if my query reference table, not view? But why?? What's the problem if I, as DBA, pretty sure that index seek better than scan?
P.S.
>in code above mean "more than" sign, just HTML problem in forum's engine.
1. SQL Server won't sub in an indexed view if the query is trivial - and it is.
2. The index is on the view, not on the table.
3. NOEXPAND is an explicit instruction to use the view, not the index.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 6, 2012 at 10:54 am
Gullimeel (7/6/2012)
Did you use the set options properly while creating the table,as well as indexed view and whiel querying the data?
Thanks and Yes - I have inserted the code
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
before view/index create and the same block of code before data retrieval from table. No effect - table scan totally.
July 6, 2012 at 11:05 am
1. SQL Server won't sub in an indexed view if the query is trivial - and it is.
My guess - this statement bears the stamp of truth. If query optimizer consider the given query as "trivial" - it's all over, any view and any index on it ignored by optimizer absolutely, and that's that - we can do nothing about it, no any hints to help. So, if you want query optimizer in Enterprise Edition use index of view - give it more complex task. 🙂
July 6, 2012 at 11:39 am
Shcherbunov Neil (7/6/2012)
1. SQL Server won't sub in an indexed view if the query is trivial - and it is.
My guess - this statement bears the stamp of truth. If query optimizer consider the given query as "trivial" - it's all over, any view and any index on it ignored by optimizer absolutely, and that's that - we can do nothing about it, no any hints to help. So, if you want query optimizer in Enterprise Edition use index of view - give it more complex task. 🙂
Not quite - NOEXPAND is the hint to use the view.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 7, 2012 at 6:26 am
ChrisM@home (7/6/2012)
Not quite - NOEXPAND is the hint to use the view.
Yes, but ONLY if you reference view (not table!) in your query. See code example in start message - what if we want 'SELECT FROM T', and don't want 'SELECT FROM V'? How NOEXPAND can help us?
July 9, 2012 at 2:20 am
Shcherbunov Neil (7/7/2012)
ChrisM@home (7/6/2012)
Not quite - NOEXPAND is the hint to use the view.
Yes, but ONLY if you reference view (not table!) in your query. See code example in start message - what if we want 'SELECT FROM T', and don't want 'SELECT FROM V'? How NOEXPAND can help us?
Read the section in BOL: "Resolving indexes on views". The opposite of NOEXPAND (use the view) is "EXPAND VIEWS" (use the tables).
Whether you reference the (indexed) view or the table in your query, the optimiser will usually choose whichever has the lowest cost. It doesn't always make the correct choice, as your sample shows very well. If you determine by test that this is the case then you can override the choice of the optimiser with one of these two hints.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 9, 2012 at 4:44 am
ChrisM@Work (7/9/2012)
Shcherbunov Neil (7/7/2012)
ChrisM@home (7/6/2012)
Not quite - NOEXPAND is the hint to use the view.
Yes, but ONLY if you reference view (not table!) in your query. See code example in start message - what if we want 'SELECT FROM T', and don't want 'SELECT FROM V'? How NOEXPAND can help us?
Read the section in BOL: "Resolving indexes on views". The opposite of NOEXPAND (use the view) is "EXPAND VIEWS" (use the tables).
Whether you reference the (indexed) view or the table in your query, the optimiser will usually choose whichever has the lowest cost. It doesn't always make the correct choice, as your sample shows very well. If you determine by test that this is the case then you can override the choice of the optimiser with one of these two hints.
Yes, it's all correct. But - what I try to emphasize:
1. We have table T and this table doesn't have any index.
2. We have view V=SELECT * FROM T and cluster index iV on this view.
3. For some reasons (the exactly reason not so important in scenario I try to describe) query optimizer IGNORE iV for query SELECT * from T. Of course, it is "stand-alone" query, no any links to view V.
4. There is absolutely nothing to do - you just not able (with any hints) to force optimizer to use iV, IF you, at the same time, don't want to edit original query SELECT * from T.
5 But if you don't mind to re-write query as SELECT * from V - yes, NOEXPAND will rescue.
That's about it.
July 9, 2012 at 5:09 am
Shcherbunov Neil (7/9/2012)
ChrisM@Work (7/9/2012)
Shcherbunov Neil (7/7/2012)
ChrisM@home (7/6/2012)
Not quite - NOEXPAND is the hint to use the view.
Yes, but ONLY if you reference view (not table!) in your query. See code example in start message - what if we want 'SELECT FROM T', and don't want 'SELECT FROM V'? How NOEXPAND can help us?
Read the section in BOL: "Resolving indexes on views". The opposite of NOEXPAND (use the view) is "EXPAND VIEWS" (use the tables).
Whether you reference the (indexed) view or the table in your query, the optimiser will usually choose whichever has the lowest cost. It doesn't always make the correct choice, as your sample shows very well. If you determine by test that this is the case then you can override the choice of the optimiser with one of these two hints.
Yes, it's all correct. But - what I try to emphasize:
1. We have table T and this table doesn't have any index.
2. We have view V=SELECT * FROM T and cluster index iV on this view.
3. For some reasons (the exactly reason not so important in scenario I try to describe) query optimizer IGNORE iV for query SELECT * from T. Of course, it is "stand-alone" query, no any links to view V.
4. There is absolutely nothing to do - you just not able (with any hints) to force optimizer to use iV, IF you, at the same time, don't want to edit original query SELECT * from T.
5 But if you don't mind to re-write query as SELECT * from V - yes, NOEXPAND will rescue.
That's about it.
It works exactly as BOL states. I don't see a problem here.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply