question about index statistics please help

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

SQL Server 2005 Forums

Transact-SQL (2005)

question about index statistics please help

Author

Topic

rajkumarrai_bis
Yak Posting Veteran

78 Posts

Posted - 2007-07-12 : 04:00:45

I am using sql server 2005
my question is about index statistics
a)
how can we view whether statistics exists for a index
currently i am using DBCC SHOW_STATISTICS('table_name','index_name')
is it correct

b)
how can I stop sql server from creating statistics
I have set to false both auto create statistics and auto update statistics for DB
when i create Index i use no_recompute option
create index index_name on table_name with (STATISTICS_NORECOMPUTE = OFF )
but still when i run DBCC SHOW_STATISTICS('table_name','index_name')
i see results
am i missing something here

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-12 : 04:28:27

first you need to change the auto update statistics on your db settings page to off, then it will not auto update any more,
to see if an index has an statistics setting to on i think you can do it the best by using the same statement you already did.

Need an SQLDB consultant?
check www.veeningsengineering.nl

rajkumarrai_bis
Yak Posting Veteran

78 Posts

Posted - 2007-07-12 : 21:48:43

I have already set to false both auto create statistics and auto update statistics for DB
but still when I create Index I can see statistics for the index
Is there anyway i can stop server from creating it
what is the use of create statistics when it always gets created automatically

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-12 : 23:28:21

Why do you want to stop sql server from creating statistics? Optimizer needs statistics to build proper query execution plan.

rajkumarrai_bis
Yak Posting Veteran

78 Posts

Posted - 2007-07-13 : 00:55:20

because my table is very large while creating index if sql server creates statistic it gets stuck
therefore i want to avoid stats while creating index
I will create stats afterwards using create statistics command

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-13 : 18:01:28

You have no control on this, add more disk space.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 18:06:54

When you create an index for the first time, it WILL create the statistics for the data at that time. Otherwise there is no point in having an index. You can use the STATS_NORECOMPUTE option in the index creation script so it will not update the stats for any data changes. Ofcourse if you at the DB level it should suppress everything. But remember you do have so update them once in a while for optimal query plan.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources