SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slow response Query


Slow response Query

Author
Message
Thamizh
Thamizh
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 520
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.
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2183 Visits: 872
Could you post query and attach the query plan as a start?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90081 Visits: 41146
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Thamizh
Thamizh
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 520
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90081 Visits: 41146
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2183 Visits: 872
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
MartJ
MartJ
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 231
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.
Thamizh
Thamizh
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 520
@MartJ : Before dropping the index I tried "DBCC FREEPROCCACHE",but it didn't help Sad
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..).
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2183 Visits: 872
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
MartJ
MartJ
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 231
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search