Mixing
SELECT is using a non-PK Index instead of the PK
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
0
down vote
favorite
In the [dbo].[Programs] table, the column [Id] is the PrimaryKey (not par of a composite key). There are also quite a few other indexes on that table.
When I'm running this simple query, SELECT [Id] FROM [dbo].[Programs], here is the execution plan:
My question is: Why is it not just using the PK index instead?
Performance is not an issue as that table has 23 rows, but I just find it odd, and I want to understand why SqlServer is right, and why I'm wrong to assumed it would be better.
sql-server-2012 index execution-plan
asked 37 mins ago
Tipx
1031
New contributor
Tipx is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
0
down vote
favorite
In the [dbo].[Programs] table, the column [Id] is the PrimaryKey (not par of a composite key). There are also quite a few other indexes on that table.
When I'm running this simple query, SELECT [Id] FROM [dbo].[Programs], here is the execution plan:
My question is: Why is it not just using the PK index instead?
Performance is not an issue as that table has 23 rows, but I just find it odd, and I want to understand why SqlServer is right, and why I'm wrong to assumed it would be better.
sql-server-2012 index execution-plan
asked 37 mins ago
Tipx
1031
New contributor
Tipx is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
0
down vote
favorite
up vote
0
down vote
favorite
In the [dbo].[Programs] table, the column [Id] is the PrimaryKey (not par of a composite key). There are also quite a few other indexes on that table.
When I'm running this simple query, SELECT [Id] FROM [dbo].[Programs], here is the execution plan:
My question is: Why is it not just using the PK index instead?
Performance is not an issue as that table has 23 rows, but I just find it odd, and I want to understand why SqlServer is right, and why I'm wrong to assumed it would be better.
sql-server-2012 index execution-plan
asked 37 mins ago
Tipx
1031
New contributor
Tipx is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
In the [dbo].[Programs] table, the column [Id] is the PrimaryKey (not par of a composite key). There are also quite a few other indexes on that table.
When I'm running this simple query, SELECT [Id] FROM [dbo].[Programs], here is the execution plan:
My question is: Why is it not just using the PK index instead?
Performance is not an issue as that table has 23 rows, but I just find it odd, and I want to understand why SqlServer is right, and why I'm wrong to assumed it would be better.
sql-server-2012 index execution-plan
sql-server-2012 index execution-plan
asked 37 mins ago
Tipx
1031
New contributor
Tipx is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 37 mins ago
Tipx
1031
New contributor
Tipx is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 37 mins ago
Tipx
1031
New contributor
Tipx is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 37 mins ago
Tipx
1031
asked 37 mins ago
Tipx
1031
1031
New contributor
Tipx is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Tipx is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Tipx is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
2
down vote
accepted
The query optimizer will look to see what's the quickest way (as far as it can tell) to get you your data.
It might be interesting to check the execution plan of the same query, with ORDER BY [Id] added.
I'll assume that Id is not only the primary key, but that its index is the clustered index on the table. What that means is that, to go through the index and pick out the Id values.
I'll also assume that IX_SupplyProgramId has a limited number of other columns included. Note that all indexes will have the value of the clustered index, as that's how they connect the index back to the actual row. And, every value of Id will be included
So, the amount of data that has to be read if it reads the clustered index is probably larger than the amount to read in the other index. Read are one of the most expensive operations the query engine does, so reducing reads is a good thing.
So, it uses the smaller index to get the Id values, instead of the clustered index.
answered 20 mins ago
RDFozz
9,45131029
Oh, so because[SupplyProgramId]is not nullable, and unique, it know it will contain every[Id]strictly once... damn he's wise! I tried using the sort on[Id], and it does indeed use the PK Index at that point. So when it reads the Clustered PK Index, since it contains the data too, it's heavier to read hence why it's not doing it... nice! Thanks sir!
– Tipx
8 mins ago
1
@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
– Aaron Bertrand♦
4 mins ago
@AaronBertrand If it wasn't "Non-Nullable and Unique", it couldn't use the other IX because it wouldn't guarantee that every [Id] would be referenced. Some [Id] could have no references to them, making the result set incomplete. Some [Id]s could be referenced multiple times, adding the need to do a distinct on the result.
– Tipx
17 secs ago
add a comment |Â
up vote
2
down vote
It has nothing to do with primary key, except that the primary key is typically clustered.
In your case SQL Server is not using the clustered index because, by definition, the clustered index includes all of the columns in the table. Since you only want Id, it is using a skinnier index that satisfies your query simply because it's less work to do so, and even if your table only has a single column, it's still going to choose the non-clustered index.
If I ask you to get me a beer from the fridge, your choices are to:
- wheel over the entire fridge
- bring over the case
- bring over a single beer
In your case, 1. is using the clustered index, 2. is using some wide index, and 3. is using an index that only contains Id.
A clustered index is simply not always the best choice for an operation, much like a Ferrari is not the car you always want to use for a task (racing someone vs. towing a yacht, for example).
answered 23 mins ago
Aaron Bertrand♦
145k19276463
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
The query optimizer will look to see what's the quickest way (as far as it can tell) to get you your data.
It might be interesting to check the execution plan of the same query, with ORDER BY [Id] added.
I'll assume that Id is not only the primary key, but that its index is the clustered index on the table. What that means is that, to go through the index and pick out the Id values.
I'll also assume that IX_SupplyProgramId has a limited number of other columns included. Note that all indexes will have the value of the clustered index, as that's how they connect the index back to the actual row. And, every value of Id will be included
So, the amount of data that has to be read if it reads the clustered index is probably larger than the amount to read in the other index. Read are one of the most expensive operations the query engine does, so reducing reads is a good thing.
So, it uses the smaller index to get the Id values, instead of the clustered index.
answered 20 mins ago
RDFozz
9,45131029
Oh, so because[SupplyProgramId]is not nullable, and unique, it know it will contain every[Id]strictly once... damn he's wise! I tried using the sort on[Id], and it does indeed use the PK Index at that point. So when it reads the Clustered PK Index, since it contains the data too, it's heavier to read hence why it's not doing it... nice! Thanks sir!
– Tipx
8 mins ago
1
@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
– Aaron Bertrand♦
4 mins ago
@AaronBertrand If it wasn't "Non-Nullable and Unique", it couldn't use the other IX because it wouldn't guarantee that every [Id] would be referenced. Some [Id] could have no references to them, making the result set incomplete. Some [Id]s could be referenced multiple times, adding the need to do a distinct on the result.
– Tipx
17 secs ago
add a comment |Â
up vote
2
down vote
accepted
The query optimizer will look to see what's the quickest way (as far as it can tell) to get you your data.
It might be interesting to check the execution plan of the same query, with ORDER BY [Id] added.
I'll assume that Id is not only the primary key, but that its index is the clustered index on the table. What that means is that, to go through the index and pick out the Id values.
I'll also assume that IX_SupplyProgramId has a limited number of other columns included. Note that all indexes will have the value of the clustered index, as that's how they connect the index back to the actual row. And, every value of Id will be included
So, the amount of data that has to be read if it reads the clustered index is probably larger than the amount to read in the other index. Read are one of the most expensive operations the query engine does, so reducing reads is a good thing.
So, it uses the smaller index to get the Id values, instead of the clustered index.
answered 20 mins ago
RDFozz
9,45131029
Oh, so because[SupplyProgramId]is not nullable, and unique, it know it will contain every[Id]strictly once... damn he's wise! I tried using the sort on[Id], and it does indeed use the PK Index at that point. So when it reads the Clustered PK Index, since it contains the data too, it's heavier to read hence why it's not doing it... nice! Thanks sir!
– Tipx
8 mins ago
1
@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
– Aaron Bertrand♦
4 mins ago
@AaronBertrand If it wasn't "Non-Nullable and Unique", it couldn't use the other IX because it wouldn't guarantee that every [Id] would be referenced. Some [Id] could have no references to them, making the result set incomplete. Some [Id]s could be referenced multiple times, adding the need to do a distinct on the result.
– Tipx
17 secs ago
add a comment |Â
up vote
2
down vote
accepted
up vote
2
down vote
accepted
The query optimizer will look to see what's the quickest way (as far as it can tell) to get you your data.
It might be interesting to check the execution plan of the same query, with ORDER BY [Id] added.
I'll assume that Id is not only the primary key, but that its index is the clustered index on the table. What that means is that, to go through the index and pick out the Id values.
I'll also assume that IX_SupplyProgramId has a limited number of other columns included. Note that all indexes will have the value of the clustered index, as that's how they connect the index back to the actual row. And, every value of Id will be included
So, the amount of data that has to be read if it reads the clustered index is probably larger than the amount to read in the other index. Read are one of the most expensive operations the query engine does, so reducing reads is a good thing.
So, it uses the smaller index to get the Id values, instead of the clustered index.
answered 20 mins ago
RDFozz
9,45131029
The query optimizer will look to see what's the quickest way (as far as it can tell) to get you your data.
It might be interesting to check the execution plan of the same query, with ORDER BY [Id] added.
I'll assume that Id is not only the primary key, but that its index is the clustered index on the table. What that means is that, to go through the index and pick out the Id values.
I'll also assume that IX_SupplyProgramId has a limited number of other columns included. Note that all indexes will have the value of the clustered index, as that's how they connect the index back to the actual row. And, every value of Id will be included
So, the amount of data that has to be read if it reads the clustered index is probably larger than the amount to read in the other index. Read are one of the most expensive operations the query engine does, so reducing reads is a good thing.
So, it uses the smaller index to get the Id values, instead of the clustered index.
answered 20 mins ago
RDFozz
9,45131029
answered 20 mins ago
RDFozz
9,45131029
answered 20 mins ago
RDFozz
9,45131029
answered 20 mins ago
RDFozz
9,45131029
9,45131029
Oh, so because[SupplyProgramId]is not nullable, and unique, it know it will contain every[Id]strictly once... damn he's wise! I tried using the sort on[Id], and it does indeed use the PK Index at that point. So when it reads the Clustered PK Index, since it contains the data too, it's heavier to read hence why it's not doing it... nice! Thanks sir!
– Tipx
8 mins ago
1
@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
– Aaron Bertrand♦
4 mins ago
@AaronBertrand If it wasn't "Non-Nullable and Unique", it couldn't use the other IX because it wouldn't guarantee that every [Id] would be referenced. Some [Id] could have no references to them, making the result set incomplete. Some [Id]s could be referenced multiple times, adding the need to do a distinct on the result.
– Tipx
17 secs ago
add a comment |Â
Oh, so because[SupplyProgramId]is not nullable, and unique, it know it will contain every[Id]strictly once... damn he's wise! I tried using the sort on[Id], and it does indeed use the PK Index at that point. So when it reads the Clustered PK Index, since it contains the data too, it's heavier to read hence why it's not doing it... nice! Thanks sir!
– Tipx
8 mins ago
1
@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
– Aaron Bertrand♦
4 mins ago
@AaronBertrand If it wasn't "Non-Nullable and Unique", it couldn't use the other IX because it wouldn't guarantee that every [Id] would be referenced. Some [Id] could have no references to them, making the result set incomplete. Some [Id]s could be referenced multiple times, adding the need to do a distinct on the result.
– Tipx
17 secs ago
Oh, so because
[SupplyProgramId] is not nullable, and unique, it know it will contain every [Id]strictly once... damn he's wise! I tried using the sort on [Id], and it does indeed use the PK Index at that point. So when it reads the Clustered PK Index, since it contains the data too, it's heavier to read hence why it's not doing it... nice! Thanks sir!– Tipx
8 mins ago
Oh, so because
[SupplyProgramId] is not nullable, and unique, it know it will contain every [Id]strictly once... damn he's wise! I tried using the sort on [Id], and it does indeed use the PK Index at that point. So when it reads the Clustered PK Index, since it contains the data too, it's heavier to read hence why it's not doing it... nice! Thanks sir!– Tipx
8 mins ago
1
1
@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
– Aaron Bertrand♦
4 mins ago
@Tipx I don't believe that uniqueness has absolutely anything to do with this case.
– Aaron Bertrand♦
4 mins ago
@AaronBertrand If it wasn't "Non-Nullable and Unique", it couldn't use the other IX because it wouldn't guarantee that every [Id] would be referenced. Some [Id] could have no references to them, making the result set incomplete. Some [Id]s could be referenced multiple times, adding the need to do a distinct on the result.
– Tipx
17 secs ago
@AaronBertrand If it wasn't "Non-Nullable and Unique", it couldn't use the other IX because it wouldn't guarantee that every [Id] would be referenced. Some [Id] could have no references to them, making the result set incomplete. Some [Id]s could be referenced multiple times, adding the need to do a distinct on the result.
– Tipx
17 secs ago
add a comment |Â
up vote
2
down vote
It has nothing to do with primary key, except that the primary key is typically clustered.
In your case SQL Server is not using the clustered index because, by definition, the clustered index includes all of the columns in the table. Since you only want Id, it is using a skinnier index that satisfies your query simply because it's less work to do so, and even if your table only has a single column, it's still going to choose the non-clustered index.
If I ask you to get me a beer from the fridge, your choices are to:
- wheel over the entire fridge
- bring over the case
- bring over a single beer
In your case, 1. is using the clustered index, 2. is using some wide index, and 3. is using an index that only contains Id.
A clustered index is simply not always the best choice for an operation, much like a Ferrari is not the car you always want to use for a task (racing someone vs. towing a yacht, for example).
answered 23 mins ago
Aaron Bertrand♦
145k19276463
add a comment |Â
up vote
2
down vote
It has nothing to do with primary key, except that the primary key is typically clustered.
In your case SQL Server is not using the clustered index because, by definition, the clustered index includes all of the columns in the table. Since you only want Id, it is using a skinnier index that satisfies your query simply because it's less work to do so, and even if your table only has a single column, it's still going to choose the non-clustered index.
If I ask you to get me a beer from the fridge, your choices are to:
- wheel over the entire fridge
- bring over the case
- bring over a single beer
In your case, 1. is using the clustered index, 2. is using some wide index, and 3. is using an index that only contains Id.
A clustered index is simply not always the best choice for an operation, much like a Ferrari is not the car you always want to use for a task (racing someone vs. towing a yacht, for example).
answered 23 mins ago
Aaron Bertrand♦
145k19276463
add a comment |Â
up vote
2
down vote
up vote
2
down vote
It has nothing to do with primary key, except that the primary key is typically clustered.
In your case SQL Server is not using the clustered index because, by definition, the clustered index includes all of the columns in the table. Since you only want Id, it is using a skinnier index that satisfies your query simply because it's less work to do so, and even if your table only has a single column, it's still going to choose the non-clustered index.
If I ask you to get me a beer from the fridge, your choices are to:
- wheel over the entire fridge
- bring over the case
- bring over a single beer
In your case, 1. is using the clustered index, 2. is using some wide index, and 3. is using an index that only contains Id.
A clustered index is simply not always the best choice for an operation, much like a Ferrari is not the car you always want to use for a task (racing someone vs. towing a yacht, for example).
answered 23 mins ago
Aaron Bertrand♦
145k19276463
It has nothing to do with primary key, except that the primary key is typically clustered.
In your case SQL Server is not using the clustered index because, by definition, the clustered index includes all of the columns in the table. Since you only want Id, it is using a skinnier index that satisfies your query simply because it's less work to do so, and even if your table only has a single column, it's still going to choose the non-clustered index.
If I ask you to get me a beer from the fridge, your choices are to:
- wheel over the entire fridge
- bring over the case
- bring over a single beer
In your case, 1. is using the clustered index, 2. is using some wide index, and 3. is using an index that only contains Id.
A clustered index is simply not always the best choice for an operation, much like a Ferrari is not the car you always want to use for a task (racing someone vs. towing a yacht, for example).
answered 23 mins ago
Aaron Bertrand♦
145k19276463
answered 23 mins ago
Aaron Bertrand♦
145k19276463
answered 23 mins ago
Aaron Bertrand♦
145k19276463
answered 23 mins ago
Aaron Bertrand♦
145k19276463
145k19276463
add a comment |Â
add a comment |Â
Tipx is a new contributor. Be nice, and check out our Code of Conduct.
Tipx is a new contributor. Be nice, and check out our Code of Conduct.
Tipx is a new contributor. Be nice, and check out our Code of Conduct.
Tipx is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f217653%2fselect-is-using-a-non-pk-index-instead-of-the-pk%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
