Update Statistics

Question

Hi All,

The Statistics of the Column in my tables was not updated. My question is that " Whether the infrequent statistics updation will not fetch the datas, if it so please explain". What i know till now is that it will slow down the select operations and process will take time to complete.

 

Thanks In advance.

 

 

Answer 1

Statistics are the ones which would help SQL Server database engine whether to choose particular index or not while fetching data from a table. If the statistics are out of date, your indexes will not be properly utilized and then it degrades the performance. Hence it is recommended to have statistics updated on regular basis.

Regards,

---

Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

Answer 2

Hi,

More reading about Statistics can be done from below link.

http://oreilly.com/catalog/transqlcook/chapter/ch08.html

and ofcourse our Books On Line is best place to start.

---

Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

Answer 3

Hi,

If statistics are not updated, Will it wont fetch the datas? Because my Query inserts the data usind the Select statement for Eg,

Insert into Tbl_name

select column1 from tablename where condition.

When I check the Execution plan, it shows the warning that the STATISTICS are not available for column (say three columns).

When I checked the table, the datas are not inserted.

Is this caused by Statistics????

Note: The above query was executed from the front End, Where as I checked it from the back end (Query Analyser) it works fine.

Thanks.

 

Answer 4

Hi Ranjith,

When we update statistics for a table it update for each and every column irrespective of data is there or not. When ever a query which will hit this column its performance will depend on statistics, if statistics are there query execution plan will optimally used it if not then we might see a different exeuction plan. So basically STATISTICS will tell selectivity of your index.

Query execution be from front/back end means Management Studio or Query Analyzer will get executed fine its only that in Management Studio it will show additional information (if estimated/actual execution plan button is selected) like whether appropriate index/stats available on said column/table/index.

HTH

---

Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

Answer 5

Hi Gurusethi,

Thanks. Please correct me if I am wrong, I could able to understand that staists updation will inprove performance of the query. So eventhough the statistics are not updated, it will fetch the datas but only thing is that it will take time to fetch....

 

Regards,

Ranjith kumar

Answer 6

Hi Ranjith,

Yes you can say that. Actual thing boil down to execution plan if there is change in execution plan then yes it will affect performance of the query while fetching results.

 

---

Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

Answer 7

Hi Gursethi,

Thanks.

Answer 8

Hi Ranjit,

It is something like this:

While retrieving the data, the database engine tries to pick up the best plan based on the criteria given in the SELECT statement. While doing so, it checks the indexes defined on the table, and picks up those indexes which helps the data retrieval faster as well as optimal usage of resources like CPU and memory. For DB engine to decide on whether to use specific index or not while retrieving the data, it makes use of statistics. So, if your statistics are not up to date, the DB engine could pick up inefficient plan for data retrieval. 

So to answer your question, if statistics are not up to date, data retrieval becomes slower but data will be retrieved. Hope this clarifies.

Regards,

 

---

Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

Answer 9

Hi,

If statistics are not updated, Will it wont fetch the datas? Because my Query inserts the data usind the Select statement for Eg,

Insert into Tbl_name

select column1 from tablename where condition.

When I check the Execution plan, it shows the warning that the STATISTICS are not available for column (say three columns).

When I checked the table, the datas are not inserted.

Is this caused by Statistics????

<<snip!>>

This is not a statistics problem. Regardsless of the state of your statistics, CRUD operations will work. Performance may vary but if you are seeing CRUD operations fail, something is broken somewhere and statistics has nothing to do with that.

 

---

No great genius has ever existed without some touch of madness. - Aristotle