Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with statistics Expand / Collapse
Author
Message
Posted Wednesday, February 16, 2011 9:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 7:44 AM
Points: 93, Visits: 299
Hi

I have noticed that looking at the execution plan of one of our sql queries there is a little yellow triangle telling me that some of the columns do not have statistics.
I updated the statistics containing the columns that were being complained about and yet the yellow traingle warning sign still continues to appear
Do I need to do something else?

Thanks
Matt
Post #1065094
Posted Wednesday, February 16, 2011 10:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
The warning triangle is saying there are statistics missing. Update stats only updates existing stats, it won't add new ones.

Is auto_create_stats on? What's the object that the warning is on?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1065111
Posted Wednesday, February 16, 2011 10:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 7:44 AM
Points: 93, Visits: 299
GilaMonster (2/16/2011)
The warning triangle is saying there are statistics missing. Update stats only updates existing stats, it won't add new ones.


If I expand the statistics node on the table that has the problem columns there is a statistic in there that contains the colums that are supposedly missing. I clicked on the 'update statistics for these colums' checkbox hoping that it would clear the error.




GilaMonster (2/16/2011)


Is auto_create_stats on?


Not sure, how would I check that?


GilaMonster (2/16/2011)


What's the object that the warning is on?


It appears to be an index that is on the table

Post #1065117
Posted Wednesday, February 16, 2011 10:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 7:44 AM
Points: 93, Visits: 299
I just checked the database properties and it looks like auto create stats is set to false
Post #1065123
Posted Wednesday, February 16, 2011 11:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 7:44 AM
Points: 93, Visits: 299
I just used this: -


USE <database name>;
GO
ALTER DATABASE <database name>
SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE <database name>
SET AUTO_UPDATE_STATISTICS ON;
GO




and it seems to fix the problem as the warning sign is gone now
Post #1065162
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse