May 10, 2011 at 10:32 am
It appears that scalar functions compile at runtime and require a exclusive lock. We have stored procedures using common UDF's to calculate user balances. When there is a lot of users on the site these procedures / UDF's are executed concurrently causing waits due to locks. Is there a way to avoid this, or a better solution then using UDF's?
May 10, 2011 at 10:36 am
You'll end up with compile locks for a number of reasons. The most common is if you don't use two-part names for objects being accessed by the UDFs. Tables need to be "dbo.MyTableName" instead of just "MyTableName", otherwise it has to issue a compile-lock while it sorts out access/security rules and sees if there's more than one table with that name.
UDFs can be a performance problem all by themselves, and can often be eliminated in favor of inline queries and such. Can you post the code, so we can take a look at possible improvements?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 10, 2011 at 11:49 am
The table names are fully qualified. The issues I'm facing is the function's have bad code (cursors, more nested function calls, etc...) that make them both poorly performing and also difficult to refactor. I'm faced with the task of doing something to either removing the locks, or if that cannot be done refactoring this proc to not use the nested function calls. I'm trying to figure out if there is anyway to remove the prevent the locks before diving in to refactor. I know performance tuning each query will help, but if the locks still occur then I don't think we'll get the improvements we need.
Is there a way to prevent the locks at run time? Does this occur on table value UDF's too?
May 10, 2011 at 11:58 am
Yes, you can prevent locks in the UDFs by making sure all of your connections are set to Transaction Isolation Level Read Uncommitted.
That will remove all of the select locks. It can also cause huge problems with dirty reads, incorrect data being returned, uncompletable scans that can (if they escalate the wrong way on a pooled connection) cause database corruption issues, and a host of other issues.
You can get partway to unlocked data by using Snapshot Isolation, if you're on Enterprise Edition. That doesn't have the Read Uncommitted issues, but does require more disk space (hard to say how much, since it depends on how transactional your database is), and it can slow down insert/update/delete operations a bit.
Refactoring the code is certainly the best way to solve it. It's probably the most work, but it's also the most certain to give the desired results without negative side-effects.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 10, 2011 at 12:00 pm
Thanks. I think that is probably the way we'll go.
May 11, 2011 at 6:31 am
Cool biz.
If you can post any of the code here, we can probably help with the refactoring and performance issues.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 12, 2011 at 9:41 am
Scalar UDFs are the single worst performance problem I have dealt with over the last 5 years as a perf tuning consultant. I advise my clients to NEVER EVER use them. I am actually writing a chapter for the second SQL Server MVP Deep Dives book entitled "Death by UDF".
If you want to improve your system's performance and concurrency you simply MUST refactor those UDFs out of existence. It can be difficult to do, but is almost always possible. I have achieved 5 and even 6 ORDERS OF MAGNITUDE performance improvements from doing this. Get professional help if you need it, but get rid of the UDFs!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply