x

Information in index system views with auto update statistics turned off

We know that the system views hold good information about indexes. But if the system isn't set to automatically update statistics, can we trust the information in there, such as the number of rows in an index, etc?

PS: If you're reading this, then I encourage you to come with a good seeder question and post it too! Without good questions, the site will never catch on as a useful resource.

more ▼

asked Oct 21, 2009 at 10:56 PM in Default

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

I wanted to make a tag for statistics, but didn't have enough points... Thanks for retagging.
Oct 22, 2009 at 08:55 AM Rob Farley
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If the system is not updating the statistics automatically and you're not running a manual process to get the statistics updated, then no, you can't trust them. What's more, you can't trust the execution plans that running against your system to return your data because those execution plans are completely dependent on the stats being up to date and accurate.

more ▼

answered Oct 22, 2009 at 08:05 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

Your queries should still work, just not so likely to use the ideal plan. Once the plan is decided upon, the system will still go to the data, even though it doesn't know how that data is distributed.
Oct 22, 2009 at 08:53 AM Rob Farley
Right. The queries will return data, and the correct data too. But the queries will in all likelihood perform very badly because the execution plans will be so out of wack due to the lack of statistics.
Oct 22, 2009 at 08:49 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x346
x131
x34
x27
x10

asked: Oct 21, 2009 at 10:56 PM

Seen: 1527 times

Last Updated: Apr 15, 2010 at 05:36 AM