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.
June 14, 2022 at 9:33 pm
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) "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".
June 15, 2022 at 1:56 pm
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.
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>'
June 15, 2022 at 2:21 pm
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>'
"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
(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) "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".
June 15, 2022 at 4:35 pm
Thank you both.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply