September 25, 2009 at 4:59 pm
Hello!
I am using SQL Server 2005 64 bit Standard Edition. One of the processes I inherited selects all records from the view. View is quite complicated and built on top of other views/tables. A couple of days ago process has started running extremely slow. Step that retrieves data from the view has been running for hours (instead of usual 40-50 min) and I had to stop the job. I was trying to improve performance of the view. I recompiled/refreshed view updated statistics on base tables, etc. But performance is still horrible.
Any advice is greately appreciated.
Thanks,
Igor
September 25, 2009 at 7:22 pm
Maybe the amount of data eventually changed the execution plan and you are getting table scans? Did you check fragmentation of indexes on the tables in the view? Maybe you might think of partitioning of some of the tables?
Regards
Piotr
...and your only reply is slàinte mhath
September 26, 2009 at 4:48 pm
I think it is more better and so fast to act performance tuning plan using DTA especially for complex views then apply these enhancements of indexes and statistics on related tables to get best hoped performance
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
September 27, 2009 at 5:29 pm
I don't think there is significant amount of data added. I will try to run the view through DTA.
September 27, 2009 at 5:41 pm
Is there a possibility that an index on one of the tables was dropped?
Can you post the execution plan of the view?
Regards
Piotr
...and your only reply is slàinte mhath
September 27, 2009 at 9:25 pm
I've run into this problem many times especially when the view calls other views and aggregations are involved. Every system has a "tipping point" very much like the proverbial straw that broke the camel's back. When you reach it, 40-50 minute processes will take 4 to 6 hours just as you described... and it's going to get worse over time.
There is one and only one way to fix it and it's not hardware. Buying bigger, faster hardware will only buy you a month or two of time before the tipping point is reached again. The real fact of the matter is that the origininal 40-50 minute process was taking too long to begin with and the process needs to be rewritten. Sure, you can make a patch here and there and maybe do something with the views so that your not calling views of views, but even that's just a temporary patch that will eventually reach the next "tipping point".
The only way to permanently fix this type of problem is to rewrite the code properly. Yeap... it's gonna take some time to do that. 😉
--Jeff Moden
September 28, 2009 at 7:16 am
I'm going to pile on a bit and agree with Jeff. When you have views calling views that are joined to views, you will begin to get very poor execution plans. There are just too many statistics across too many tables for SQL Server to be able to properly sort them all out in order to return a good execution plan in a reasonable period of time. Trying to run it through the DTA and getting one or two new indexes is unlikely to have any affect.
Do you have the execution for when it performed well? You might want to compare that to the current execution plan, look for differences.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 28, 2009 at 7:43 am
Unless you capture plan cache, you have no means of history !
And if thing start going bad, it may go steep downhill !
Bad design (view on view, ..) probably ... ehm .. no IS the cause of this.
Things can can speed up this effect:
- fragmentation (logical/physical)
- fragmentation at OS level
- missing indexes
- missing statistics
Things I would start with:
- persist (save) the proc cache content
- use our typical DMV queries to attack performance problems (search SSC)
- watch out for (b)locking escalation.
- communicate you'll be forced ding all stuff that follows, and that applications will suffer this !
- rebuild all indexes
- update all statistics
- free proccache ! (to be sure all plans are using the new stats)
- Make a lot of wind ! That'll be the only way for projects to be forced to pay attention to design !
- analyse the persisted proc cache
- you could also launch a performance trace. That may be an easy way to capture top 10% of the actual load. (DTA can use this output !)
Keep a "Captain Kirk Logbook" so you know what you did and the impact those actions had on your system.
Johan
Learn to play, play to learn !
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[/url]
- 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
September 29, 2009 at 8:44 am
Unfortunately the true solution here maybe be to simply 'tune' the view(s), which given their complexity could take many days or even weeks of effort and carry significant risk of altering expected output. I personally hate scenarios like this - and I get paid by the hour to fix them!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 29, 2009 at 9:25 am
First of all, thanks to everybody for feedback! I have inherited the database and unfortunately limited in what I can do to speed up performance. I agree that rewriting process is the most efficient way to go, but I am somewhat limited in time. I didn't capture execution plan when query was performing 'well'. The main view is very convoluted. Overall code pattern reminds me of Russia doll matryoshka (one view inside another, etc.). In any case, I am trying to re architect the process to capture incremental changes instead of refreshing from scratch by querying entire view.
Thanks again,
Igor
September 29, 2009 at 9:30 am
ALZDBA,
Could you please elaborate on how you save and analyze cache? Do you save contents of master..syscacheobjects or corresponding DMV? What kind of analysis do you perform?
Thanks,
Igor
September 30, 2009 at 1:44 am
Igor Marchenko (9/29/2009)
ALZDBA,Could you please elaborate on how you save and analyze cache? Do you save contents of master..syscacheobjects or corresponding DMV? What kind of analysis do you perform?
Thanks,
Igor
I would persist the plan cache to be able to have some kind of comparisson for the queries.
SELECT CASE when st.dbid = 32767
then 'Resource'
else DB_NAME(st.dbid) end [DB_NAME],
OBJECT_SCHEMA_NAME(st.objectid,st.dbid) AS [SCHEMA_NAME],
OBJECT_NAME(st.objectid,st.dbid)AS [OBJECT_NAME],
st.dbid,
st.objectid
, st.text as TheText
,QP.query_plan
, cp.*
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
cross apply sys.dm_exec_query_plan ( cp.plan_handle ) QP
DMV sys.dm_exec_query_stats also provides usage and consumption info.
Every little bit of help counts, so investigate the DMVs, they have more data for you.
Johan
Learn to play, play to learn !
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[/url]
- 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
September 30, 2009 at 11:27 am
Thanks!
October 1, 2009 at 4:15 pm
are you updated statistics lately ? if no - you should, it may help!
October 1, 2009 at 4:24 pm
Yes, I did.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply