May 23, 2011 at 12:18 pm
Ninja's_RGR'us (5/23/2011)
GilaMonster (5/23/2011)
sqldba_icon (5/23/2011)
So if i run this select first time, it will take 45 mins and then i run in few mins it will take only few secs reason is because the plan is cache.Are you absolutely sure it's because of the plan being cached and not the data being cached? 45 minutes is far beyond a reasonable compile time, I've never seen the optimiser take more than 5 minutes and that was for a horrendous mess of nested views.
5 minutes? :blink::blink:
The worse I've seen is 0.5 sec and that was for a 3000 dynamic series of statements... 15 queries and 90 tables.
I can't imagine what would take 5 minutes to compile :w00t:
View upon view upon view upon view, all multi-table and complex. It wasn't the optimiser that took the longest, it was the parser/algebratiser. To inline all the views then simplify the resultant query to eliminate unnecessary tables and columns.
Not something that I'd expect to run into on 4even an occasional basis.
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
May 24, 2011 at 11:10 am
sqldba_icon (5/23/2011)
This job runs once everyday. I have nailed it down to a select statement which has the issue. So if i run this select first time, it will take 45 min and then i run in few min it will take only few secs reason is because the plan is cache. Now for time being i want this plan to stay in memory so that when the job runs next day it doesn't have to recreate the plan. Makes sense?
I've never seen anything quite so dramatic as 45 min down to a few sec with the same query just from loading procedure cache, data cache or both. This is a huge difference. The biggest difference, and this was data cache, not procedure cache, was from 4 min down to about 20 sec, and this was on a server that was very busy with other requests.
You might want to look at the other traffic on the server when the job is run the first time. Someone doing a backup, some monster ETL process running, etc. I've seen servers thrash on this sort of thing where one query gets the short end of the stick on resources while another process (the ETL process) got all the goodies.
Todd Fifield
May 25, 2011 at 5:56 am
You can try using Plan Guides. With SQL Server 2008 , you can create a plan guide based on a query plan handle. Execute the query manually, get the plan handle from DMV's, then create the plan guide. Then, anytime the query is executed, it will use the plan guide, which is force use the query plan that you attached.
But as everyone else has said, this is a last resort. If statistics are out of whac, or schema changes, your plan may actually take longer than letting SQL create a new plan each time.
May 26, 2011 at 8:16 am
You have been working at this for 4 days now. Get a performance tuning professional to give it a review and you should be able to see significant improvements in just an hour or two's time.
I also note that you haven't posted table/index/select scripts either and we cannot begin to help you without that.
It is quite likely that your system may just be under powered for what you are asking of it too! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 26, 2011 at 4:37 pm
Thanks
May 27, 2011 at 12:41 am
jason.spangler (5/25/2011)
You can try using Plan Guides. With SQL Server 2008 , you can create a plan guide based on a query plan handle. Execute the query manually, get the plan handle from DMV's, then create the plan guide. Then, anytime the query is executed, it will use the plan guide, which is force use the query plan that you attached.But as everyone else has said, this is a last resort. If statistics are out of whac, or schema changes, your plan may actually take longer than letting SQL create a new plan each time.
We tried plan guides for a dynamic query to force a good plan with greate success, but the underlying issue was caused by old statistics, so when we managed to pursuade the client to update statistics on a regular basis, the plan guide wasn't needed anymore. We got good plans very time after that.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
May 27, 2011 at 6:55 am
sqldba_icon (5/23/2011)
This job runs once everyday. I have nailed it down to a select statement which has the issue. So if i run this select first time, it will take 45 mins and then i run in few mins it will take only few secs reason is because the plan is cache. Now for time being i want this plan to stay in memory so that when the job runs next day it doesn't have to recreate the plan. Makes sense?
I doubt that the 45 minutes is actually spent recompiling a new plan. When looking for a clue to a problem like this, then start with the wait state queues.
Performance Tuning Waits Queues.doc
http://technet.microsoft.com/en-us/library/cc966413.aspx
sys.dm_os_wait_stats
http://technet.microsoft.com/en-us/library/ms179984.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply