Statistics issue. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Statistics issue.

Author

Topic

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-04-03 : 07:59:20

Hi All,

i have a table as shown below:

create table stattest
(
id int,
name varchar(30),
sal int
)

Currently i don't have any statistics defined on any column of this table.

my data base settings related to below columns from sys.databases table are

is_auto_create_stats_on --->1
is_auto_update_stats_on --->1
is_read_only --->0

if i run below query
select * from stattest where id=120
statistics are created automatically on the column "Id".

Can you please confirm is this because of option "is_auto_create_stats_on" set to 1?

is there any way to update value of this column "is_auto_create_stats_on" to "0" from sys.databases table?

Thanks,

M.MURALI kRISHNA

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-03 : 10:10:37

ALTER DATABASE <db name> SET AUTO_CREATE_STATISTICS OFF

But that's not a good idea unless you have a very good reason to do so.

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-03 : 11:48:07

Why would you not want statistics?

-Chad

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-04-03 : 14:38:41

When AUTO CREATE STATISTICS is set at OFF , a manual process must be set up to create statistics. Normally I have AUTO CREATE STATISTICS at ON. This means the SQL Query Optimizer creates the statistics on individual columns for cardinality improvement.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources