Index don't use !

Question

Post reply

Index don't use !

farax_x

Hall of Fame

Points: 3579
More actions
July 25, 2015 at 1:49 am

#319960

I have table like this :
CREATE TABLE [dbo].[tblSMS_SentTest](
[UserId] [int] NOT NULL,
[SMSId] [bigint] NOT NULL,
[DoerId] [bigint] NOT NULL ,
[IsBulk] [bit] NOT NULL ,
[Orginator] [varchar](15) NOT NULL,
[Recipient] [bigint] NULL,
[RecipientName] [nvarchar](50) NULL,
[RecipientCount] [int] NOT NULL ,
[Message] [nvarchar](640) NOT NULL,
[MessageLength] [tinyint] NOT NULL ,
[SentCount] [int] NOT NULL,
[SentDate] [datetime] NULL,
[CreateDate] [datetime] NOT NULL ,
[LastUpdateDate] [datetime] NULL,
[StatusId] [smallint] NOT NULL ,
[Flag] [tinyint] NOT NULL ,
[IsDeleted] [bit] NOT NULL ,
[DeliveredId] [bigint] NULL,
[DeliveredDate] [smalldatetime] NULL,
[DeliveredCount] [int] NULL,
[Properties] [xml] NULL,
[IsWebService] [bit] NOT NULL ,
[IsArchived] [bit] NOT NULL ,
[RetryNo] [tinyint] NOT NULL ,
[RecipientType] [tinyint] NULL,
[RecipientProvince] [smallint] NULL,
[isDeleteOk] [bit] NULL,
[MessageCost] [smallint] NULL,
CONSTRAINT [PK_tblSqweMSSent] PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[SMSId] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [sent_PK]
)
indexes:
USE [CGArchive]
GO
/****** Object: Index [IX_tblSMS_Sent_Conversation] Script Date: 7/25/2015 12:11:06 PM ******/
CREATE NONCLUSTERED INDEX [IX_tblSMS_Sent_Conversation] ON [dbo].[tblSMS_SentTest]
(
[UserId] ASC,
[Recipient] ASC,
[StatusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [sent_INDEX]
GO
/****** Object: Index [IX_tblSMS_Sent_CreateDate] Script Date: 7/25/2015 12:11:06 PM ******/
CREATE NONCLUSTERED INDEX [IX_tblSMS_Sent_CreateDate] ON [dbo].[tblSMS_SentTest]
(
[CreateDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [sent_INDEX]
GO
/****** Object: Index [IX_tblSMS_Sent_DeliveredId] Script Date: 7/25/2015 12:11:06 PM ******/
CREATE NONCLUSTERED INDEX [IX_tblSMS_Sent_DeliveredId] ON [dbo].[tblSMS_SentTest]
(
[DeliveredId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [sent_INDEX]
GO
/****** Object: Index [IX_tblSMS_Sent_Recipient] Script Date: 7/25/2015 12:11:06 PM ******/
CREATE NONCLUSTERED INDEX [IX_tblSMS_Sent_Recipient] ON [dbo].[tblSMS_SentTest]
(
[Recipient] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [sent_INDEX]
GO
/****** Object: Index [IX_tblSMS_Sent_SentDate] Script Date: 7/25/2015 12:11:06 PM ******/
CREATE NONCLUSTERED INDEX [IX_tblSMS_Sent_SentDate] ON [dbo].[tblSMS_SentTest]
(
[SentDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [sent_INDEX]
GO
/****** Object: Index [IX_tblSMS_Sent_SMSId] Script Date: 7/25/2015 12:11:06 PM ******/
CREATE NONCLUSTERED INDEX [IX_tblSMS_Sent_SMSId] ON [dbo].[tblSMS_SentTest]
(
[SMSId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [sent_INDEX]
GO
/****** Object: Index [IX_tblSMS_Sent_UR] Script Date: 7/25/2015 12:11:06 PM ******/
CREATE NONCLUSTERED INDEX [IX_tblSMS_Sent_UR] ON [dbo].[tblSMS_SentTest]
(
[UserId] ASC,
[Recipient] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [sent_INDEX]
GO
create sample data :
SET NOCOUNT ON;
DECLARE @i INT =0
WHILE (@i <100000)
BEGIN
INSERT INTO [dbo].[tblSMS_SentTest](UserId, SMSId, DoerId, IsBulk, Orginator, Recipient, RecipientName, RecipientCount, Message, MessageLength, SentCount, SentDate, CreateDate, LastUpdateDate, StatusId, Flag, IsDeleted, DeliveredId, DeliveredDate, DeliveredCount, Properties, IsWebService, IsArchived, RetryNo, RecipientType, RecipientProvince, isDeleteOk, MessageCost)
VALUES(@i, 1, 1, 1, 1, 1, '', 1, 'TEST', 1, 1, GETDATE(), GETDATE(), GETDATE(), 1, 1, 1, 1, GETDATE(), 1, NULL, 1, 1, 1, 1, 1, 1, 1)
SET @i +=1
END
GO
UPDATE STATISTICS dbo.tblSMS_SentTest
and my question is :
I have query like this :
DECLARE @EndDate DATETIME=dateadd(day,-30,getdate()),
@Offset INT = 100,
@Count INT=10
SELECT s.SMSId
FROM dbo.tblSMS_SentTest s WITH( NOLOCK)
WHERE
s.SentDate<@EndDate
ORDER BY s.SMSId OFFSET @offset ROWS FETCH NEXT @Count ROWS ONLY
It doesnt use IX_tblSMS_Sent_SentDate
what is the best index for this query ?

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

That index doesn't contain SMSId. You could change an existing index for this query:

CREATE NONCLUSTERED INDEX [IX_tblSMS_Sent_SMSId] ON dbo.tblSMS_SentTest

([SMSId] ASC) INCLUDE (SentDate)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden