May 30, 2017 at 4:53 am
Hi All,
Yesterday on a production environment we got complaints about very bad performance.
I logged in and found out that all queries related to a specific table even simple ones are taking very high unusual time although the issue didn't happen during peak time.
I suspected there is a problem with indexes so i tried to rebuild them but i couldn't due to traffic so i recreated them one by one.
Once i did that things went back to normal.
During the problem i checked indexes fragmentation and indexes on that table didn't show as having high fragmentation , we also have a job to rebuild indexes anyway.
I was really puzzled because of that problem and very worried as i don't know yet the root cause for problem as things were fine and all of a sudden this happened.
Looking forward for your insights please, if you need any specific details please let me know.
Regards
Nader
May 30, 2017 at 4:58 am
Most likely nothing to do with the indexes themselves.
Rebuilding indexes updates statistics and updating statistics invalidates all execution plans that use those stats. If a couple bad plans got into cache, your rebuilds would have resulted in new plans being generated and the problem going away.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2017 at 5:04 am
GilaMonster - Tuesday, May 30, 2017 4:58 AMMost likely nothing to do with the indexes themselves.Rebuilding indexes updates statistics and updating statistics invalidates all execution plans that use those stats. If a couple bad plans got into cache, your rebuilds would have resulted in new plans being generated and the problem going away.
Thank you for your reply.
But could you tell me please how could a bad plan go into cache, we really need to prevent this from happening again , yesterday some modules in our system were almost offline and the rest were very slow because of that.
Thanks again
May 30, 2017 at 5:32 am
Difficult to diagnose after the event. Maybe out-of-date statistics, or parameter sniffing. If you could capture some actual execution plans next time it happens, that would help. Do you have any examples of the queries that caused problems?
John
May 30, 2017 at 5:42 am
nadersam - Tuesday, May 30, 2017 5:04 AMBut could you tell me please how could a bad plan go into cache
Nearly impossible to answer, as there are lots of ways
Stale statistics
Bad parameter sniffing (https://app.pluralsight.com/library/courses/identifying-fixing-performance-issues-caused-parameter-sniffing/table-of-contents)
Poor query patterns (https://www.simple-talk.com/content/article.aspx?article=2280)
and more.
Best is to diagnose specifics if it happens again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2017 at 7:09 am
Thank you very much guys for your replies they are appreciated
Nader
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply