Post reply

statistics bug fix - turn it off and back on again...

  • September 16, 2011 at 2:40 am

    #257391

    We have an enterprise edition SQL 2005 64-bit server (build 9.0.5057) that I have been aksed to look at because some queries are performing badly. I found an issue with how the statistics are estimated. The script below creates a table with ~8 million records to illustrate the issue.

    SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON

    GO

    USE tempdb

    GO

    CREATE TABLE [dbo].[Table1](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [col1] [char](1) COLLATE Latin1_General_CI_AS NOT NULL,

    CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED

    ( [ID] ASC))

    GO

    INSERT INTO [dbo].[Table1]([col1])

    VALUES ('A')

    GO

    INSERT INTO [dbo].[Table1]([col1])

    SELECT [col1]

    FROM [dbo].[Table1]

    GO 23

    DBCC SHOW_STATISTICS ('dbo.Table1','PK_Table1') WITH HISTOGRAM

    UPDATE STATISTICS dbo.Table1 WITH FULLSCAN

    DBCC SHOW_STATISTICS ('dbo.Table1','PK_Table1') WITH HISTOGRAM

    The estimated and actual query plans for the 2 queries below should show ~8341050 rows will be returned. Our server was showing the correct value for the first query but only 1 as the "estimated number of rows" in the second query. I have not been able to reproduce this on another enterprise server (build 9.0.5000) or a developer edition server (build 9.0.5057). With trial and error I found the tipping point was the maximum value for a smallint

    SELECT *

    FROM [dbo].[Table1]

    WHERE ID > 32767

    SELECT *

    FROM [dbo].[Table1]

    WHERE ID > 32768

    This morning the server was patched (windows updates only) and rebooted and the issue has been fixed. It turns out that turning computers off and back on again does work!

    I have searched everywhere for this bug but not found anything. Has anyone else encountered this problem?

    hardware details: IBM 3850 M2, Windows 2008 R2 standard 64-bit. 28GB RAM, 2 x E7440 2.4Ghz xeon

    SQL details: distributor, Litespeed 5.2 installed, sp_configure changes (max server memory=20480, max degree of parallelism=4), tempdb = 1x25GB data and 1x2GB log file.

  • September 16, 2011 at 3:49 am

    #1382442

    Not running into this perhaps?

    http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/

    It can happen for any ascending column, not just dates.

    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

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

Viewing 2 posts - 1 through 1 (of 1 total)

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