Update Statistics is causing DB file to grow in always on envrionment

Question

Post reply

Update Statistics is causing DB file to grow in always on envrionment

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

PJ_SQL SSCertifiable Points: 6699 More actions · Answer 1

This is the link used for updating statistics

https://www.brentozar.com/archive/2016/04/updating-statistics-ola-hallengrens-scripts/

Thanks.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

John Mitchell-245523 wrote:

John
Jeff Moden wrote:
John Mitchell-245523 wrote:
That's not (just) updating statistics - it's index maintenance. When you rebuild an index, you should plan to have at least the same amount of free space in your database as the size of the index, and possibly more. If you don't, the data file is likely to have to grow.
John
It's probably not due to index maintenance. See the following setting in the code above...
@OnlyModifiedStatistics = 'Y',
Mmmm... the parameter is @OnlyModifiedStatistics, not @OnlyModifyStatistics. As I understand it, all that means is that statistics are only updated if there have been any modifications to the data. It doesn't mean that indexes are left untouched. This is how the documentation suggests doing statistics only:
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'
John

Thanks for the catch, John.

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PJ_SQL SSCertifiable Points: 6699 More actions · Answer 3

Sorry the confusion.

So, I have to modify SQL Agent job to execute :

EXECUTE dbo.IndexOptimize

@databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = NULL,

@FragmentationHigh = NULL,

@UpdateStatistics = 'ALL',

@LogToTable = 'Y'

INSTEAD of

EXECUTE dbo.IndexOptimize @databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y',

@LogToTable = 'Y'

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

No - that's just a sidebar. That's what you would do if you wanted to update statistics only, and not rebuild your indexes. I'm sure Jeff will explain the benefits of not maintaining indexes at all, but that's a slightly different matter.

John