SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


functions


functions

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231676 Visits: 46354
Thomas Martin (9/12/2008)

What would cause this kind of behavior?


Has anything changed? Any index creates/drops? Any database settings changed?

It could be data volumes. The amount of data's increased and now the optimiser's picked a different plan. It could be index fragmentation. It could be inaccurate statistics

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


Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150033 Visits: 19449
Have you rebooted lately? I've seen plans just go out the window some times. Have you checked a plan on the development system?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Brookstone
Brookstone
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 211
Thanks again for all the help.

Yes, we rebooted the server, rebuilt all of the indexes, and resampled the statistics. When we compared the execution plans between systems, they were identical. The data size is approximately the same, 3 million rows (since 2005). Now we are looking at configuration options.

When we remove all the functions the process runs is seconds, then as soon as we add just one back, we have extended run time.



Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26248 Visits: 12487
What continues to be a mystery is why a procedure that has been running since 2005 for 20 minutes would increase to 11+ hours. (It appears to me that the optimizer is forcing a cursor operation.)


Enter "tipping point" in the search box up there and read the articles and posts it will return you.
It should clear your mystery.

Once again - data accessing function in SELECT list is a cursor.
Before some stage there was probably enough memory to hold all results of the functions in memory and reuse them for following rows.

Now you have either more sets of parameters the before or less memory because of more users, other applications launched on the same server, etc.
Anyway, there is no enough memory for all data accessing functions results, and server has to run all "hidden cursors" you put into the query.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220968 Visits: 42002
To clarify what Sergiy just said... all those functions that do "lookups" are just an insideous way of doing correlated subqueries which are RBAR on sterioids. RBAR can appear to work fairly well if it all fits in memory cuz memory is fast. If it doesn't, you get the symptoms you just mentioned... everything was fine and then, one day, BOOM! Same goes when something works well on dev and then blows up in prod... just like Sergiy said, chances are you have less usable memory available on the prod machine because you have more users or processes which means it'll "tip" well before the dev box does.

Also as Sergiy stated... the best thing to do would be to resolve the lookups in the form of a join. If you're real careful, you can do that in a view and then join to the view.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30749 Visits: 8986
Oh yes, functions can be like troyan horses,....
they may serve you very well, even for a long time, until your system reaches the condition for them to burst open ....

That's why you should avoid functions that do more than just perform e.g a calculation, based on input parameters (no extra stuff ...).

All other things should be done using proper joins to tables, views, nested table expressions, @- #- ##temptables, ....

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Brookstone
Brookstone
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 211
Thanks again for all the help and insight. We are going to rewrite the process to eliminate the functions.

Much appreciated!



Jim Russell-390299
Jim Russell-390299
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2325 Visits: 1403
FWIW - I noted a reference to @today (or something) earlier in your comments. I have been bit big time by T-SQL deciding that a time variable (e.g. from GetDate) was nondeterministic, even if the date/time parameter was passed in as a variable. I think it is a bug, but can't get anyone to agree.
Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26248 Visits: 12487
Jim Russell (9/16/2008)
FWIW - I noted a reference to @today (or something) earlier in your comments. I have been bit big time by T-SQL deciding that a time variable (e.g. from GetDate) was nondeterministic, even if the date/time parameter was passed in as a variable. I think it is a bug, but can't get anyone to agree.

I disagree.
I have several UDF's taking datetime parameter, and all are deterministic.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220968 Visits: 42002
Can you show us one, Sergiy... so we can get the idea of how to do it? Thanks.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search