Auto create statistics and Auto Update Statistics - question

[bessebo]

We have noticed that during the day that we are experiencing some performance issues with our ERP system. I've been doing some fairly extensive research on this issue including monitoring the database on a daily basis with performance monitor. Since these slowdowns appear extremely random and do not seem to be related to locking within the database I am starting to think that the database properties Auto Create Statistics and Auto Update Statistics may be my culprit as they will tend to run at arbitrary times during the day. I have a weekly job that rebuilds all of our indexes in our databases.
I have 2 questions:
1) Can I just turn off these properties in the middle of the day without having to stop and restart SQLServer?
2) Do you think that rebuilding indexes once a week should be sufficient or should I run a sp_updatestats every night?

Regards,
Bessebo

 

[mrdenny]

1) Yes, no problem.
2) It depends on how much data is being added/updated to the indexes. If they change rarly then weekly should be fine. If you are pushing thousands of rows into the index daily, then you will want to rebuild it daily.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[bessebo]

Thanks Denny,
There are definitely certain tables that are updated more often than others. I may opt to perform an sp_updatestats every night (except Friday) and then run the SQLMaint on Sunday. Do you think it is possible that the Auto Updatestats could be causing a performance problem? It just makes sense that this could be my issue and have read that this could lead to performance issues.

Bessebo

 

[mrdenny]

It's possible. It wouldn't hurt to turn it off and see what happens.

I assume that you've checked the drive contention, disk queueing, memory issues, blocking, locking, etc?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[bessebo]

I have been monitoring regularly and have not been able to find anything in particular. Memory appears to be fine, the processors are being hit fairly hard and we are contemplating an upgrade in the near future. I also am not finding any locking at the time these occur. The delays will happen for maybe 30 seconds and then may not occur for another hour. By the time they call me the performance issue usually frees up. It just appears so random that I'm hoping that this is the reason. Thanks for the info.

Regards,
Bessebo

 

[mrdenny]

No problem. You might want to run profiles against the server for a day, and wait for the problem to show up again. It might be able to give some insite.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[bessebo]

OK,
Last night I had a job run that performed a sp_updatestats and this morning I unchecked the Update Statistics property on my production databases. Users immediately started having performance issues for about 5 minutes but it went away. So I'm a little concerned that by unchecking those boxes that maybe I have somehow introduced an issue. I'm holding my breath at this point and I even stopped any type of monitoring on my databases until I see some type of stabilization. Is it possible that by unchecking those database properties that some type of initialization has to take place which could've resulted in a performance hit? Let's see how the rest of the day goes.

Regards,
Bessebo

 

[mrdenny]

It could have, but I don't think so.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[bessebo]

There were no other performance issues since first thing this morning. Let's hope it continues this way.

Regards,
Bessebo