Index with included columns

Question

Index with included columns

bharathsivam

SSC Enthusiast

Points: 158
More actions
June 14, 2022 at 9:16 pm
Go to Answer
#4049512

Hello all,
I have a table named [dbo.Transaction] with multiple columns.
My questions are around two columns ([TransactionId] and [TransactionGuid]) that exists on that table.
Column 1 [TransactionId] integer type and it is the primary key on the table so this has Primary key index
Column 2 [TransactionGuid] Guid type. Although unique this defined as a Non key column but has a non clustered index
Most of the queries against this table is using [TransactionGuid] column and then uses TransactionId. I see many deadlocks against this table [dbo.Transaction]
If I modify the non key index [TransactionGuid] to include [TransactionId] as an included column on the non-clustered index.
Would the reads be faster?
Would it prevent a table scan and use just the index instead?
I'm lookin for performance improvement opportunities here.
ScottPletcher

SSC Guru

Points: 100947
More actions
June 15, 2022 at 2:51 pm
Answer
#4049942

(1) If the query is fetching only columns that are part of the index, SQL will not need to access the main table at all and will just use the index. SQL may still need to scan the index, depending on the WHERE clauses, etc., in the query.
For your specific example, not all columns are in the index, so SQL will use the index to get the clustering key(s) and then do a single-read keyed lookup back to the main table. For a single row, or just a few rows, that is a generally efficient process, and it's much better than having to put all the extra columns (FileId, FileName, FileSize) into the index.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100947 More actions · Answer 1

If I modify the non key index [TransactionGuid] to include [TransactionId] as an included column on the non-clustered index.

No. Column TransactionId is automatically included in every nonclustered index (including the one for TransactionGuid) because it is part of the clustering key.

Your existing indexes are correctly set up. The guid index will nearly always need to do a lookup back to the clus index, but that's fine.

As to general performance improvements, here are some tips:

(1) Review missing index and index usage stats to determine how many table scans are being done and why. Build new indexes if you genuinely need them. Often there is a another choice for a clustered index that works better than id (seriously; it's a huge mistake to automatically use identity as the clus key for a table, choose the clus key very carefully).

(2) review data compression and see if it is worthwhile for your table (for larger tables, it nearly always is). Use proc sys.sp_estimate_data_compression_savings to check on that.

(3) If the fillfactor is set to 100, rebuild the index to lower it to 98 or 99, to insure that minor changes don't cause page splits (technically it's more complicated than that but, for a broad, general rule, that will do).

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

bharathsivam SSC Enthusiast Points: 158 More actions · Answer 2

Thank you for your response. I will research on your three suggestions.

Given that the primary key is included in the non-clustered index I have the following questions.

If a query is fetching only columns that is already part of the index, would the following query does a table scan on the transaction table or it will just use column values from the index?
If there is an open transaction pending to be committed on the dbo.transaction table would the query wait to complete until transaction is committed or would it just read the values of the index?

Select 
f.Fileid,
f.FileName,
f.Filesize,
t.Transactionid,
t.Transactionguidid
from
dbo.transaction t
inner join file f on f.transactionid = t.transactionid
where 
t.transactionguid = '<guid>'

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

bharathsivam wrote:

Thank you for your response. I will research on your three suggestions.
Given that the primary key is included in the non-clustered index I have the following questions.

Just to clarify this, because it very much matters. The primary key is not included. The clustered key is included. It just so happens in this case, that the clustered key and the primary key are the same. They will not always be that way. Best to know, for certain, how this works.

If a query is fetching only columns that is already part of the index, would the following query does a table scan on the transaction table or it will just use column values from the index?
If there is an open transaction pending to be committed on the dbo.transaction table would the query wait to complete until transaction is committed or would it just read the values of the index?
Select 
f.Fileid,
f.FileName,
f.Filesize,
t.Transactionid,
t.Transactionguidid
from
dbo.transaction t
inner join file f on f.transactionid = t.transactionid
where 
t.transactionguid = '<guid>'

From the indexes you've shown, assuming good data distribution, no, you shouldn't see a table scan. You will see a key lookup because the columns listed are not a part of the index you mentioned (at least the way you've described it).
This depends on your isolation level. Assuming Read Committed, or greater, initially, a U (Update) lock is taken during the update process. While the update is finding the rows, before it converts to an X (Exclusive) lock, yes, other processes can read the data. However, while the X lock is in place, the transaction is being committed, no one else can read from the resources locked. Unless, you're using Read Committed Snapshot. Then there'll be a row version in tempdb that other processes can read during the X lock. If you're at Read Uncommitted, no shared locks are taken out, so reads are possible at all points, including during the commit, resulting in dirty reads (which is not just getting 'dog' instead of 'cat', but also includes the possibility of duplicate or missing data, depending on the query).

"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

bharathsivam SSC Enthusiast Points: 158 More actions · Answer 4

bharathsivam

SSC Enthusiast

Points: 158

June 15, 2022 at 4:35 pm

#4049980

Thank you both.