Performance degrades severely after executing REORGANIZE

Hi,
We have a SQLServer 2005 database that is undergoing a serious problem. It
is causing some searches to take 10 minutes rather than seconds. Our users
are almost unable to use the application since this has been ongoing for 3
days, so any help you can provide is much appreciated!
We have a job that runs weekly. The job does these steps:
1. Reorganize indexes
2. Update statistics
3. Cleanup history
In the Event Viewer of the server, we can see that this job fails every 2
weeks. So it runs and successfully one week, then fails the next, then
succeeds, then fails. We do not know why it fails, there is no error message
in the event viewer other than "step failed".
However, when it runs SUCCESSFULLY, we see a sever performance degredation
the next morning. When it fails, we see no performance changes the next day.
In the past, when this has occurred, we have run update stats and the
problem goes away. That happened about 3 times. However, this week, running
update stats is not fixing the issue. We have rebooted the server also, with
no affect.
Has anyone ever seen anything like this before? Please advise!!
thanks
-Robb
Hi Robb
Do you do anything after this such as shrinking the database or do you have
autoshrink enabled?
John
"Robb Gilmore" wrote:

> Hi,
> We have a SQLServer 2005 database that is undergoing a serious problem. It
> is causing some searches to take 10 minutes rather than seconds. Our users
> are almost unable to use the application since this has been ongoing for 3
> days, so any help you can provide is much appreciated!
> We have a job that runs weekly. The job does these steps:
> 1. Reorganize indexes
> 2. Update statistics
> 3. Cleanup history
> In the Event Viewer of the server, we can see that this job fails every 2
> weeks. So it runs and successfully one week, then fails the next, then
> succeeds, then fails. We do not know why it fails, there is no error message
> in the event viewer other than "step failed".
> However, when it runs SUCCESSFULLY, we see a sever performance degredation
> the next morning. When it fails, we see no performance changes the next day.
> In the past, when this has occurred, we have run update stats and the
> problem goes away. That happened about 3 times. However, this week, running
> update stats is not fixing the issue. We have rebooted the server also, with
> no affect.
> Has anyone ever seen anything like this before? Please advise!!
> thanks
> -Robb
|||Hi John,
No, nothing after that. And autoshrink is not enabled.
We did find out that the job failed due to lack of dick space for the
logs...that explains why it sometimes failed. But still, when it succeeds,
the performance goes right down the tubes. It has been bad now since Monday.
Queries taking 10 minutes.
Any advice on what else to look at or try?
thanks
Robb
"John Bell" wrote:
[vbcol=seagreen]
> Hi Robb
> Do you do anything after this such as shrinking the database or do you have
> autoshrink enabled?
> John
> "Robb Gilmore" wrote:
|||Hi Robb,
From your description, I understand that:
After executing REORGANIZE to index, you found that the performance
degrades dramatically. You have used UPDATA STATISTICS, but it still
couldn't get back to normal. Queries took about 10 minutes.
If I have misunderstood, please let me know.
It is most likely caused by missing indexes and changes in statistics.
If you can locate which statistic caused the performance degrading, you can
try drop it and then recreate it by using:
CREATE STATISTICS X WITH FULLSCAN, NORECOMPUTE
and a batch job that does
UPDATE STATISTICS X WITH FULLSCAN, NORECOMPUTE.
Also, I recommend that you refer to this article for troubleshooting this
issue:
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/pro...prb.mspx#EYBAG
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
http://www.microsoft.com/technet/pro...ats.mspx#EFFAE
NOTE: Our managed newsgroup is focused on break/fix issues that are neither
urgent nor complex. If the issue is urgent to your business, it is
recommended that you contact Microsoft Customer Support Services (CSS) via
telephone so that a dedicated Support Professional can assist you in a more
efficient manner. Please be advised that contacting phone support will be a
charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Robb
That is very odd, if you replaced this with a script that used DBCC
DBREINDEX do you still get the same problem?
John
"Robb Gilmore" wrote:
[vbcol=seagreen]
> Hi John,
> No, nothing after that. And autoshrink is not enabled.
> We did find out that the job failed due to lack of dick space for the
> logs...that explains why it sometimes failed. But still, when it succeeds,
> the performance goes right down the tubes. It has been bad now since Monday.
> Queries taking 10 minutes.
> Any advice on what else to look at or try?
> thanks
> Robb
> "John Bell" wrote:
|||Hi Robb
This is very odd, if you replaces this task with a script that used DBCC
DBREINDEX do you get the same problem? You could also add in a script that
out the information from sys.dm_db_index_physical_stats() before and after
this task!
Have you checked for any other jobs that are run against the database?
John
"Robb Gilmore" wrote:
[vbcol=seagreen]
> Hi John,
> No, nothing after that. And autoshrink is not enabled.
> We did find out that the job failed due to lack of dick space for the
> logs...that explains why it sometimes failed. But still, when it succeeds,
> the performance goes right down the tubes. It has been bad now since Monday.
> Queries taking 10 minutes.
> Any advice on what else to look at or try?
> thanks
> Robb
> "John Bell" wrote:
|||Thank you Charles and John,
We will open a call with CSS.
-Robb
******************************************
"Charles Wang[MSFT]" wrote:

> Hi Robb,
> From your description, I understand that:
> After executing REORGANIZE to index, you found that the performance
> degrades dramatically. You have used UPDATA STATISTICS, but it still
> couldn't get back to normal. Queries took about 10 minutes.
> If I have misunderstood, please let me know.
> It is most likely caused by missing indexes and changes in statistics.
> If you can locate which statistic caused the performance degrading, you can
> try drop it and then recreate it by using:
> CREATE STATISTICS ?X WITH FULLSCAN, NORECOMPUTE
> and a batch job that does
> UPDATE STATISTICS ?X WITH FULLSCAN, NORECOMPUTE.
> Also, I recommend that you refer to this article for troubleshooting this
> issue:
> Troubleshooting Performance Problems in SQL Server 2005
> http://www.microsoft.com/technet/pro...prb.mspx#EYBAG
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
> http://www.microsoft.com/technet/pro...ats.mspx#EFFAE
> NOTE: Our managed newsgroup is focused on break/fix issues that are neither
> urgent nor complex. If the issue is urgent to your business, it is
> recommended that you contact Microsoft Customer Support Services (CSS) via
> telephone so that a dedicated Support Professional can assist you in a more
> efficient manner. Please be advised that contacting phone support will be a
> charged call.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default...S;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||John and Charles,
Our issue is resolved! Thank you both for your help.
It's hard to believe, but the only thing we needed to do was use the "FULL
SCAN" option on the update statistics. Charles mentioned it in his post, and
my DBA tried it and it worked.
So we definitely learned the difference between using the FULL SCAN option
and not using it!
Thank you both for your input
-Robb
|||Hi,
Appreciate your update and response. I am glad to hear that the problem has
been fixed. If you have any other questions or concerns, please do not
hesitate to contact us. It is always our pleasure to be of assistance.
Have a nice day!
Charles Wang
Microsoft Online Community Support