September 28, 2011 at 2:20 pm
Hi,
I've a partitioned table by UID, with a clustered index on UID. A third party tool shows that the following update takes the longest time to complete and potentially blocks other processes. I've 2 questions:
1. Since the table is partitioned and we use snapshot isolation, should there be any blocking if multiple calls are made to update table1 on different partitions?
2. I’ve created a composite index on file_id and UID but did not specify any include columns. The columns participating in the update, are fairly large, first_name, last_name, address1, etc. are varchar(4000). Would I get any peroformance improvmens by adding include to the index for is_numeric, is_even, is_general? Since not all the columns are going to be in the INCLUDE, then the table would still need to be accessed.
UPDATE li
SET first_name =
CASE
WHEN ls.has_first_name = 1 THEN ls.first_name
ELSE li.first_name
END,
last_name =
CASE
WHEN ls.has_last_name = 1 THEN ls.last_name
ELSE li.last_name
END,
address_1 =
CASE
WHEN ls.has_address_1 = 1 THEN ls.address_1
ELSE li.address_1
END,
address_2 =
CASE
WHEN ls.address_2 is not null THEN ls.address_2
ELSE li.address_2
END,
city =
CASE
WHEN ls.has_city = 1 THEN ls.city
ELSE li.city
END,
state =
CASE
WHEN ls.has_state = 1 THEN ls.state
ELSE li.state
END,
zip_code =
CASE
WHEN ls.has_zip_code = 1 THEN ls.zip_code
ELSE li.zip_code
END,
country =
CASE
WHEN ls.has_country = 1 THEN ls.country
ELSE li.country
END,
is_numeric = ls.is_numeric,
is_even = ls.is_even,
is_general = ls.is_general
FROM @temp_tbl ls
INNER JOIN table1 li
ON ls.file_id = li.file_id
WHERE li.uid = @uid
Thanks
September 28, 2011 at 2:46 pm
Assuming updates to different partitions, no you shouldn't see blocking while those updates are occurring. You can still block within a partition though.
As to your second question, no, 1/2 a covering index is a non-covering index. It's like being pregnant. It's either a covering index or it's not.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 28, 2011 at 3:22 pm
Grant Fritchey (9/28/2011)
Assuming updates to different partitions, no you shouldn't see blocking while those updates are occurring. You can still block within a partition though.
Though if both cause lock escalation, they'll block each other. By default, lock escalation is to table.
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
September 30, 2011 at 12:55 pm
GilaMonster, what generally causes lock escalation?
September 30, 2011 at 1:01 pm
aibogomolov (9/30/2011)
GilaMonster, what generally causes lock escalation?
5000+ rows.
See this: http://msdn.microsoft.com/en-us/library/ms184286.aspx
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply