November 20, 2018 at 11:13 am
can I ENABLE auto create or update stats for each user db's anytime on prod? or has any performance issue or downevent?
What is the command?
I am aware of properties and update the settings only..
Please advise.
November 20, 2018 at 1:36 pm
To change the setting use the ALTER DATABASE command. Here's a nice article that describes the effects of the STATISTICS settings of ALTER DATABASE:
https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/
This command itself will not cause downtime, but any queries that run after these settings are turned on may experience a slight delay the first time a statistics entry needs to be created or updated.
November 27, 2018 at 11:01 am
sizal0234 - Tuesday, November 20, 2018 11:13 AMcan I ENABLE auto create or update stats for each user db's anytime on prod? or has any performance issue or downevent?
What is the command?
I am aware of properties and update the settings only..
Please advise.
I would suggest for PROD to run Update Stats only during the time when the load on the server is less. Like after working hours.
November 28, 2018 at 6:34 am
sizal0234 - Tuesday, November 20, 2018 11:13 AMcan I ENABLE auto create or update stats for each user db's anytime on prod? or has any performance issue or downevent?
What is the command?
I am aware of properties and update the settings only..
Please advise.
Chris Harshman cited an article and is correct in saying that changing the setting won't cause an issue directly. Indirectly, it can cause a bit of a problem in that it auto-updated statistics are typically at a lower sample rate than FULL. Depending on the data involved, that can miss some things and cause a bit of a slowdown.
And, to rinu Philip 's point, if statistics do update on a large table, it can take quite some time. You should have some scheduled job that runs to check and update your stats and do stats maintenance during the quietest time(s) although, for me, turning off auto-update stats would be a bit silly because of the substantial influx of data that we have.
Also, be wary of anything you may get off the internet for maintaining stats. When I first started at my current company, the stats maintenance code they downloaded off the internet made stats for ALL columns of ALL tables. While stats don't take much room, they are only updated 1 column at a time and having stats on every bloody column on a long and wide table can take literally hours to update.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply