Results 1 to 4 of 4

Thread: only clusted index used !

  1. #1
    Join Date
    Jun 2008
    Posts
    2

    only clusted index used !

    Hi we have table with 10000000 rows

    it take 40 second to execute unless we use WITH (INDEX option

    I create empty table with all indexes ,still execution plan show that only clusted index scan used...

    We tried to run same statement on 12 different servers with same result

    Why optimiser ignore other indexes ?





    Here is table and query

    SET NUMERIC_ROUNDABORT OFF

    GO

    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON

    GO

    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors

    GO

    CREATE TABLE #tmpErrors (Error int)

    GO

    SET XACT_ABORT ON

    GO

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    GO

    BEGIN TRANSACTION

    GO

    PRINT N'Creating [dbo].[TransactionsPOS]'

    GO

    CREATE TABLE [dbo].[TransactionsPOS]

    (

    [trn_TransactionID] [bigint] NOT NULL,

    [trn_ShiftID] [int] NOT NULL,

    [trn_FinanceTransTypeCode] [int] NOT NULL,

    [trn_RetrievalRefNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_R__4C0144E4] DEFAULT (' '),

    [trn_SequenceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_S__4CF5691D] DEFAULT (' '),

    [trn_TransSettlementStatusCode] [int] NOT NULL,

    [trn_Date] [datetime] NOT NULL,

    [trn_TransTypeCode] [int] NOT NULL,

    [trn_TransInfoCode] [int] NOT NULL,

    [trn_ModalityTypeCode] [int] NOT NULL,

    [trn_PaymentModalityCode] [int] NOT NULL,

    [trn_CardTypeRetailerNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_C__4DE98D56] DEFAULT (' '),

    [trn_CardNoLenght] [int] NOT NULL CONSTRAINT [DF__Transacti__trn_C__4EDDB18F] DEFAULT ((0)),

    [trn_CardNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_C__4FD1D5C8] DEFAULT (' '),

    [trn_CardAdditionalInfo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_C__50C5FA01] DEFAULT (' '),

    [trn_CardExpiryDate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

    [trn_ResponseCode] [int] NOT NULL,

    [trn_Amount] [numeric] (13, 4) NOT NULL CONSTRAINT [DF__Transacti__trn_A__51BA1E3A] DEFAULT ((0)),

    [trn_ApprovalCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_A__52AE4273] DEFAULT (' '),

    [trn_IsAuthorisedOnly] [tinyint] NOT NULL CONSTRAINT [DF__Transacti__trn_I__53A266AC] DEFAULT ((0)),

    [trn_ReversalCode] [int] NOT NULL,

    [trn_InvoiceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_I__54968AE5] DEFAULT (' '),

    [trn_OperatorNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_O__558AAF1E] DEFAULT (' '),

    [trn_OptionalData] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL CONSTRAINT [DF__Transacti__trn_O__567ED357] DEFAULT (' '),

    [trn_IsManuallyEntered] [tinyint] NOT NULL CONSTRAINT [DF__Transacti__trn_I__5772F790] DEFAULT ((0)),

    [trn_LastUpdatedTS] [datetime] NOT NULL CONSTRAINT [DF__Transacti__trn_L__58671BC9] DEFAULT (getdate()),

    [trn_SourceFeedID] [int] NULL

    )

    GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    PRINT N'Creating primary key [XPKTransactionsPOS] on [dbo].[TransactionsPOS]'

    GO

    --ALTER TABLE [dbo].[TransactionsPOS] ADD CONSTRAINT [XPKTransactionsPOS] PRIMARY KEY NONCLUSTERED ([trn_TransactionID])

    ALTER TABLE [dbo].[TransactionsPOS] ADD CONSTRAINT [XPKTransactionsPOS] PRIMARY KEY CLUSTERED ([trn_TransactionID])

    GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    PRINT N'Creating index [IX_TransactionsPOS_ShiftID] on [dbo].[TransactionsPOS]'

    GO

    CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_ShiftID] ON [dbo].[TransactionsPOS] ([trn_ShiftID])

    GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    PRINT N'Creating index [IX_TransactionsPOS_ShiftNo] on [dbo].[TransactionsPOS]'

    GO

    CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_ShiftNo] ON [dbo].[TransactionsPOS] ([trn_ShiftID])

    GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    PRINT N'Creating index [IX_TransactionsPOS_Date] on [dbo].[TransactionsPOS]'

    GO

    CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_Date] ON [dbo].[TransactionsPOS] ([trn_Date] DESC)

    --CREATE CLUSTERED INDEX [IX_TransactionsPOS_Date] ON [dbo].[TransactionsPOS] ([trn_Date] DESC)

    GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    PRINT N'Creating index [IX_TransactionsPOS_TransTypeCode] on [dbo].[TransactionsPOS]'

    GO

    CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_TransTypeCode] ON [dbo].[TransactionsPOS] ([trn_TransTypeCode])

    GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    PRINT N'Creating index [IX_TransactionsPOS_ModalityTypeCode] on [dbo].[TransactionsPOS]'

    GO

    CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_ModalityTypeCode] ON [dbo].[TransactionsPOS] ([trn_ModalityTypeCode])

    GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    PRINT N'Creating index [IX_TransactionsPOS_PaymentModalityCode] on [dbo].[TransactionsPOS]'

    GO

    CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_PaymentModalityCode] ON [dbo].[TransactionsPOS] ([trn_PaymentModalityCode])

    GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    PRINT N'Creating index [IX_TransactionsPOS_ReversalCode] on [dbo].[TransactionsPOS]'

    GO

    CREATE NONCLUSTERED INDEX [IX_TransactionsPOS_ReversalCode] ON [dbo].[TransactionsPOS] ([trn_ReversalCode])

    GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    PRINT N'Creating index [IX_TRansactionsPos_InvoiceNo] on [dbo].[TransactionsPOS]'

    GO

    CREATE NONCLUSTERED INDEX [IX_TRansactionsPos_InvoiceNo] ON [dbo].[TransactionsPOS] ([trn_InvoiceNo])

    GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    PRINT N'Adding foreign keys to [dbo].[Payments]'

    GO

    --ALTER TABLE [dbo].[Payments] WITH NOCHECK ADD

    --CONSTRAINT [FK_pmt_trn] FOREIGN KEY ([pmt_TransactionID]) REFERENCES [dbo].[TransactionsPOS] ([trn_TransactionID]) NOT FOR REPLICATION

    --GO

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

    GO

    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION

    GO

    IF @@TRANCOUNT>0 BEGIN

    PRINT 'The database update succeeded'

    COMMIT TRANSACTION

    END

    ELSE PRINT 'The database update failed'

    GO

    DROP TABLE #tmpErrors

    GO

    -- this query show that execution plan use clusted index scan, qurry run for 40 seconds

    SELECT TOP 1000

    dbo.TransactionsPOS.*

    FROM

    dbo.TransactionsPOS

    --WITH (INDEX(IX_TransactionsPOS_Date))

    WHERE

    trn_Date >= '2008-03-01'

    AND

    trn_Date <= '2008-03-11'







    -- this query show that execution plan use X_TransactionsPOS_Date index seek, qurry run for 3 seconds

    SELECT TOP 1000

    dbo.TransactionsPOS.*

    FROM

    dbo.TransactionsPOS

    --WITH (INDEX(IX_TransactionsPOS_Date))

    WHERE

    trn_Date >= '2008-03-01'

    AND

    trn_Date <= '2008-03-11'

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Try order by trn_date.

  3. #3
    Join Date
    Jun 2008
    Posts
    2

    Red face

    order by trn_Date works
    when
    with
    SELECT TOP 1000
    and
    up to
    SELECT TOP 22500

    if I run
    SELECT TOP 22501
    or
    SELECT *
    it still scan


    but if range is small (1day)
    query use correct index

    trn_Date >= '2008-03-01'
    AND
    trn_Date <= '2008-03-02'

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    index is not useful for large result set, so optimizer decides to use clustered index scan.

    you can see total logical io by setting

    set statistics_io on

    --run query with and without hint

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •