November 6, 2002 at 1:34 am
Warm greetings!
I just want to ask for your professional opinion about my concern. I would like to further
understand the reason why SQL Server 2K (Execution Plan) displays a Warning
message saying that the table (fields) doesn't have statistics. I have
created an SQL Union statement and after taking a look at the execution plan
(Clustered Index Seek) has a red warning as i've said earlier. Can you
please further elaborate this matter? Is this bad or good? Is this
something that I should have set on my tables?
Your prompt reply is very much appreciated. Thank you very much!!
Best regards,
Ramil Domingo
Developer
Comtech Solutions
November 6, 2002 at 2:37 am
In order for SQL Server to come up with the best way to execute your query, it needs statictical information on the distribution of the data (e.g. if half of the rows in a table have a particular value, it is quicker for SQL Server to scan the whole table instead of doing an indexed lookup).
When you create an index on a table, SQL Server will create these statistics automatically, and if you have the 'Auto Update Statistics' option set, it will keep the statistics up to date for you.
Sometimes SQL Server will want statistics on columns that aren't indexed. If you have the 'Auto Create Statistics' option set, it will create these automatically.
I suspect that you have the 'Auto Create Statistics' option turned off. That is why SQL Server is complaining about missing statistics. If you right click the 'red warning', one of the options is to create the missing statistics.
It can make a huge difference to the speed of the query when these statistics are created, as SQL Server can choose a totally different way of running the query as a result.
November 6, 2002 at 3:55 am
Thank you for the quick reply but I double checked my database options and both Auto Update Statistics and Auto Create Statistics are set to On. Is there any other way to test whether this option is really set to On let's say using QA or something?
Thanks for the information.
November 6, 2002 at 4:23 am
You can check by executing the system stored procedure sp_helpdb.
I'm sure it will give you the same answer as enterprise manager
November 6, 2002 at 4:24 am
Even with Auto Create statistics on you have to use that column for multiple query runs. If this is the first time you used that column or is infrequently used auto create may have determined creating these statistics is not worth while yet.
Also, the whole 'Auto Update Statistics' is sort of misleading. The statistics are not updated until a certain percentage of change has occurred in the data of a table. On small tables with a high number of transactions this may be often, if a large table even with the same number of transactions as the small it may happen less frequently than you need. Myself and most people I know run sp_updatestats occasionally to keep these inline with the actual tables.
Now as for it ON, the properties in EM with not report checked unless is ON.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply