SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Statistics issue.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mmkrishna1919
Yak Posting Veteran

India
95 Posts

Posted - 04/03/2013 :  07:59:20  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 04/03/2013 :  10:10:37  Show Profile  Visit russell's Homepage  Reply with Quote
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.

Edited by - russell on 04/03/2013 10:11:10
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/03/2013 :  11:48:07  Show Profile  Visit chadmat's Homepage  Reply with Quote
Why would you not want statistics?

-Chad
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2079 Posts

Posted - 04/03/2013 :  14:38:41  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000