April 10, 2015 at 9:23 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?
If I can pack more data in a page, that means more data in an extent (which is what SQL Server reads from disk) that means less IO to get the same data.
To look at it another way, if I only need 200 bytes of data from each row of data that may consist of several thousand bytes of data in the main table, and I am returning a large data set SQL Server may decide that scanning the table is better than using key lookups if the index that could help doesn't have all the data needed to satisfy the query.
April 10, 2015 at 10:33 am
RonKyle (4/10/2015)
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.
Like any other useful and good tool, it can be used for destructive and stupid purposes.
But, think about it, what if you're in situation where, if you just had two clustered indexes on a table, you'd be set. INCLUDE all the columns, and it's exactly the same as a second clustered index. Done.
"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 10:34 am
Lynn Pettis (4/10/2015)
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?
If I can pack more data in a page, that means more data in an extent (which is what SQL Server reads from disk) that means less IO to get the same data.
To look at it another way, if I only need 200 bytes of data from each row of data that may consist of several thousand bytes of data in the main table, and I am returning a large data set SQL Server may decide that scanning the table is better than using key lookups if the index that could help doesn't have all the data needed to satisfy the query.
And also, depending on the indexes and the columns we're talking about, adding stuff at the leaf level may be smaller than adding it to the key, which will be stored at every level plus the leaf. Size alone isn't a reason to put one over the other necessarily.
"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 10:53 am
Grant Fritchey (4/10/2015)
Lynn Pettis (4/10/2015)
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?
If I can pack more data in a page, that means more data in an extent (which is what SQL Server reads from disk) that means less IO to get the same data.
To look at it another way, if I only need 200 bytes of data from each row of data that may consist of several thousand bytes of data in the main table, and I am returning a large data set SQL Server may decide that scanning the table is better than using key lookups if the index that could help doesn't have all the data needed to satisfy the query.
And also, depending on the indexes and the columns we're talking about, adding stuff at the leaf level may be smaller than adding it to the key, which will be stored at every level plus the leaf. Size alone isn't a reason to put one over the other necessarily.
If I am creating a covering index and the data does not help the index itself it becomes an included column. I don't like putting more in the index than needed since INCLUDED columns were added to SQL Server.
April 10, 2015 at 11:23 am
Lynn Pettis (4/10/2015)
Grant Fritchey (4/10/2015)
Lynn Pettis (4/10/2015)
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?
If I can pack more data in a page, that means more data in an extent (which is what SQL Server reads from disk) that means less IO to get the same data.
To look at it another way, if I only need 200 bytes of data from each row of data that may consist of several thousand bytes of data in the main table, and I am returning a large data set SQL Server may decide that scanning the table is better than using key lookups if the index that could help doesn't have all the data needed to satisfy the query.
And also, depending on the indexes and the columns we're talking about, adding stuff at the leaf level may be smaller than adding it to the key, which will be stored at every level plus the leaf. Size alone isn't a reason to put one over the other necessarily.
If I am creating a covering index and the data does not help the index itself it becomes an included column. I don't like putting more in the index than needed since INCLUDED columns were added to SQL Server.
Yep. Total agreement. Unless I know I need it in the key, it's going to the INCLUDE.
"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
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply