Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2011-12-02 : 05:12:55
|
| I have a function, MyFunction, that takes a parameter and uses that to do a lookup with several joins. Indexes and query plans seem to be OK.It is doing a recompile (not sure why), and the recompile lock prevents access to that function until it has finished, and under heavy load this causes significant lock waits.How can I trace when the recompiles happen? (I'm assuming that from there I will be able to try to work out WHY!)The Function is only ever called from Sprocs (no dynamic SQL).I ran Profiler during busy period yesterday looking for sp_recompile, but that hasn't helped much, if at all. I got 45 occurrences of about 10 differently named Sprocs (in the OBJECTNAME column), and everything else was either "encrypted" (presumably "parts of Sprocs" being recompiled? or some snippets of SQL (presumably the same, but happens to be from Sprocs that are not currently encrypted)Thanks |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-14 : 14:27:35
|
Gail: Been meaning to get back to you on this. Your article:http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/is really the definition of my problem. MyFunction is probably causing all sorts of locking & blocking issues, but I'm just not seeing them, or getting a feel for them, using IO STATISTICS or QUERY PLAN. The whole problem is "submarined" and very hard to monitor and optimise. The only way we have found to discover we have a problem is when the site gets busy, the LOCKs skyrocket, and users start timing out  Until I read your article I had half-a-notion that I could use SQL Profiler to capture the actual query plans, and indeed we did record that data when the site was really busy, but I haven't had time to look at them as yet. We've been spending the time trying to code the MyFunction out of the queries altogether.Is there any notion that Denali (or even SQL 2K8 R2, which we don't yet have installed) will be able to include info. about such functions in STATICS and Query plan? If not seems like a huge accident-waiting-to-happen to poor DEVs like me! It strikes me that it would be much better to be aware of the problem when using STATISTICS IO and/or QUERY PLAN so that we could discover the problem in the normal way that we optimise our code. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-15 : 03:54:01
|
| I haven't looked at this in 2012, but I haven't heard of any improvements.Personally I steer very clear of UDFs (with the sole exception of inline table-values UDFs) as their performance characteristics are just so poor.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-15 : 17:12:17
|
quote:
Originally posted by GilaMonster
.... as their performance characteristics are just so poor.
As I have found out ... although if I could diagnose them more easily I might be able to improve on that (or to have realised I should steer clear of them much earlier!) |
|
|
|
Sachin.Nand
2937 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-18 : 23:27:59
|
I remember that topic now, thanks  I don't think its quite the same, my function just does a SELECT, but it uses several tables joined in quite a complex way and, if a parameter is not provided (i.e. NULL) it does an additional lookup to get the value (from a Session Data table). We have, now, made sure in 99% of cases that the parameter is never provided as NULL as the need for the additional lookup seemed to frequently cause a recompilation.This function seems to trigger a recompilation which causes a block on other processes using(?) or needing to recompile(?) that function. I don't really understand why as the way the function uses the parameters is (I would have thought) going to be constant and not find a particular parameter value that the statistics will suggest needs a different query plan. The underlying data does not change, so the indexes / statistics will be fine.I just need to get rid of the thing from my code; problem is that we use it everywhere, and hadn't realised the possible side effects because it was not obvious when we originally checked Query Plan / I/O Statistics etc. ... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-19 : 05:18:40
|
| It's possibly not just the recompile. If you're using that as a scalar function in the SELECT clause of a query, that function will be getting run once for each and every row of the resultset.Did you go through that blog post on recompiles? If so, what's the reason for the recompiles?--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-19 : 13:17:12
|
| Most (i.e. almost all) of the time its a single row select.(I'm away from the office at the moment, I'll dig out the details when I'm back.) |
|
|
|
|
|
|
|
|
How to Detect Recompile of a Function