Performance degradation MSSQL 2008 Enterprise x64

Last Post 21 May 2010 10:10 AM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

20 May 2010 04:57 AM
Hi, People... I have a big problem since I chanced my environment from Sybase ASE 12.5 to MSSQL 2008. My environment works fine during 3 and 4 weeks. After this, CPU rises 100% and everything stays too slow. Solution Default to solve this problem: reboot servers. After the reboot, all works very well during 3 and 4 weeks, when the problem comes back. I already did all I knowed to improve the performance, but the environment continues with this problem... I already set the 'lock pages in memory', set 'max memory' and 'min memory', set max dop. I run a reindex job everyday at night and update stats full scan at weekend. I already talked to Microsoft Brazil about my problem and They always say the problem is "bad objects developed". I can't believe it's problem of my environment is this. When I used Sybase, my environment worked very well with much less RAM. If this is the problem, why on the internet, we found a lot of articles saying that environments MS must be restarted frequently to retain good performance? ...that memory manager of SQL Server is weak? etc... Anyone can help me? Best Regards... Jorge.
New Member
New Member

20 May 2010 10:09 AM
For a well configured instance and a well behaived app there is absolutely no reason you need to reboot SQL Server on a regular basis. Did you look at the wait stats to see what it is SQL Server says it is waiting on? Did you trace to see what is using all the CPU? Without more details I would guess that you are running into one of two issues both that are related to the root cause though. I suspect you have a TokenAndPermUserStore issue or simply too many adhoc queries that don't reuse plans or a combination of the two most likely. Are you using stored procedures or are the database calls adhoc in nature. If they are adhoc you have to deal with the consequences that may be a side effect. The first article below will tell you if you have a problem with the TokenAndPermUserStore or not and point you to a KB article on what to do about it. The others are related to looking into wait statistics and should help you get to the bottom of what the waits really are. If you are doing adhoc queries and the TokenStore is not the issue it may be all the compiles for the lack of reuse in the query plans. Try turning on Forced Parameterization or the Optimize for Adhoc Workloads options. See BooksOnLine for more detals on those.
New Member
New Member

21 May 2010 08:39 AM
Thanks for your answer, gunneyk!
As you suspected, I think my environment has problems with a lot of ad hoc queries. I run this query...

select objtype,
   count(*) as number_of_plans,
   sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
   avg(usecounts) as avg_use_count
from sys.dm_exec_cached_plans
group by objtype ;

...and I received the following result...

objtype number_of_plans size_in_MBs avg_use_count
-------------------- --------------- -------------------- -------------

I read all of your article sugestions and I could understand, a little bit, about cache manage from sql server (my major experience is in Sybase ASE environments).

Well, by the article "", I saw I can run DBCC FREESYSTEMCACHE('SQL Plans') frequently to avoid growing of the cache (guilt ad hoc query).

So, What do you think (due your experience)?
Do I try to use this (above) as an alternative solution OR try to change de configuration that you recommended (Forced Parameterization or the Optimize for Adhoc Workload)?

What is most curious to see is...
If this problem is coming since 2005 - sp3, why the Microsoft didn't solve it in 2008? or 2008 SP1? and more...
Because we don't find any explanations, as your excelent explanation in this forum, on the MS oficial documentation?

Best Regards,
New Member
New Member

21 May 2010 10:10 AM
Clearing the proc cache is only a temporary solution. You really need to fix the adhoc nature of the queries to get the most of the enviorment. But you need to determine if the TokenAndPermUserStore is the real culprit first. By the way this behaviour is not new to 2008, it has been here for a long time. Actually they have done a lot to minimize the issues and added features such as the Forced Parameterization and Optimize for Adhoc Workloads. If u have an issue with TokenStore you need to address that as per the KB and possibly in conjunction with the two options mentioned. They will potentially help both symptoms. But it is impossible o say if they will help or hurt without trying them. The good news is you don't need to restart for them to take affect. Just give them time to see how they behave either way. Ultimately you shoudl look at chaing your app so that you either use more stored procedures or you make the calls such that you can reuse query plans the way the engine is designed. The relational engine is optimized for certain things and makes some assumptions to get peak performance. One of them is proper calls to the database tht will reuse query plans. It's not hard to do that but if the developers of the application chose to ignore that aspect you will pay the price.

Acceptable Use Policy