small table + many DELETES + UPDATE = deadlocks

Question

small table + many DELETES + UPDATE = deadlocks

Viewing 10 posts - 16 through 24 (of 24 total)

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

Brandie Tarvin SSC Guru Points: 173053 More actions · Answer 1

mberry 51447 (6/1/2010)
Nope it is SQL_Latin1_General_CP1_CI_AS

If the database is case insensitive, do you know why the vendor put an UPPER() function in the WHERE clause?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

mberry 51447 SSC-Addicted Points: 407 More actions · Answer 2

Brandie Tarvin (6/1/2010)
mberry 51447 (6/1/2010)
Nope it is SQL_Latin1_General_CP1_CI_AS
If the database is case insensitive, do you know why the vendor put an UPPER() function in the WHERE clause?

No clue. I didnt write it. But somehow I dont believe that use of the UPPER function is causing this.

Brandie Tarvin SSC Guru Points: 173053 More actions · Answer 3

mberry 51447 (6/1/2010)
somehow I dont believe that use of the UPPER function is causing this.

Every function included in a WHERE clause causes problems. It most instances, it can turn a set based query into a RBAR query. The more functions you have in a query, the more performance problems you have. So, removing every unnecessary function can only help you out.

That's the reason why Gail said:

Not much I can do about that update. Those functions in the where clause are going to kill it

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

So all the UPPER is doing is preventing index usage. Wonderful. No possibility of getting the query changed?

Ok, this is what I suggest for a first try:

Drop that unique nonclustered index and replace it with a primary key clustered

Drop all of the nonclustered indexes (this is assuming that the queries you've told me of are the only ones running against that table). There are far too many for a fast insert/update/delete table and they're all too narrow to be useful

Create a nonclustered index (FYI_DKEY, FYI_VER_MAJOR, FYI_VER_MINOR,FYI_SERVICE_CUSTOM)

Create a nonclustered index (FYI_SERVICE_TYPE, FYI_SERVICE_OWNER, FYI_PROCESS_TIME, FYI_SERVICE_SUBTYP)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

mberry 51447 (6/1/2010)
But somehow I dont believe that use of the UPPER function is causing this.

Maybe, maybe not. But it's not helping the situation at all. There's no way to get that update to fully use an index, because of the UPPER and the CONVERT.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Brandie Tarvin (6/1/2010)
It most instances, it can turn a set based query into a RBAR query

You're thinking of UDFs which behave as cursors in disguise. All a built-in function will do is turn an index seek into an index scan, with the associated locking impact.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mberry 51447 SSC-Addicted Points: 407 More actions · Answer 7

Ok. I have added the indexes. but the first one is a bit wide. I got the following:

Warning! The maximum key length is 900 bytes. The index 'IX_TEMP1' has maximum length of 2012 bytes. For some combination of large values, the insert/update operation will fail.

mberry 51447 SSC-Addicted Points: 407 More actions · Answer 8

Also keep in mind the main deadlocks I see are only invloving the deletes. not the update even though the update runs frequently it is not a node of the chain 90% of the time

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

mberry 51447 (6/1/2010)
Ok. I have added the indexes. but the first one is a bit wide. I got the following:
Warning! The maximum key length is 900 bytes. The index 'IX_TEMP1' has maximum length of 2012 bytes. For some combination of large values, the insert/update operation will fail.

Not good.

Ok, change that one to this:

(FYI_DKEY, FYI_VER_MAJOR, FYI_VER_MINOR) INCLUDE (FYI_SERVICE_CUSTOM)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mberry 51447 SSC-Addicted Points: 407 More actions · Answer 10

OK. Thanks for your help. I should see deadlocks tonight if it doesnt work. I will let you know. Thanks so much for your help!