Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

functions Expand / Collapse
Author
Message
Posted Friday, September 12, 2008 7:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 40,456, Visits: 36,912
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 2008, MVP
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

Post #568481
Posted Friday, September 12, 2008 7:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
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
Post #568494
Posted Friday, September 12, 2008 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 12:26 PM
Points: 127, 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.



Post #568503
Posted Friday, September 12, 2008 5:24 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.
Post #568998
Posted Saturday, September 13, 2008 2:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 35,610, Visits: 32,202
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #569065
Posted Sunday, September 14, 2008 6:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:21 AM
Points: 6,743, Visits: 8,517
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #569159
Posted Monday, September 15, 2008 11:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 12:26 PM
Points: 127, Visits: 211
Thanks again for all the help and insight. We are going to rewrite the process to eliminate the functions.

Much appreciated!



Post #569709
Posted Tuesday, September 16, 2008 6:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403
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.
Post #570141
Posted Tuesday, September 16, 2008 1:49 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.
Post #570582
Posted Tuesday, September 16, 2008 5:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 35,610, Visits: 32,202
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #570691
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse