November 18, 2002 at 11:54 am
Greetings,
I am managing the database portion of the ongoing development process in our organization. We have several developers that are constantly creating tables or new columns on tables.
I want to make sure that statistics are created on these new columns or tables, so that performance will be optimized when we release our product to our customers. What I want to do is run sp_createstats at the end of every build....this is a very clean way to ensure that any columns without statistics will have statistics created for them.
However, when I run sp_createstats on our database, I get the following error:
'Cannot create more than 249 nonclustered indices or column statistics on one table.'
the above message relates to a huge table that we use for data loads. Anyway, the error apparently causes the sp_createstats to quit running. I am worried that it does not get to all the columns before it errors out. So, I ran it a second time, and I get a bunch of these errors:
Server: Msg 515, Level 16, State 2, Procedure sp_createstats, Line 99
Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.
The statement has been terminated.
Does anyone know what this is? Is this the best way to ensure that statistics are created on new objects? If not, what's the better way?
Thanks,
SB
November 19, 2002 at 2:28 am
Hi, did you try
EXEC sp_createstats 'indexonly'
This create statistics only for columns with an index.
November 19, 2002 at 8:01 am
Is it a good idea to create statistics on columns that are not indexed? Is there a performance benefit to this, or is it not worth the effort?
If it is recommended, I would like to keep ALL statistics updated, not just the ones on the columns with indexes.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply