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.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2004-05-13 : 19:38:25
|
| Hi,I have a table that stores vehicle information. A common query is getting a vehicle by supplying year, make and model. In Enterprise manager, I created an Index using these columns (VehicleID is the primary key so a clustered index already exists on that column. This new index would be a non-clustered index... correct?). On the properties page for indexes there is a checkbox for "Do not automatically recompute statistics". By default it is checked. But, if you read the SQLServer help:>>Select this option to tell SQL Server to use previously created statistics. This choice may not produce optimal results and is not recommended.Maybe I'm reading it wrong but it appears the help says don't check it, but by default it is checked. (Typically if I'm unsure about a setting I leave the default.) Should it be checked or not? Does it make a difference?Also, since it is a non-clustered index, the fill factor doesn't really apply? (I left it at 0, which is the default)I don't have a lot of experience with indexes so I want to make sure it is correct.ThanksNic |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-05-14 : 07:19:42
|
| You really need to understand what "recompute statistics" means in order to make an educated decision on how to set it for you index. There are situations when it should be used and when it shouldn't (that's why it is an option). Do some reading on the the topic and see if it clears things up. Post further questions on the setting.Fill factor does, in fact, matter for non-clustered indexes. It matters for non-clustered indexes every bit as much as for clustered indexes. Again, I suggest you read up on fill factor. If something is unclear, post again.No one (who knows what they are doing) is going to tell you to always recompute statistics and always set the fill factor to 10. It all depends on what you need the index to do ...Jay White{0} |
 |
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2004-05-14 : 12:23:08
|
| Maybe I'm not very good at English but I'm having trouble understanding the SQLServer documentation concerning recomputing statistics on indexes.STATISTICS_NORECOMPUTEDisabling automatic recomputation of distribution statistics may prevent the SQL Server query optimizer from picking optimal execution plans for queries involving the table.So if you include the STATISTICS_NORECOMPUTE in your create index statement are you enabling or disabling automatic recomputation?Double negatives have always caused me trouble ;)I read it as disabling (I just want to make sure because when you user Enterprise manager to help create an index by defualt it includes the STATISTICS_NORECOMPUTE option)Nic |
|
|
|
|
|
|
|
|
non-clustered index