July 19, 2013 at 11:57 pm
In my server there is a specific query which was running 3 sec.Suddenly query was running slow.
I checked Blocks/IO/CPU/Memory/Stats/Index and everything was good except the query plan.
In the query plan ... I saw one of the table index scan cost is around 90% and there was a recommend message in same window that "Index missing" so i added the missing index,after that query performed well.
After couple of days same issue came back, this time query plan was also good.I checked all perspective and every thing is good.I have no idea to fix this issue.This time i dropped and recreated the same index what i created previously and that fix the issue.
I've no idea how it has been fixed? Even, that index does not have any fragmentation! As per my knowledge dropping and creating the index on live DB is not a recommended one.
Do i need to check anyother thing?
Please help me on this.
July 20, 2013 at 4:20 pm
Could you post query and attach the query plan as a start?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 20, 2013 at 5:35 pm
Are you rebuilding indexes and stats in a regular maintenance plan? Does the query take parameters?
Please see the second link in my signature line for the best way to post performance problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 20, 2013 at 8:05 pm
Thank you for your reply Jeff ,
# Yes, We do weekly Index- Maintenance and stats
# Yes the query takes the parameters
# Sorry, Cannot share the query plan because of the work restrictions.
But i can explain,Only one Non-clustered index cost was high which was 95% during the slow performance.
Right now i'm simply dropping that Non-clustered index and recreating (Rebuild,Re-org was not fixing the issue) if the issue heads up.
By doing that, index scan cost reduced from 95% to 35%
I don't know what makes performance improvement to the query while dropping & Re-creating the index even Re-Build & Re-org was not working.
July 20, 2013 at 9:51 pm
From the sounds of it, you may be experiencing a thing called "Parameter Sniffing", which is usually a good thing, by the way. Sometimes it causes a bad plan to be selected, though. There are many "fixes" for such a thing and what the bad Parameter Sniffing is and how to fix it appear in many articles and post. Just Google for "Parameter Sniffing".
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2013 at 3:19 am
Thamizh (7/20/2013)
Sorry, Cannot share the query plan because of the work restrictions.
Without the plan and the query, there is not much we can do. Performance tuning is not about telepathy.
You could try SQL Sentry Plan Explorer which permits you anonymise a plan. But that only gives us the query plan, but not the query and nor the table definitions which we might ask for later on.
This article on my web site discusses parameter sniffing and how you can deal with it: http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp
But if your employer/client does not permit you share information in public forums, and you are not able to resolve the issue from articles like mine, your options for free help may be exhausted, and you may need to hire a consultant.
But i can explain,Only one Non-clustered index cost was high which was 95% during the slow performance.
Note that the percentages you see are estimated costs, and may not reflect the actual costs.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 21, 2013 at 3:56 am
I believe dropping an index will cause any execution plans that use that index to be recompiled on the next run as the index is no longer valid. As you are then recreating the index, the same effect will be experienced again i.e. when the query is next executed the optimizer will have to build up a new plan with a new set of parameter values saved in the plan, these are the values that the optimizer assumes are standard and good to use.
It sounds that the values saved are only really a good reflection of the data in the underlying tables for a few days. It may be worth reviewing the maintenance on the stats and seeing if having more frequent updates on the stats on the relevant tables may have a positive benefit:
http://msdn.microsoft.com/en-us/library/ms187348.aspx
http://msdn.microsoft.com/en-us/library/ms173804.aspx
You could also look at using OPTION (recompile) for specific parts of the query where the issue is or even WITH RECOMPILE for the entire procedure. These will cause the optimizer to recompile either part or all of the plan respectively. I DON'T RECOMMEND THIS in general but it if you try it and things work better for days then it would tell you that you have negatively performing parameter sniffing and should take a look at ways to avoid that.
Good luck in your investigations and please post how you get on.
July 21, 2013 at 11:54 am
@martj-2 : Before dropping the index I tried "DBCC FREEPROCCACHE",but it didn't help 🙁
Will "DBCC FREEPROCCACHE" clear the old plan ?
@Erland : i do understand,without Query or Query plan its hard to bring the solution.
I'm trying to find out all the possible outcomes if i drop and create the index?
If i get that, I can avoid the "dropping & recreating index" and go to direct steps(example:freeproccache,updatestats,etc..).
July 21, 2013 at 1:49 pm
Thamizh (7/21/2013)
Will "DBCC FREEPROCCACHE" clear the old plan ?
I'm trying to find out all the possible outcomes if i drop and create the index?
If i get that, I can avoid the "dropping & recreating index" and go to direct steps(example:freeproccache,updatestats,etc..).
Dropping and recreating means that you flush all plans related to the table and you also update statistics with fullscan. If it's a parameter-sniffing issue, flushing the plan is enough. If the issue is due to outdated statistics, it's not.
One thing to check is if the query includes a search condition to select "the most recent rows" or somesuch. Such queries can quickly get problem with outdated statistics, because all search conditions fall outside the histogram.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 22, 2013 at 1:59 am
As Erland Sommarskog says, DBCC FREEPROCCACHE will indeed clear the plan in question and all others; be careful with the use of it and not good on production and from you're first post you're on production?
It's looking somewhat like it's a stats issue so maybe take a look at the execution plan and see how many rows are being estimated compared to how many rows are actually returned, big differences could indicate outdated stats. In a previous post Erland mention SQL Senty Plan Explorer, go download a copy (it's free) and have a go. It really is a great tool and it will put all the stats info into a table for you highlighting where estimates differ from actual values.
Mart
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply