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'
