Autogrowth values keep changing

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||

Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...

John

|||

Not a helpful reply, it can and it does.

I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.

This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.

This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.

|||

Yes this is quite serious. It stops our ETL-process because of the drive being full.

Nothing in our tracelog yet...

|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

|||

Thanks!

BR John

|||

HowardRichards wrote:

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

I was not aware of this bug. Thanks for the correction.|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!

Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.

|||

Yes - we've been hit by this as well.

We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.

The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!

So you need to change it using the alter database statement:

alter database dbname modify file (name = filename, filegrowth = 10%)

Roll on SP2...

|||

We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.

When attempting to change the value, the following error appears:

Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)

from the SQL Server 2005 SP2 fix list:

919611 (http://support.microsoft.com/kb/919611/)

FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1

Can anyone advise if running this statement will correct this issue permanently?

alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)

Thanks!

|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.

Autogrowth values keep changing

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||

Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...

John

|||

Not a helpful reply, it can and it does.

I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.

This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.

This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.

|||

Yes this is quite serious. It stops our ETL-process because of the drive being full.

Nothing in our tracelog yet...

|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

|||

Thanks!

BR John

|||

HowardRichards wrote:

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

I was not aware of this bug. Thanks for the correction.|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!

Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.

|||

Yes - we've been hit by this as well.

We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.

The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!

So you need to change it using the alter database statement:

alter database dbname modify file (name = filename, filegrowth = 10%)

Roll on SP2...

|||

We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.

When attempting to change the value, the following error appears:

Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)

from the SQL Server 2005 SP2 fix list:

919611 (http://support.microsoft.com/kb/919611/)

FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1

Can anyone advise if running this statement will correct this issue permanently?

alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)

Thanks!

|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.

Autogrowth values keep changing

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||

Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...

John

|||

Not a helpful reply, it can and it does.

I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.

This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.

This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.

|||

Yes this is quite serious. It stops our ETL-process because of the drive being full.

Nothing in our tracelog yet...

|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

|||

Thanks!

BR John

|||

HowardRichards wrote:

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

I was not aware of this bug. Thanks for the correction.|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!

Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.

|||

Yes - we've been hit by this as well.

We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.

The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!

So you need to change it using the alter database statement:

alter database dbname modify file (name = filename, filegrowth = 10%)

Roll on SP2...

|||

We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.

When attempting to change the value, the following error appears:

Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)

from the SQL Server 2005 SP2 fix list:

919611 (http://support.microsoft.com/kb/919611/) FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1

Can anyone advise if running this statement will correct this issue permanently?

alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)

Thanks!

|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.

Autogrowth values keep changing

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||

Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...

John

|||

Not a helpful reply, it can and it does.

I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.

This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.

This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.

|||

Yes this is quite serious. It stops our ETL-process because of the drive being full.

Nothing in our tracelog yet...

|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

|||

Thanks!

BR John

|||

HowardRichards wrote:

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

I was not aware of this bug. Thanks for the correction.|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!

Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.

|||

Yes - we've been hit by this as well.

We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.

The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!

So you need to change it using the alter database statement:

alter database dbname modify file (name = filename, filegrowth = 10%)

Roll on SP2...

|||

We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.

When attempting to change the value, the following error appears:

Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)

from the SQL Server 2005 SP2 fix list:

919611 (http://support.microsoft.com/kb/919611/)

FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1

Can anyone advise if running this statement will correct this issue permanently?

alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)

Thanks!

|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.

Autogrowth values keep changing

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||

Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...

John

|||

Not a helpful reply, it can and it does.

I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.

This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.

This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.

|||

Yes this is quite serious. It stops our ETL-process because of the drive being full.

Nothing in our tracelog yet...

|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

|||

Thanks!

BR John

|||

HowardRichards wrote:

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

I was not aware of this bug. Thanks for the correction.|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!

Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.

|||

Yes - we've been hit by this as well.

We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.

The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!

So you need to change it using the alter database statement:

alter database dbname modify file (name = filename, filegrowth = 10%)

Roll on SP2...

|||

We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.

When attempting to change the value, the following error appears:

Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)

from the SQL Server 2005 SP2 fix list:

919611 (http://support.microsoft.com/kb/919611/)

FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1

Can anyone advise if running this statement will correct this issue permanently?

alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)

Thanks!

|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.

Auto update to sql server tables

I'm changing data storage for an asp.net project from MS Access to Sql Server. I've got the web site working, but I need to update the sql server tables with data from our Oracle db daily. What is the "best" way to do that?

I've read about DTS, but have never done anything like that. Would it be worth the time and effort to study? (So far I've created a package, with the import wizard, that doesn't work & I don't have the authority to delete :-)

I know I could create a dataset with my Oracle data and use that to update sql server. But is there a way to schedule an aspx to run authomatically? Would this affect performance? The sql server db isn't very big (30-40,000 records), but the Oracle db is & I need to do quite a bit of manipulation to the data.

This is new to me & I'm don't know what I should be searching for to find help. And if there is a more appropriate place to post this question, please let me know.

Thanks.Yes. DTS is one good way to go and it is very easy to do.

Do you need to purge the data in your SQL before each load? Then the account has to have the right.

You can schedule the DTS to run at whatever time you want.

Auto update statistics

In SQL 2000 is there a way of changing the threshold that auto updating of statistics kicks in? Currently I am working with a db where the stats are becoming extremely poor after a day (as no automatic stats update is taking place) and the length of time a stored proc takes to compile and run goes from about 5 mins to around 120 mins. An update stats fixes the problem.

Thanks.

Hello...

You could use a querry hint inside that procedure if only one is affected by this. You could use the hint to force SQL to use the right Index.

Also are you using some default Values for your SP?
If those defaults are "totally different" from the data thats in the Table (example default is NULL for a field and you change this to -1 inside the SP), then SQL might also be tempted to pick the wrong plan for a SP|||

Thanks for your reply.

I would like to avoid query hints, as quite a number of procedures are affected by the stats problem.
I am not using default values within the SPs in fact a lot of the data being processed is fairly similar to the existing data within the tables. I don't quite understand why the stats are becoming out of date so quickly. They are not being automatically updated as the additional data being added to the database is fairly small (comfortably under the 500 + 20% of rows in the table that forces an auto update stats). That's why I was asking if there was a way of changing the threshold at which an auto update of stats takes place.

Alternatively is there a way of limiting the number of times the optimizer attempts to find a cheap execution plan?

|||Have you considered scheduling a job to update the relevant statistics at regular intervals?|||

Stefano Stefani - MSFT wrote:

Have you considered scheduling a job to update the relevant statistics at regular intervals?

Hi All,

I am facing the similar situation. When large number of records are added to certain tables, query optimizer generates very poor plan for those query that reference these tables. There are total of 3 tables and they contain 120 millions, 110 million,s and 70 millions of rows respectively.

Let suppose I run a job every hour but what is should be the right sample % to run the job effectively. Why auto update stats are not created promptly? What is the cause of query optimizer's such poor plan selection?

I am using SQL Server 2005 in Windows 2003 Server and we use SAN for data storage.

I would appreciate if someone can point to me some other usefull links.

Thanks in advance for all your help.

Regards,

JIm

|||

Have you figured out your issues?

If not do you have both Auto Update Statistics and Auto Update Statistics Asynchronously set to True? Check out this link and go down to the section "Maintaining Statistics in SQL Server 2005". It give some good information. http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx also look at the new feature of auto update statistics Async.

Auto update statistics

In SQL 2000 is there a way of changing the threshold that auto updating of statistics kicks in? Currently I am working with a db where the stats are becoming extremely poor after a day (as no automatic stats update is taking place) and the length of time a stored proc takes to compile and run goes from about 5 mins to around 120 mins. An update stats fixes the problem.

Thanks.

Hello...

You could use a querry hint inside that procedure if only one is affected by this. You could use the hint to force SQL to use the right Index.

Also are you using some default Values for your SP?
If those defaults are "totally different" from the data thats in the Table (example default is NULL for a field and you change this to -1 inside the SP), then SQL might also be tempted to pick the wrong plan for a SP|||

Thanks for your reply.

I would like to avoid query hints, as quite a number of procedures are affected by the stats problem.
I am not using default values within the SPs in fact a lot of the data being processed is fairly similar to the existing data within the tables. I don't quite understand why the stats are becoming out of date so quickly. They are not being automatically updated as the additional data being added to the database is fairly small (comfortably under the 500 + 20% of rows in the table that forces an auto update stats). That's why I was asking if there was a way of changing the threshold at which an auto update of stats takes place.

Alternatively is there a way of limiting the number of times the optimizer attempts to find a cheap execution plan?

|||Have you considered scheduling a job to update the relevant statistics at regular intervals?|||

Stefano Stefani - MSFT wrote:

Have you considered scheduling a job to update the relevant statistics at regular intervals?

Hi All,

I am facing the similar situation. When large number of records are added to certain tables, query optimizer generates very poor plan for those query that reference these tables. There are total of 3 tables and they contain 120 millions, 110 million,s and 70 millions of rows respectively.

Let suppose I run a job every hour but what is should be the right sample % to run the job effectively. Why auto update stats are not created promptly? What is the cause of query optimizer's such poor plan selection?

I am using SQL Server 2005 in Windows 2003 Server and we use SAN for data storage.

I would appreciate if someone can point to me some other usefull links.

Thanks in advance for all your help.

Regards,

JIm

|||

Have you figured out your issues?

If not do you have both Auto Update Statistics and Auto Update Statistics Asynchronously set to True? Check out this link and go down to the section "Maintaining Statistics in SQL Server 2005". It give some good information. http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx also look at the new feature of auto update statistics Async.