July 13, 2007 at 8:47 am
I am using sql server 2005
my question is about index statistics
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 = ON )
but still when i run DBCC SHOW_STATISTICS('table_name','index_name')
i see results
am i missing something here
I want this 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
July 13, 2007 at 10:18 am
What makes you say it's the stats creation that makes it hang?
What makes you say it is hanging? Creating an index on a large table will take time. Some indexes I have take many hours to build.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 13, 2007 at 8:24 pm
no offence but the only way to test whether stats are problem or not is to stop them from being created
July 14, 2007 at 2:10 am
When you create an index, statistics will always be created. The no_recompute affects whether or not the stats will be updated when data changes.
The auto create stats just controls whether or not SQL will create statistics based on non-indexed columns while it's processing a query.
You can use profiler to watch stats creations and updates. There's an autostats event under Performance.
Very little can block a statistics create/update as the process runs in read-uncommitted isolation
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply