May 16, 2011 at 2:26 am
Hi,
I have a problem and I don't know what else to look.
We have these sql functions (scalar and table-valued) created by developers, these functions are called by various applications created by them made in VB and .net as well as websites.
These functions seem to hang or stop working and application users report timeout. Killing these processes will not help I have to restart sql service.
Do you have any advise? I can send a screenshot as sample.
Thank you,
Zaizai
May 16, 2011 at 3:07 am
It could be anything, we need much more detail to advise sensibly.
First of all I would check for locks and find the locking leader, then I would try to inspect the query plan of the "bad" statement and see if something changed (e.g. are the statistics up to date?).
What do you mean that killing the offending SPIDs doesn't help? They don't get killed? Other sessions fire the same statements as soon as you kill one?
-- Gianluca Sartori
May 16, 2011 at 7:29 am
Functions are a notorious TSQL bottleneck. Most of the time they are used incorrectly or written badly and the combination causes poor performance. This is especially true if you're working with multi-statement table valued functions. The key is that they don't have statistics, so they're treated as if they have one row. As long as the number of rows in them is low, say less than 100, then performance should be fine. But when you get more rows performance tanks.
It requires a fundamental rewrite and redesign of your processes.
Based on the information at hand, do I know that you're there? Nope. We'd need to see a lot more information, but the likelihood is high.
"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
May 16, 2011 at 7:14 pm
Thanks.
I will check back on this
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply