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'