November 23, 2011 at 8:17 am
peoplesoft application with sql server 2005 upgraded to sql server 2008 r2(WITH COMPATABILITY MODE 80) on new hardware.
the configurations for the new environment is similar to the old with no significant change.
one application module with one sql instance is working good while the other application module is performing badly on the separate sql node. even both the node have similar settings of the old environment.
the CPU spiking above 90 if MAXDOP 0 so is set to 2 same as in the old environment
when tried to execute the query manually on the poorly performing SQL server through SSMS its working good, while the application is still performing bad.( for one query the execution time is double in new Environment than old)
The Isolation for the user database is set to Read committed snapshot and indexes are Rebuild.but still have Blocking and Locking issues during peak hours. (auto update/create stats enabled)
-updated manually created stats
-Dropped the tables of that query and recreated them
- file layout is verified.( i.e. TempDB, LOG and MDF files)
- checked with different MAXDOP options.
- checked for the updated/latest drivers on box
- Checked for issues in network
- the issues from application end is ruled out as other module is going good and a very few operations are working fine.
- checked for the I/o issues (even this can be rules out as the same san box used for old and new environment and in new environment the other module is going good withe this san box
please let me know if am missing something or is there anything specific to peoplesoft on SQL server environment should be done.
-Gowtham M
November 23, 2011 at 8:21 am
other observation is that Version store is used to be huge around 300GB in old environment which is around a few 10's of GB in the new environment.
the Execution plan is also different on old and new environment which i think is expected as index and stats are same and up to date but the old env is on 2005 and new env is on sql server 2008 r2
November 23, 2011 at 10:13 pm
parameter sniffing is also ruled out and trying to force use the good(fast) execution plan..
please give me the inputs if am missing something:-)
November 25, 2011 at 12:53 am
enabled trace flag 8780 as identified execution plan time out in the profiler collected.
some of the quries going good but other become worse and in error log shows
"A time out occurred while waiting to optimize the query. Rerun the query."
also heavy blocking is seens on the box.
🙂 inputs please 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply