I recently worked on a system with performance issues. Under load I found CPU would spike for a two minute stretch to 100% and then back a healthy 30% for five minutes and then back to 100% for two minutes. This patterned continued throughout the work day and resulted in what appeared to be random application freezes at the client. Analyzing server wait packets over time revealed CXPACKET wait types trended very closely to the CPU spikes. I found the query responsible for the CXPACKET and noticed it was performing a heap scan on a very large table. My first thought was to add an index, however after analyzing the existing indexes it appeared an index already existed that could be leveraged by the query. In fact, when I ran the query in SSMS it used the index and used hardly any resources compared to the table scan. At this point parameter sensitivity was the suspected culprit of the performance problem. I submitted a ticket to the vendor app to put forward a code change. While we waited for the vendor to respond something had to be done about the CPU spikes. I didn't want to flush the entire plan cache periodically as I feared this would introduce new problems. Instead I wrote a small script to search for the bad plan every five minutes and then flush it.
DECLARE @plan_handle VARCHAR(60) SET @plan_handle = (SELECT STUFF(convert(VARCHAR(60),qs.plan_handle,2),2,0,'x') FROM sys.dm_exec_query_stats qs WHERE qs.query_plan_hash = 0x9FA7AB0E3C3D9CA4) DECLARE @sql VARCHAR(100) WHILE @plan_handle IS NOT NULL BEGIN SET @sql = 'DBCC FREEPROCCACHE('+@plan_handle+');' EXEC(@sql) WAITFOR DELAY '00:00:30' SET @plan_handle = (SELECT STUFF(convert(VARCHAR(60),qs.plan_handle,2),2,0,'x') FROM sys.dm_exec_query_stats qs WHERE qs.query_plan_hash = 0x9FA7AB0E3C3D9CA4) END