Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Analyzer
Posting Yak Master
115 Posts |
 Posted - 2009-05-12 : 09:11:53
|
| A large aggregation query processing around 30 million rows suddenly slowed by 120%. Waits, queues look good, no locks or blocks and nothing has been deployed recently on this production DB.When I restore the DB to another test box, it will also exhibit the indentical 120% increase, but some days it does not and will be much quicker.The issue has nothing to do with any code schema\changes, not HW related, not conflicting with other processes on the test box, is portable but intermittant on other servers which will use the same statistic, indexes, data etc. I have updated statistics but still the problem persist - any ideas which factors may cause this ? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-12 : 09:17:13
|
Have you checked the procedure cache?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2009-05-12 : 09:53:19
|
| Have now checked the procedure cache and the 'Adhoc' value is low, doesn't look like any bloating is going on and Page Life Expectancy is still high. Would recyling the server possible clear the root cause? |
|
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2009-05-12 : 11:48:36
|
| Noticed the SQL server is maxing out (100%) a single CPU on 16 CPU server. What conditions might cause when the workload should be split? |
|
|
|
|
|
|
Why does query now run 120% slower?