optimize blocking query

Hello,

I try to optimize this query :
SELECT TOP 1 evt_message.evt_message_id,
             evt_message.evt_id,
             evt_message.evt_key,
             evt_message.whse,
             evt_message.validate_key,
             evt_message.nbr_of_retry,
             evt_message.stat_code,
             evt_message.error_seq_nbr,
             evt_message.cl_message_id,
             evt_message.create_date_time,
             evt_message.mod_date_time,
             evt_message.user_id,
             evt_message.schema_id,
             evt_message.els_actvty_code,
             evt_message.cd_master_id
FROM   evt_message WITH (updlock, rowlock)
WHERE  ( ( ( evt_message.stat_code >= @P1 )
           AND ( evt_message.stat_code <= @P2 ) )
         AND ( evt_message.nbr_of_retry < @P3 ) )
ORDER  BY evt_message.evt_message_id ASC
OPTION ( fast 1 )  

The structure table is

USE [SADRFWMSPRD]
GO

/****** Object:  Table [dbo].[evt_MESSAGE]    Script Date: 12/19/2013 11:25:00 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[evt_MESSAGE](
      [evt_MESSAGE_ID] [int] NOT NULL,
      [evt_ID] [int] NULL,
      [evt_KEY] [varchar](100) NULL,
      [WHSE] [varchar](3) NULL,
      [VALIDATE_KEY] [varchar](100) NULL,
      [NBR_OF_RETRY] [int] NOT NULL,
      [STAT_CODE] [int] NOT NULL,
      [ERROR_SEQ_NBR] [int] NOT NULL,
      [CL_MESSAGE_ID] [int] NULL,
      [CREATE_DATE_TIME] [datetime] NULL,
      [MOD_DATE_TIME] [datetime] NULL,
      [USER_ID] [varchar](15) NULL,
      [SCHEMA_ID] [varchar](3) NULL,
      [ELS_ACTVTY_CODE] [varchar](15) NULL,
      [CD_MASTER_ID] [int] NULL,
 CONSTRAINT [PK_evt_MESSAGE] PRIMARY KEY CLUSTERED
(
      [evt_MESSAGE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 80) ON [PKMSDATA]
) ON [PKMSDATA]

GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[evt_MESSAGE] ADD  CONSTRAINT [DF_evt_MESSAGE_NBR_OF_RETRY]  DEFAULT ((0)) FOR [NBR_OF_RETRY]
GO

ALTER TABLE [dbo].[evt_MESSAGE] ADD  CONSTRAINT [DF_evt_MESSAGE_STAT_CODE]  DEFAULT ((0)) FOR [STAT_CODE]
GO

ALTER TABLE [dbo].[evt_MESSAGE] ADD  CONSTRAINT [DF_evt_MESSAGE_ERROR_SEQ_NBR]  DEFAULT ((0)) FOR [ERROR_SEQ_NBR]
GO

ALTER TABLE [dbo].[evt_MESSAGE]  WITH NOCHECK ADD  CONSTRAINT [FK_evt_MESSAGE_TO_CD_MASTER] FOREIGN KEY([CD_MASTER_ID])
REFERENCES [dbo].[CD_MASTER] ([CD_MASTER_ID])
GO

ALTER TABLE [dbo].[evt_MESSAGE] NOCHECK CONSTRAINT [FK_evt_MESSAGE_TO_CD_MASTER]
GO


@P1 = 20, @P2 = 22, @P3 = 3

Thanks

Regards
bibi92Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
If that's typically how you query the table, you should change the clustered index to:
( stat_code, nbr_of_retry, evt_MESSAGE_ID ) --esp. if you make "nbr_of_retry" smallint instead of int
--or ( stat_code, evt_MESSAGE_ID ), although that may be less efficient if you have many different "nbr_of_retry" values per stat_code range

Once you get the table properly clustered, that query will ZIP.


Here are the T-SQL commands to do that:

ALTER TABLE dbo.evt_message  DROP CONSTRAINT PK_evt_MESSAGE

CREATE UNIQUE CLUSTERED INDEX CL_evt_message ON dbo.evt_message ( stat_code, nbr_of_retry, evt_MESSAGE_ID ) WITH ( FILLFACTOR = 95 ) ON [PKMSDATA]

--If you really need a separate index on MESSAGE_ID alone,
ALTER TABLE dbo.evt_message  ADD CONSTRAINT PK_evt_MESSAGE PRIMARY KEY NONCLUSTERED ( evt_MESSAGE_ID ) WITH ( FILLFACTOR = 95 ) ON [PKMSDATA]
0
 
magarityCommented:
That looks pretty good actually with your clustered index on the order by field.

What is the rowcount and the distribution of values in evt_message.stat_code and evt_message.nbr_of_retry ?  A non-clustered index on one or both of those MIGHT help but not if there are only a few choices relative to how many rows in the table.

You could convert this:
 ( evt_message.stat_code >= @P1 )
           AND ( evt_message.stat_code <= @P2 )
to:
 evt_message.stat_code BETWEEN @P1 and @P2.
0
 
JimFiveCommented:
Using a subquery will get rid of the sort required by TOP
Select <fields>
FROM   evt_message evt1 WITH (updlock, rowlock)
WHERE evt1.evt_message_id = (SELECT Min(evt2.evt_message_id)
                                                    FROM evt_message evt2
                                                   WHERE ( ( ( evt2.evt_message.stat_code >= @P1 )
                                                   AND ( evt2.evt_message.stat_code <= @P2 ) )
                                                   AND ( evt2.evt_message.nbr_of_retry < @P3 ) ))
OPTION ( fast 1 )   

Open in new window

0
 
magarityCommented:
I also think you should remove "OPTION ( fast 1 )" because your query specifies only one row with the TOP 1 already.  Using the fast option causes the query plan to be considered differently and this may be conflicting with your actual requirement of only getting one row in the first place.  Use the fast option for example when there are a lot of rows to retrieve and a lot of processing by the calling application so it can get started on the first rows asap.  In this case, there is only one row anyway.
Before you re-index anything, please answer my questions above re: how many codes and dates typically per ID value.  Depending on this answer different indexing will either help a lot or actually hurt.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.