November 29, 2010 at 10:52 am
I am told by Microsoft that there is no way to find corrupt statistics (different from bad statistics) and rebuilding the indexes is the only solution since it re-creates the stats. But i am more curious to find when my stats are getting corrupted. Any ideas on how to find it?Thanks
November 29, 2010 at 10:54 am
How did you first notice that some stats are corrupt?
November 29, 2010 at 11:16 am
Yeah, I haven't seen corrupt stats, so I'm curious how they evidence as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2010 at 11:21 am
Grant Fritchey (11/29/2010)
Yeah, I haven't seen corrupt stats, so I'm curious how they evidence as well.
My best guess at the moment is checkdb finding an error in a stat. Other than that I'm pretty much blanking out. I never had Sql Server shoot me an error because it couldn't read the stats.
November 29, 2010 at 11:30 am
I've seen corrupt stats on SQL 2000, but that's because on SQL 2000 the stats were stored in an image column in sysindexes. Never seen on 2005+. I'd start by asking whoever told you there's no way to fix corrupt stats what they meant.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2010 at 11:34 am
GilaMonster (11/29/2010)
I've seen corrupt stats on SQL 2000, but that's because on SQL 2000 the stats were stored in an image column in sysindexes. Never seen on 2005+. I'd start by asking whoever told you there's no way to fix corrupt stats what they meant.
Maybe I misread but the OP says that he's searching for a solution that does not require index rebuild... which is the only option I know ATM.
November 29, 2010 at 11:41 am
Ninja's_RGR'us (11/29/2010)
GilaMonster (11/29/2010)
I've seen corrupt stats on SQL 2000, but that's because on SQL 2000 the stats were stored in an image column in sysindexes. Never seen on 2005+. I'd start by asking whoever told you there's no way to fix corrupt stats what they meant.Maybe I misread but the OP says that he's searching for a solution that does not require index rebuild... which is the only option I know ATM.
Index rebuild may not do it. The SQL 2000 solution (which didn't even work all the time) was to drop the index/statistic and recreate. If it's a column statistic (not associated with an index) then a rebuild won't touch it.
Honestly, I'd start by asking the person from Microsoft who initially said a rebuild would be required and get more details out of them as to what they meant by 'corrupt statistics'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2010 at 1:38 pm
Thanks everyone. Mentioned below are few more details on the issue and the solution.
i) We started noticing sql dumps under the log folder with these messages "A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. ".We identified the proc causing the issue and if we re-ran the proc only from the management studio i get the same error and my connection gets disconnected.
ii) So out of fluke we rebuild the index on that one table where is issue was and query worked fine.
iii) Ran DBCC checkdb , result came out error free without any message or warning.Microsoft has also confirmed that DBCC checkdb does not identify corrupt stats.
iv) Microsoft analysed the sql dumps and said some of your processes from the front end application has corrupted the statistics (btw this is a sql server 2005 standard edition with SP3) and that's why you see an "assertion" error. MSFT said currently there is no way to detect corrupt statistics.
Hope this answer helps.
November 29, 2010 at 2:26 pm
Index rebuilds can easily cause corruption (normal corrupt pages) to disappear as the process of rebuilding deallocates the damaged page. I'm going to consult an expert on corruption as to the stats.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2010 at 2:43 pm
GilaMonster (11/29/2010)
Index rebuilds can easily cause corruption (normal corrupt pages) to disappear as the process of rebuilding deallocates the damaged page. I'm going to consult an expert on corruption as to the stats.
Thanks. You mean you would consult an expert on this?:-)
November 29, 2010 at 2:54 pm
Chiming in at Gail's request...
There's no way to detect corrupt statistics. The statistics blobs are not checked by DBCC CHECKDB as the query optimizer team has never provided a mechanism for doing so. It's unfortunate, but that's the way it is.
The rebuild fixed the issue as it rebuilds all index column statistics with the equivalent of a full scan.
What processes did MS say had corrupted the statistics? Statistics don't get corrupted unless there's a bug in SQL Server - they may get out-of-date, or skewed, but not corrupted. Can you post the wording from the SR where they say stats got corrupted?
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 29, 2010 at 3:34 pm
Thanks Paul. Mentioned below is the statement from them after analysing the sql dumsps
"On further analysis we found that we were trying to get the Average length of a set of index column prefixes from the Histogram and we failed for the same. This clearly
indicates that the Statistics for the Index on this table has got corrupted due to which we were not able to get the Average length of a set of index column prefixes. Since you went ahead and
re-built the indexes a new set of statistics and histogram have got created and that has resolved the issue.
"
November 29, 2010 at 5:40 pm
Ah - so it doesn't say "some of your processes from the front end application has corrupted the statistics" like you said. I'm surprised they didn't speculate on how the corruption occured or give you any advice, but then again front-line support has gotten a lot worse over the last 5 years or so. The corruption would likely have been an IO subsystem issue. Do you have page checksums turned on?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 30, 2010 at 9:04 am
Paul, they haven't mentioned in the email but they did say verbally that some of the process from the front end has corrupted the stats. We have seen a similar issue on different servers but we do not know how to recreate the issue from front end. What info would i get by turning on page checksums and how do i do it?
November 30, 2010 at 9:55 am
ALTER DATABASE <dbname> SET PAGE_VERIFY CHECKSUM
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply