Problem involving Paralelism or Database corruption?

  • Hi,

    I'm experiencing a weird situation:

    I have a "basic" table, like this one:

    CREATE TABLE [dbo].[Transactions] (
     [IDTransaction] [int] IDENTITY (1, 1) NOT NULL ,
     [Company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [ObjectType] [int] NULL ,
     [TransactionType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [NumKeyCol] [int] NULL ,
     [KeyCols] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [KeyColValues] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [CreatedDateTime] [datetime] NULL ,
     [TreatedDateTime] [datetime] NULL ,
     [Note] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [Failed] [bit] NULL 
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[Transactions] WITH NOCHECK ADD 
     CONSTRAINT [PK_Transactions] PRIMARY KEY  CLUSTERED 
     ([IDTransaction]) ON [PRIMARY] 
    GO
     CREATE  INDEX [pk_TreatedDateTime] ON [dbo].[Transactions]([TreatedDateTime]) ON [PRIMARY]
    GO
     CREATE  INDEX [PK_Failed] ON [dbo].[Transactions]([Failed]) ON [PRIMARY]
    GO
    

    When I do a select using only the index, it returns 9 rows, having unique IDTransaction.

    select idtransaction, failed from transactions where failed = 1

    1367036, 1
    1368861, 1
    1386689, 1
    1393734, 1
    1406558, 1
    1407617, 1
    1409823, 1
    1411004, 1
    1412169, 1

    When I add one field to my select Statement, like this:

    select idtransaction, failed, CreatedDateTime from transactions where failed = 1

    each row appears four times, with duplicate idtransactions

    1367036 1 2006-02-10 10:54:28.153
    1368861 1 2006-02-10 14:26:31.250
    1412169 1 2006-02-16 15:51:34.723
    1367036 1 2006-02-10 10:54:28.153
    1368861 1 2006-02-10 14:26:31.250
    1386689 1 2006-02-14 13:39:43.107
    1393734 1 2006-02-15 09:51:51.363
    1406558 1 2006-02-16 11:08:48.600
    1407617 1 2006-02-16 13:40:09.177
    1409823 1 2006-02-16 15:06:12.553
    1411004 1 2006-02-16 15:32:05.190
    1412169 1 2006-02-16 15:51:34.723
    1386689 1 2006-02-14 13:39:43.107
    1393734 1 2006-02-15 09:51:51.363
    1406558 1 2006-02-16 11:08:48.600
    1407617 1 2006-02-16 13:40:09.177
    1409823 1 2006-02-16 15:06:12.553
    1411004 1 2006-02-16 15:32:05.190
    1412169 1 2006-02-16 15:51:34.723
    1367036 1 2006-02-10 10:54:28.153
    1368861 1 2006-02-10 14:26:31.250
    1386689 1 2006-02-14 13:39:43.107
    1393734 1 2006-02-15 09:51:51.363
    1406558 1 2006-02-16 11:08:48.600
    1407617 1 2006-02-16 13:40:09.177
    1409823 1 2006-02-16 15:06:12.553
    1411004 1 2006-02-16 15:32:05.190
    1412169 1 2006-02-16 15:51:34.723
    1367036 1 2006-02-10 10:54:28.153
    1368861 1 2006-02-10 14:26:31.250
    1386689 1 2006-02-14 13:39:43.107
    1393734 1 2006-02-15 09:51:51.363
    1406558 1 2006-02-16 11:08:48.600
    1407617 1 2006-02-16 13:40:09.177
    1409823 1 2006-02-16 15:06:12.553
    1411004 1 2006-02-16 15:32:05.190

    I have more than 1376978 rows in this table.

    I suspect a paralelism problem, or pagefiles corruption... Does anybody has a clue on why this is happening?

    Thanks you,

    Patrick Veilleux, N'Ware Technologies

  • Really weird.

    Try this

    select idtransaction, failed, 'Constant' from transactions where failed = 1

     


    Kindest Regards,

    Amit Lohia

  • It returns 9 rows, no duplicates (10 actualy now since there is one more "failed" record in the table, but you know what I mean)

    I will try to do dome DBCC reindex or DBCC check database this weekend to try to solve the problem...

    Patrick Veilleux

     

  • Doing this query will ignore the indexes:

    select idtransaction, failed, CreatedDateTime from transactions WITH (INDEX(0)) where failed = 1

    and it returns the proper amount of rows.

    So the table seems ok because of this query, and the index seems ok because of the "select failed from transaction where failed = 1"

    It may confirm that the paralelism is in cause...

    Patrick

     

Viewing 4 posts - 1 through 3 (of 3 total)

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