Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Slow response Query Expand / Collapse
Author
Message
Posted Friday, July 19, 2013 11:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 49, Visits: 344
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.
Post #1475729
Posted Saturday, July 20, 2013 4:20 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 756, Visits: 631
Could you post query and attach the query plan as a start?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1475778
Posted Saturday, July 20, 2013 5:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 35,962, Visits: 30,255
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1475789
Posted Saturday, July 20, 2013 8:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 49, Visits: 344
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.
Post #1475791
Posted Saturday, July 20, 2013 9:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 35,962, Visits: 30,255
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1475798
Posted Sunday, July 21, 2013 3:19 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 756, Visits: 631
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
Post #1475808
Posted Sunday, July 21, 2013 3:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:49 AM
Points: 40, Visits: 178
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.

Post #1475812
Posted Sunday, July 21, 2013 11:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 49, Visits: 344

@MartJ : 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..).
Post #1475831
Posted Sunday, July 21, 2013 1:49 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 756, Visits: 631
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
Post #1475836
Posted Monday, July 22, 2013 1:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:49 AM
Points: 40, Visits: 178
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
Post #1475890
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse