Should I refresh query plan cache Please let me explain my problem and situation: 

I have a web application - MVC3, MSSQL Server 2005, LinqToSQL. It has been running great until one fine morning I pushed a lot of rows to a table that is heavily used and since then I was getting query timeouts. In order to fix the problem I run the Database Tuning Advisor and I added some Indexes and Statistics. I also created a maintenance plan to rebuild indexes daily. After those additions, the application has been behaving unstable; it would work fast for couple of hours then it would start timing out again. Next, life forced me to clean up the table in matter, and the amount of rows in it is even smaller now than it was before but the timeouts are still happening. So, I removed all indexes that I created and now the website is much more stable but from time to time I still see some timeouts.

I've been trying to figure out how to fix those queries and when I profile it and paste the query directly into the SQL Management Studio it returns the results in 1 second, but when I run this query from my application, it's about 25 seconds. Then after it runs for the first time, next time it goes as fast as on the server! 

I started doing some research and it looks like when I played with all those indexes my query plans got messed up and now they are creating issues. 

My questions are :


Should I refresh my query plan cache (around 22000 queries - a lot of them has been used only once) and 
If I do it, what would the impact be on the SQL while they are all rebuilding?
Lets take your problem step by step:


  It has been running great until one fine morning I pushed a lot of rows to a table that is heavily used and since then I was getting query timeouts.


Whenever you do large updates/inserts to you tables, highly recommend to update stats and reorg/rebuild indexes. That way query optimizer does not select or produce bad plans on wrong estimates.


  In order to fix the problem I run the Database Tuning Advisor and I added some Indexes and Statistics.


Never do that without understanding your workload and proper testing the recommendations. Refer to Don’t just blindly create those “missing” indexes! by Aaron Bertrand.


  I also created a maintenance plan to rebuild indexes daily.


I would recommend you to look at the fragmentation ratio of the indexes and accordingly reorganize them or rebuild them. Best is to use SQL Server Index and Statistics Maintenance as it is best free software out and implemented widely.


  Next, life forced me to clean up the table in matter, and the amount of rows in it is even smaller now than it was before but the timeouts are still happening. So, I removed all indexes that I created and now the website is much more stable but from time to time I still see some timeouts.


Same as above. Now the optimizer has wrong statistics and hence inefficient query plan can be produced. Best is to UPDATE STATISTICS and mark that table for recompile using sp_recompile , so next time the optimizer will generate new plan based on updated stats available.

Also read up on : Slow in the Application, Fast in SSMS?