Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 :

  1. Should I refresh my query plan cache (around 22000 queries - a lot of them has been used only once) and
  2. If I do it, what would the impact be on the SQL while they are all rebuilding?
share|improve this question
    
Since you are using 2008R2, have you enabled optimize for adhoc workloads configuration setting ? Also check how much is your adhoc plans occupying using query form sqlskills.com/blogs/kimberly/…. Refer to Slow in the Application, Fast in SSMS? sommarskog.se/query-plan-mysteries.html Also suggest you to look if statistics are up-to-date or not and indexes are properly defragmented. –  Kin Jul 18 '13 at 20:31
    
I am rebuilding my indexes daily, however I think I'll have to take a look at statistics. –  bobek Jul 18 '13 at 20:37
    
how about optimize for adhoc workloads configuration setting ? Is it On or OFF ? –  Kin Jul 18 '13 at 20:39
    
I am sorry Kin, I use 2005 server, not 2008. I will correct the question now. –  bobek Jul 18 '13 at 20:53
    
Don't run DTA on a production server. You could make the situation worse because DTA will create hypothetical statistics and indices. –  Bogdan Sahlean Jul 19 '13 at 10:17

2 Answers 2

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?

share|improve this answer

First of all, no, you probably shouldn't clear your query plan cache. You probably having a problem with bad parameter sniffing. Here are some articles about it by various people. Greg Larson with SimpleTalk, Jes Schultz Borland with Brent Ozar Unlimited, and Thomas LaRock. You can do a quick search and you will see tons of others. It's a popular subject.

The first thing I would do if I was you is update statistics for each table affected.

update statistics TableName;

Next run sp_recompile on the tables affected. That will recompile all stored procedures associated with that table. This will have some impact (I'll explain in a moment) but is better than clearing out your whole cache.

EXEC sp_recompile Table;

When you clear out your query plan cache, or mark a stored procedure for recompile the optimizer has to re-create the plan for that piece of code. This of course takes time. And if you have an active system completely clearing out your cache will slow your whole system down for a period of time while everything is re-created. Of course each query/SP will only be re-created as it is used.

share|improve this answer
    
Thank you! So, if I change indexes (add or remove them, or change them) does that make my query plans go bad? –  bobek Jul 18 '13 at 20:41
    
No, but you loaded a bunch of data into the table, and if it wasn't over the limit for updating the stats automatically then it may have thrown them off. –  Kenneth Fisher Jul 18 '13 at 20:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.