select top 100 query very slow one table

[Anthony Altieri]

Here is the table in question.
Number of records 5512825
not a big table and plenty of good indexs i think? that is the big
questions.
Could some one help?
After the table creation and index you will see a query that takes
almost 2.3 minutes to run this is totally bad. That time is through
the query analyzer.
Any help here would be greatly apperciated.
CREATE TABLE [dbo].[tblpolmaster] (
[IDX] [int] IDENTITY (1, 1) NOT NULL ,
[AG_NO] [int] NOT NULL ,
[PR_NO] [int] NOT NULL ,
[PLAN_NO] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUB_PLAN_NOS] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[FIN_NO] [int] NOT NULL ,
[ACCT_NO] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DRAFT_NO] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_LNAME] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_FNAME] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_ADDRESS] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_CITY] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_STATE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_ZIP] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_PLUS4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_PHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_FAX] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_MEMBER] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_NUM] [char] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USER_ID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PR_COUNT] [int] NOT NULL ,
[TRANS_DT] [smalldatetime] NULL ,
[PR_CERT] [int] NOT NULL ,
[EDIT_DT] [smalldatetime] NULL ,
[POL_NUM_MEMBER] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPLAN_NO] ON
[dbo].[tblpolmaster]([PLAN_NO]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPOL_NAME] ON
[dbo].[tblpolmaster]([POL_LNAME], [POL_FNAME]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPOL_STATE] ON
[dbo].[tblpolmaster]([POL_STATE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPOL_NUM] ON
[dbo].[tblpolmaster]([POL_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterAG_NO] ON [dbo].[tblpolmaster]([AG_NO])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPR_NO] ON [dbo].[tblpolmaster]([PR_NO])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPOL_ADDRESS] ON
[dbo].[tblpolmaster]([POL_ADDRESS]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPOL_PHONE] ON
[dbo].[tblpolmaster]([POL_PHONE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPR_COUNT] ON
[dbo].[tblpolmaster]([PR_COUNT]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterFIN_NO] ON
[dbo].[tblpolmaster]([FIN_NO]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterUSER_ID] ON
[dbo].[tblpolmaster]([USER_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterTRANS_DT] ON
[dbo].[tblpolmaster]([TRANS_DT]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPOL_MEMBER] ON
[dbo].[tblpolmaster]([POL_MEMBER]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterDRAFT_NO] ON
[dbo].[tblpolmaster]([DRAFT_NO]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterEdited] ON
[dbo].[tblpolmaster]([EDIT_DT]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPOL_NUM_MEMBER] ON
[dbo].[tblpolmaster]([POL_NUM_MEMBER]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPOL_FNAME] ON
[dbo].[tblpolmaster]([POL_FNAME]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPOL_LNAME] ON
[dbo].[tblpolmaster]([POL_LNAME]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblpolmasterPOL_CITY] ON
[dbo].[tblpolmaster]([POL_CITY]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

Select Top 100 tblpolmaster.Idx,
tblpolmaster.Ag_No, tblpolmaster.Pr_No, tblpolmaster.Pol_LName,
tblpolmaster.Pol_FName,
tblpolmaster.pol_Address, tblpolmaster.Pol_City,
tblpolmaster.Pol_State, tblpolmaster.POL_MEMBER, tblpolmaster.Pr_Count
FROM tblpolmaster WITH (NOLOCK)
Where
tblpolmaster.PR_NO = 514
And tblpolmaster.POL_STATE = 'FL'