June 28, 2012 at 11:45 am
Hello,
I have a function that is part of a pre-aggregation process which is a part of a Datawarehouse load. We have this data load and process running in both a production and development environment. The aggregation on development is always running fast, very fast. Production runs very slow, very slow. Both servers have the same CPU's and memory available. The production CPU's are even faster than Dev. The only time prod runs very fast is after we clear the cache (DBCC FREEProcCache). everytime though it gets back to the nightly load it becomes slow again. Dev is always fast no matter what we do.
does nayone have any ideas?
Thanks,
mishka.
June 28, 2012 at 11:54 am
What else is running on the production system at night? It is not unusual to have jobs scheduled to perform the heavy-lifting type maintenance over night. If this is the case, you may be experiencing locking on tables being maintained that are blocking your extract process.
Check on that and let us know.
June 28, 2012 at 11:57 am
Yes, we thought about that too, but in the morning if I run the job it is still very slow. The only time it is fast is if i free the cache.
June 28, 2012 at 12:08 pm
One possibility could be that there is something in Prod before say nightly batch which execute your function
and a plan is generated. This plan might not be the best when it run for nightly batch and thus it is not performing properly. e.g. during day some query run and say use the fucntion for say 1 or 2 rows..Thus nl join would be prefered while during night batch it might be running for thousands of rows and then nl join is not efficient.
Try this..Check the execution plan when the function run slow and when the function run fast. It might give you some idea.Otherwise check the dev plan vs the prod plan...
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
June 28, 2012 at 12:41 pm
Yes, the function does run during the day multiple times, but yesterday during the day it was running fast. So it had a clean and optimized plan. During the nightly load it started to run slow again. Also, when I came in this moning it was still running slow until I Freed the Cache. Before I freed the cache on Prod I wanted to check the difference in the plan between Dev and prod but the checked the query plan attribute was NULL in both. Dev still ran fast with this value being NULL.
June 28, 2012 at 12:50 pm
For fucntion you can not get the plan as it is. You have to use the sys.dm_exec_query_stats dmv
and join this with dm_exec_query_text like below
select
dest.text,deqp.query_plan,deqs.*
from
sys.dm_exec_query_stats deqs
/*F0C6560A-9AD1-448B-9521-05258EF7E3FA*/ --use a newid so that we could exclude this query from the performanc emetrics output
outer apply sys.dm_exec_query_plan(deqs.plan_handle) deqp
outer apply sys.dm_exec_sql_text(deqs.sql_handle) dest
where
dest.text not like '%F0C6560A-9AD1-448B-9521-05258EF7E3FA%'
and dest.text like '%<unique text from the function>%'
GulliMeel
http://gullimeelsqlsybase.wordpress.com/2012/06/28/finding-top-n-worst-performaing-queries/
http://gullimeelsqlsybase.wordpress.com/2012/06/26/improved-merge-join-algorithm-in-sql-server-2008/
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
June 28, 2012 at 2:22 pm
Biggest problem, we can't see what you see. Anything we tell you is nothing more than a shot in the dark. When you talk about a function running, are talking about a function or a stored procedure? It would help if you could post the problem code, the DDL for any and all talbes involved (including indexes but not extended properties), the actual execution plans (one from when the code runs fast and one from when it runs slow).
June 28, 2012 at 2:41 pm
Gullimeel (6/28/2012)
For fucntion you can not get the plan as it is. You have to use the sys.dm_exec_query_stats dmvand join this with dm_exec_query_text like below
select
dest.text,deqp.query_plan,deqs.*
from
sys.dm_exec_query_stats deqs
/*F0C6560A-9AD1-448B-9521-05258EF7E3FA*/ --use a newid so that we could exclude this query from the performanc emetrics output
outer apply sys.dm_exec_query_plan(deqs.plan_handle) deqp
outer apply sys.dm_exec_sql_text(deqs.sql_handle) dest
where
dest.text not like '%F0C6560A-9AD1-448B-9521-05258EF7E3FA%'
and dest.text like '%<unique text from the function>%'
GulliMeel
http://gullimeelsqlsybase.wordpress.com/2012/06/28/finding-top-n-worst-performaing-queries/
http://gullimeelsqlsybase.wordpress.com/2012/06/26/improved-merge-join-algorithm-in-sql-server-2008/">
http://gullimeelsqlsybase.wordpress.com/2012/06/28/finding-top-n-worst-performaing-queries/
http://gullimeelsqlsybase.wordpress.com/2012/06/26/improved-merge-join-algorithm-in-sql-server-2008/
I tried to run this on dev but the query_plan still comes up null.
June 28, 2012 at 2:44 pm
Lynn Pettis (6/28/2012)
Biggest problem, we can't see what you see. Anything we tell you is nothing more than a shot in the dark. When you talk about a function running, are talking about a function or a stored procedure? It would help if you could post the problem code, the DDL for any and all talbes involved (including indexes but not extended properties), the actual execution plans (one from when the code runs fast and one from when it runs slow).
Yes, I understand what you say. It is a function, not a procedure. I will not be able to post the code. I will post the plan once I can get it.
Thanks for your help.
June 28, 2012 at 4:03 pm
Okay, what kind of function are we talking about; scalar, inline table valued, multi-statement table valued function?
How is it being used?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply