October 29, 2015 at 7:54 am
Large table.
Large table, with fields A through Z.
Clustered index on a item A (int), B (int).
index on the items B,C,D,E,F,G,H (7 indexes).
One Covering index on A,B,D,F,H,J
Query,
Select B,D,F,H,J from Large_Table where F=@a_value
Can SQL-server use an index and then use the covering index to collect the data?
(And therefore not using the 'main' table).
Or does it read the main table (clustered index) after an index is used ?
Ben
-- ben brugman
-- 20151029
--
-- Create an example table.
--
CREATE TABLE Large_Table
(
A int NULL,
B int NULL,
C varchar(20) NULL,
D datetime NULL,
E int NULL,
F int NULL,
G int NULL,
H int NULL,
I int NULL,
J int NULL,
K int NULL,
L int varchar(200) NULL,
M int varchar(200) NULL,
N int NULL,
) ON [PRIMARY]
CREATE CLUSTERED INDEX Cluster_AB ON dbo.Large_Table
(
A,
B
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Large_Table_B ON dbo.Large_Table
(
B
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Large_Table_C ON dbo.Large_Table
(C) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Large_Table_D ON dbo.Large_Table
(D) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Large_Table_E ON dbo.Large_Table
(E) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Large_Table_F ON dbo.Large_Table
(F) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Large_Table_G ON dbo.Large_Table
(G) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Large_Table_H ON dbo.Large_Table
(H) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Large_Table_ABDFHJ ON dbo.Large_Table
(A,B,D,F,H,J) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Example query 1.
Select B,D,F,H,J from Large_Table where F=15
-- Example query 2.
Select B,D,F,H,J from Large_Table where F>15 and F< 131
October 29, 2015 at 8:12 am
No. Lookups are always to the clustered index.
That will either use the index on F and do key lookups to the cluster or will scan the covering index if the lookups are too expensive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2015 at 8:30 am
GilaMonster (10/29/2015)
No. Lookups are always to the clustered index.That will either use the index on F and do key lookups to the cluster or will scan the covering index if the lookups are too expensive.
Thanks,
We have to reconsider the usage of the covering index.
Ben
October 29, 2015 at 5:26 pm
It depends. If the nonclustered index contains all columns needed to satisfy the query, SQL will not go back to the main table.
If you (almost) search the table by F, you should cluster the table on F. You might be able to get rid of most (or even all) of the nonclustered, covering indexes then.
The clustered index is the most critical choice for table performance. Don't pick it based on a catchy saying of some type, but on what is best for that particular table's usage.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 30, 2015 at 1:54 am
October 30, 2015 at 7:12 am
The Question was:
Can SQL-server use an index and then use the covering index to collect the data?
(And therefore not using the 'main' table).
Answers:
GilaMonster (10/29/2015)
No. Lookups are always to the clustered index
ScottPletcher (10/29/2015)
It depends. ...
Jayanth_Kurup (10/30/2015)
Depending on carnality of the value @a_value in Column F ...
These anwsers do not agree with each other. I tend to go with Gail's anwser.
Please rectify me if there is a situation where the anwser could be yes.
Or did I misfrase the question?
Ben.
Ben
October 30, 2015 at 7:32 am
ben.brugman (10/30/2015)
The Question was:Can SQL-server use an index and then use the covering index to collect the data?
(And therefore not using the 'main' table).
Answers:
GilaMonster (10/29/2015)
No. Lookups are always to the clustered indexScottPletcher (10/29/2015)
It depends. ...Jayanth_Kurup (10/30/2015)
Depending on carnality of the value @a_value in Column F ...These anwsers do not agree with each other. I tend to go with Gail's anwser.
Please rectify me if there is a situation where the anwser could be yes.
Or did I misfrase the question?
Ben.
Ben
Key lookups are always to the clustered index - but there's "Index intersect" which blurs the issue.
"then use the covering index" - I think you're missing the point about covering indexes. The index is covering for the query (contains all of the columns referenced by the query from that table) or it isn't.
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
October 30, 2015 at 8:02 am
When it comes to fetching data from a table, there are many ways it can be done. The SQL optimizer tries to find the best plan within limits, these limits include execution time, number of rows being fetched, current resource utilization , hints etc.
So while SQL uses the Clustered Index when performing lookups what the OP needs to keep in mind is that a Lookup is not guaranteed to happen every time.
October 30, 2015 at 8:28 am
ChrisM@Work (10/30/2015)
Key lookups are always to the clustered index - but there's "Index intersect" which blurs the issue.
In the question there was an ordering (the word 'then'), so the intersect was not intended within the question.
"then use the covering index" - I think you're missing the point about covering indexes. The index is covering for the query (contains all of the columns referenced by the query from that table) or it isn't.
In the example selected columns are B,D,F,H,J these columns are covered by the 'covering' index.
So this index is covering for the query. (So I do not understand what you mean that I am missing the point).
Technically it would be possible using the index containing the F column and then using the Covering index table. (Fields A and B are present in the F index, they could be used within the covering index). As Gail explained this does not happen.
The reason I asked was the table can take up more space than there is available in the cache. If for most (or a lot) of queries it would be possible to access only the 'Covering' index, then the burdance on the cache would be less.
Often the usage of this type of index (with a lot of rows) is for situations where there a a lot of different combinations for selection. To create an index for each combination *) would lead to far to many indexes so now a wide index is used. Most searches on the wide index are done with a full index scan. (And then then the lookup(s) in the cluster are done). The wide index is still a lot smaller then the full table, so a full index scan is far more efficient than the full (cluster) table scan.
Ben
*)
(With 6 fields in the index, there are 15 combinations of 2 fields, 20 combinations of 3 fields, 15 combinations of 4 fields. Althogether to much to create an index for each situation. 64 (63) combinations in total).
(For 8 fields the number of combinations is 256 (255)).
October 30, 2015 at 8:37 am
You have this index definition in your first post:
CREATE NONCLUSTERED INDEX IX_Large_Table_ABDFHJ ON dbo.Large_Table
(A,B,D,F,H,J) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Why?
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
October 30, 2015 at 11:11 am
ChrisM@Work (10/30/2015)
You have this index definition in your first post:CREATE NONCLUSTERED INDEX IX_Large_Table_ABDFHJ ON dbo.Large_Table
(A,B,D,F,H,J) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Why?
2 Reasons.
1.
Any combination of the fields A,B,D,F,H,J can be used for a selection.
For example F, H, D in the where clause.
This results in a reasonable fast search with a full index scan over this index.
Then the data is read from the clustered table.
To cover any combination of the A,B,D,F,H,J, 63 indexes would be needed.
(In the real table the larger index is not 6 but 9 fields, 255 combinations are possible).
(Alternative would be a full cluster table scan, this table is at least 10 times the size of the index).
2.
If only a limited number of columns is needed, a covering index might give a performance and a cache advantage over not havign the covering index.
For selections with a limited number of rows returning the difference with the clustered table is smal. Except if in most cases this table is sufficient and the clustered table is only read rarely, then this covering index gives over a factor 10 of advantage for the cache. (The size of data is less then 1/10 of the full table, so the cache needed is also 1/10 if the full index is in cache).
Ben
October 30, 2015 at 12:02 pm
ben.brugman (10/30/2015)
The Question was:Can SQL-server use an index and then use the covering index to collect the data?
(And therefore not using the 'main' table).
The answer is No.
What you'll see will either be one of:
- a seek of the noncovering index and key lookups to the clustered index
- a scan of the covering index (not the clustered index, because it's bigger then the covering index and hence less efficient
Lookups are ONLY done to either the clustered index or the heap, you will never see lookups done to a nonclustered index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2015 at 12:06 pm
ben.brugman (10/30/2015)
Any combination of the fields A,B,D,F,H,J can be used for a selection.For example F, H, D in the where clause.
This results in a reasonable fast search with a full index scan over this index.
Then the data is read from the clustered table.
Unlikely. It's fairly rare for SQL to scan a non-covering index and then do lookups, because it's hard for it to get accurate estimations. It's prone to scan the cluster in those cases
To cover any combination of the A,B,D,F,H,J, 63 indexes would be needed.
(In the real table the larger index is not 6 but 9 fields, 255 combinations are possible).
To do every combination, yes, but that's usually not necessary. In most systems some combinations of columns are more common than others. What usually works well is to tailor indexes for the more common cases, then have a 'fall-back' index for queries that are using less common sets of columns.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2015 at 12:12 pm
GilaMonster (10/30/2015)
ben.brugman (10/30/2015)
The Question was:Can SQL-server use an index and then use the covering index to collect the data?
(And therefore not using the 'main' table).
The answer is No.
What you'll see will either be one of:
- a seek of the noncovering index and key lookups to the clustered index
- a scan of the covering index (not the clustered index, because it's bigger then the covering index and hence less efficient
Lookups are ONLY done to either the clustered index or the heap, you will never see lookups done to a nonclustered index.
My original answer was related to whether a key lookup would always be done. As included with my answer, it depends on whether the nonclustered index is truly covering or not. If the index covers all needed columns, no lookup is done because it is not needed.
My understanding is also that if the table is a heap, SQL can "intersect" multiple nonclustered indexes to generate the desired result, without having to do a lookup back to the heap. It may not do seeks (keyed lookups) on other nonclustered indexes, but it can at least scan them and join the results.
Presumably SQL could also intersect nonclus indexes even if the table had a clus index, but I haven't seen that, maybe because SQL so strongly prefers using a clus index.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 30, 2015 at 12:13 pm
Again, column "F" seems to figure in every one of your searches. If that's true, cluster the table on F first. You might be able to eliminate some of the covering indexes and improve overall performance.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply