Please help- use to run in 5 minutes now 9 hours

NYdmx123 2016-08-03 02:52:45

Please someone give me a hand with this– please just point me in the correct direction

Aaron Bertrand 2016-08-03 02:59:54
This query took 9 hours to return 100 rows? Are you sure there weren't other environmental factors going on (like blocking)? Have you eliminated all of the common causes I outlined in this post? Can you submit an actual, post-execution plan generated by Plan Explorer (preferably not anonymized, or at least anonymized in a more modern version of Plan Explorer? This looks like a plan generated by SSMS and then opened and anonymized in an older version of Plan Explorer which stripped out the query.
NYdmx123 2016-08-03 03:06:07
no – im sorry the top 100 was just to get an actual plan — its actually the first insert statement is based on a view – so i have narrowed down the problem to this view — i will post on here, I am not sure how to create plan in plan explorer — what i do is run in SSMS and then right click and display in SQL plan explorer pro, then i save — my version of sql sentry is 10.1.94 — however my sql server is 2005 πŸ™
Aaron Bertrand 2016-08-03 03:07:09
Just copy your query to Plan Explorer's Command Text window, then click the Get Actual Plan button on the toolbar.
NYdmx123 2016-08-03 03:19:11
I cant believe your actually looking at one of my developers jobs right now!! or in future — when ever you answered me πŸ™‚ u made my day!!!!!!!!!!!
NYdmx123 2016-08-03 03:27:54
i did see i was doing over 2500 scans and over 875,000 logial reads for ChannelAdvisor_Sku_Suppress when i parsed the IO stats from the top 100 i was able to run
NYdmx123 2016-08-03 03:15:06
i just posted the view – with only estimated plan — i can not get actual plan to come up — the view runs for hours and hours and never returns anything πŸ™

the title of my post is

Aaron Bertrand 2016-08-03 03:17:40
"Hours and hours" is almost never a plan / query optimization problem. Again, did you check for other things, like blocking, what the query is waiting on, etc.? Please see this post.
NYdmx123 2016-08-03 03:17:08
thank you soooo much — any help u can give even a point in the wrong direction right now is better then what i got to go with — link text