April 10, 2015 at 7:50 am
debating this with a colleague ... which would you expect to be more efficient ? (all fields int)
CREATE NONCLUSTERED INDEX [ICX_PERSON] ON [dbo].[PERSON]
(
[PERSON_ID] ASC
) include (ROLE_ID)
GO
or
CREATE NONCLUSTERED INDEX [ICX_PERSON] ON [dbo].[PERSON]
(
[PERSON_ID] ,ROLE_ID
)
GO
Or would they behave the same ?
April 10, 2015 at 7:57 am
Depends on the queries being run against that table, impossible to answer without knowing the queries.
This may give some insight into indexes and queries
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
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
April 10, 2015 at 8:00 am
so the only way to validate either of them would be no harm testing etc rather than if the majority of queries use both fields , but some use either /or, making an assumption based on that useage pattern
April 10, 2015 at 8:08 am
Neither index is particularly useful for a query that filters only on RoleID, see the blog post I posted.
Again, without looking at the queries that run against a table, absolutely nothing can be said about what indexes are best. Index design is driven by queries, by what queries filter on, join on and select, and you're mostly going to be looking at queries which run frequently, not once a month.
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
April 10, 2015 at 8:14 am
As a general rule, isn't the first one more suited to
SELECT ROLE_ID
WHERE PERSON_ID = ?
while the second is more suited to
SELECT *
WHERE PERSON_ID = ? AND ROLE_ID = ?
In the first case, you need the role ID, so you add it in the include so that a query using that index doesn't then need to navigate to the leaf page.
April 10, 2015 at 8:17 am
RonKyle (4/10/2015)
As a general rule, isn't the first one more suited toSELECT ROLE_ID
WHERE PERSON_ID = ?
while the second is more suited to
SELECT *
WHERE PERSON_ID = ? AND ROLE_ID = ?
Yup, though the first can use the second index with no (or likely unmeasurable) reduction in performance and the second can use the first index with a reduction in performance that will range between none and minor depending on the data distribution of the Person_ID column.
If, as the table name suggests, Person_ID is unique, then either query will use either index almost identically. If it's not, things get more complicated
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
April 10, 2015 at 8:24 am
Interesting. I haven't personally made much use of include indexes since they do make the indexes bigger and I'm not convinced the performance gains are significant. I know others who simply create them out of hand, but I remain unconvinced that this is a good idea. Have you used them a lot?
April 10, 2015 at 8:29 am
Yes person_id is unique , role_id is not
April 10, 2015 at 8:48 am
RonKyle (4/10/2015)
Interesting. I haven't personally made much use of include indexes since they do make the indexes bigger and I'm not convinced the performance gains are significant. I know others who simply create them out of hand, but I remain unconvinced that this is a good idea. Have you used them a lot?
I use them fairly frequently. They do make the index bigger, but they create a covering index without modifying the key structure. It's as Gail says, it's all down to needs of the queries you're running.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 10, 2015 at 8:52 am
Grant Fritchey (4/10/2015)
RonKyle (4/10/2015)
Interesting. I haven't personally made much use of include indexes since they do make the indexes bigger and I'm not convinced the performance gains are significant. I know others who simply create them out of hand, but I remain unconvinced that this is a good idea. Have you used them a lot?I use them fairly frequently. They do make the index bigger, but they create a covering index without modifying the key structure. It's as Gail says, it's all down to needs of the queries you're running.
This. Exactly this.
If I can add two include columns to an index and get a query that runs 10 times a second down from 400ms to 10ms, you bet I'm going to do that.
Now, including every single column in the table to tune a query that runs once a day? Probably not so much.
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
April 10, 2015 at 8:53 am
simon_s (4/10/2015)
Yes person_id is unique , role_id is not
In that case they're pretty much going to behave much the same way for any query that filters on person_id. Use the one with include and make it a unique index. Or, if the clustered index is enforcing the pk, rather don't use either as they're redundant with a clustered pk on person_id.
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
April 10, 2015 at 9:05 am
If I can add two include columns to an index and get a query that runs 10 times a second down from 400ms to 10ms, you bet I'm going to do that.
Now, including every single column in the table to tune a query that runs once a day?
The situations I see are much closer to the second case than the first.
April 10, 2015 at 9:07 am
Grant Fritchey (4/10/2015)
RonKyle (4/10/2015)
Interesting. I haven't personally made much use of include indexes since they do make the indexes bigger and I'm not convinced the performance gains are significant. I know others who simply create them out of hand, but I remain unconvinced that this is a good idea. Have you used them a lot?I use them fairly frequently. They do make the index bigger, but they create a covering index without modifying the key structure. It's as Gail says, it's all down to needs of the queries you're running.
I use them as well. The indexes may be bigger, but by avoiding key lookups, or worse table/index scans, on the main table that is even wider (say 1 to 5 rows of data per page depending on data stored) it improves the performance of key procedures/queries.
April 10, 2015 at 9:10 am
but by avoiding key lookups, or worse table/index scans
assuming the table is not a heap (which it shouldn't be, right?), why would it (at least as a general rule) not then use the pointers that point to the clustered key?
April 10, 2015 at 9:17 am
RonKyle (4/10/2015)
but by avoiding key lookups, or worse table/index scans
assuming the table is not a heap (which it shouldn't be, right?), why would it (at least as a general rule) not then use the pointers that point to the clustered key?
It would use the pointers to the clustered index. That's what a key lookup is.
If the table's a heap, you get rid lookups instead
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply