Manually Created statistics on a table on a column but it is seen nothing

Question

Manually Created statistics on a table on a column but it is seen nothing

Thinky Night

SSCommitted

Points: 1663
More actions
August 1, 2012 at 5:40 am

#392867

Hi All,
I created some statistics on a table on column. But when i examine statistics detail, it is not created and updated.
"No statistics is available" message is seen.
The other problem is,
Automatically created statistics was not updated regularly. Although the table is update everyday, last statistics date is last month. Is there a reson for this message?
My DB belong to a BI Application.
Regards,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQL Show SSCertifiable Points: 6143 More actions · Answer 1

Even though 'Auto Create Statistics' is ON, You need to run a query to 'acutally' enabled.Optimizer will not create a statistics until any query fire against the table.

SQL Show SSCertifiable Points: 6143 More actions · Answer 2

"Automatically created statistics was not updated regularly. Although the table is update everyday, last statistics date is last month. Is there a reson for this message?"

1. Have you enabled 'auto update stats'= ON?

2. how many rows are there in that table ? How many updates are running against the table.?

Optimizer will employ some algorithm to update the statistics..

http://blog.sqlauthority.com/2010/04/21/sql-server-when-are-statistics-updated-what-triggers-statistics-to-update/

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Can you post a small script that reproduces the problem (e.g. create table, insert test data, create the statistics and then the statistics are not created)?

About the statistics that did not get updated - Don't forget that the statistics are marked as should be updated after 500 rows + 20% of the number of rows in the table were modified. If number of rows that were inserted/deleted/updated t did not reach this number, then the statistics should not be updated.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQL Show SSCertifiable Points: 6143 More actions · Answer 4

Sure,

USE [STATS_DB]

GO

Select DATABASEPROPERTY('stats_db', 'IsAutoCreateStatistics')

GO

Create Table Stats_Table ( a Int)

GO

select OBJECT_NAME (OBJECT_ID) ObjectName, * from sys.stats where [OBJECT_ID] = OBJECT_ID('Stats_Table')

If @@ROWCOUNT = 0

Print 'No Stats Available'

Else

Print 'Stats Available'

GO

Select * From [STATS_DB]..Stats_Table where a like 'a%'

GO