Not using index
-
I have a query that is doing a table scan even though there is an index on the date field in question. Why wouldn't it be using the index?
If I put the hint there, it works, but under what conditions do you need to use the hint?
Thanks
Susan
-
No need for hints (99.999% of the time).
If you do something like this the index will NOT be used :
where DATEDIFF(D, DateCol, GetDate()) < 15
This version would use an index :
where DateCol >= DATEADD (D, -15, GetDate()) AND DateCol < GetDate().
Can you post your query so we can have a look at it?
-
It's happening at the end of the query..
DECLARE @DateFr datetime
DECLARE @DateTo datetime
--SET @DateFr = '5/11/2006 12:00:00AM'
--SET @DateTo = '5/11/2006 11:59:59PM'
SET @DateFr = '10/12/2006 12:00:00AM'
SET @DateTo = '10/12/2006 11:59:59PM'
SELECT '<XMLData>
<Decisions>
<Decision char="N" desc="Not Verified" />
<Decision char="R" desc="Rejected" />
<Decision char="V" desc="Verified" />
</Decisions>'
SELECT
TargusInfo.RCID,
TargusInfo.First_Name,
TargusInfo.Last_Name,
CAST(TargusInfo.Phone_Number AS VARCHAR) + (
CASE WHEN TargusInfo.Phone_Ext IS NULL THEN ''
ELSE ' x' + CAST(TargusInfo.Phone_Ext AS VARCHAR)
END) AS Phone_Number,
TargusInfo.Lead_Type,
TargusInfo.DecisionChar,
CASE WHEN LQ.validate_reason IS NOT NULL THEN PS.label
ELSE TargusInfo.[Reason]
END AS Reason,
CASE ISNULL(LQ.AdminUserID, 0)
WHEN 0 THEN 'Targus'
ELSE AU.first_name + ' ' + AU.last_name
END AS DecisionBy,
TargusInfo.Date_IN,
TargusInfo.Affiliate_ID
FROM (
SELECT
RCRM.RCID,
LRQ.First_Name,
LRQ.Last_Name,
(
CAST(LRQ.phone_area AS VARCHAR) + '-' +
CAST(LRQ.phone_prefix AS VARCHAR) + '-' +
CAST(LRQ.phone_suffix AS VARCHAR)
) AS Phone_Number,
NULLIF(LRQ.phone_ext, '') AS Phone_Ext,
CASE LRQ.Lead_Type_ID
WHEN 1 THEN 'BUY'
WHEN 2 THEN 'SELL'
ELSE 'BUYSELL'
END AS Lead_Type,
LRQ.DecisionChar,
TTT.Test_Description AS Reason,
LRQ.Date_IN,
LRQ.Affiliate_ID
FROM
dbo.tbl_Leads_Result_Queue LRQ WITH (INDEX(date_in) NOLOCK)
inner join dbo.tbl_Targus_Test_Description TTT (NOLOCK) on LRQ.ruleNum = TTT.RuleNO
inner join dbo.tbl_RCResultMatch RCRM (NOLOCK) on RCRM.ResultID = LRQ.Lead_ID
WHERE 1=1 --LRQ.ruleNum = TTT.RuleNO
--AND RCRM.ResultID = LRQ.Lead_ID
AND (LRQ.Date_IN BETWEEN @DateFr AND @DateTo /*'4/21/2006 12:00:00AM' AND '4/21/2006 12:00:00PM' */)
) TargusInfo INNER JOIN rcexec.tbl_leads_queue LQ (NOLOCK) ON LQ.parent_lead_id = TargusInfo.RCID
LEFT OUTER JOIN dbo.tbl_admin_users AU (NOLOCK) ON AU.admin_user_id = LQ.AdminUserID
LEFT OUTER JOIN dbo.tbl_label_repository PS (NOLOCK) ON PS.label_id = LQ.validate_reason
WHERE --LQ.parent_lead_id = LQ.lead_id AND
(LQ.Date_IN BETWEEN @DateFr AND @DateTo /*'4/21/2006 12:00:00AM' AND '4/21/2006 12:00:00PM' */)
Order by RCID
for xml raw
-
Can I get the ddl to recreate the index?
-
CREATE TABLE [dbo].[tbl_RCResultMatch] (
[RCID] [bigint] NULL ,
[ResultID] [bigint] NULL ,
[DecisionChar] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AlternatePhone] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_in] [datetime] NULL ,
[Acceptance_Failure_Description] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_admin_users] (
[admin_user_id] [int] IDENTITY (1, 1) NOT NULL ,
[group_id] [int] NOT NULL ,
[user_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_created] [datetime] NOT NULL ,
[date_updated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_label_repository] (
[label_id] [int] IDENTITY (1, 1) NOT NULL ,
[label] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[label_type_id] [int] NOT NULL ,
[delete_flag] [int] NOT NULL ,
[label_order_id] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [rcexec].[tbl_leads_queue] (
[lead_id] [int] IDENTITY (1, 1) NOT NULL ,
[parent_lead_id] [int] NULL ,
[partner_id] [int] NOT NULL ,
[RealtorCompanyID] [bigint] NOT NULL ,
[RealtorPrimaryContactUserName] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RealtorPrimaryContactFirstName] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RealtorPrimaryContactLastName] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RealtorCompanyName] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RealtorTotalOffices] [int] NOT NULL ,
[RealtorTotalAgents] [int] NOT NULL ,
[RealtorBrandLogoURL] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RealtorMoreInfoURL] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RealtorWeekendCoverage] [bit] NOT NULL ,
[RealtorCustomerServiceRating] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DesiredMonthlyPayment] [float] NOT NULL ,
[realtor_id] [bigint] NULL ,
[lead_type_id] [int] NULL ,
[lead_status_id] [int] NULL ,
[buy_property_type_id] [int] NULL ,
[sell_property_type_id] [int] NULL ,
[time_frame_id] [int] NULL ,
[best_time_id] [int] NULL ,
[matched_on] [varchar] (767) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_area] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_suffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_ext] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_area2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_prefix2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_suffix2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_zip_1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_city_state_1] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_zip_2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_city_state_2] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_zip_3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_city_state_3] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_price_range_start] [money] NULL ,
[buy_price_range_end] [money] NULL ,
[buy_square_footage] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_bedroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_bathroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_detail_list] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_price_desired] [money] NULL ,
[sell_price_qualifying] [money] NULL ,
[sell_square_footage] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_bedroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_bathroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_detail_list] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[additional_info] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lead_fee] [money] NULL ,
[no_charge] [int] NULL ,
[credited] [int] NULL ,
[lead_problem] [int] NULL ,
[validate_reason] [int] NULL ,
[date_in] [datetime] NULL ,
[date_sent] [datetime] NULL ,
[TrafficLogID] [bigint] NULL ,
[notify_offers] [bit] NULL ,
[credit_history] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[has_agent] [bit] NOT NULL ,
[found_home] [bit] NOT NULL ,
[cell_area] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cell_prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cell_suffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[why_selling] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[is_buysell] [bit] NOT NULL ,
[Affiliate_ID] [bigint] NOT NULL ,
[num_sold] [tinyint] NULL ,
[for_RNDist] [bit] NOT NULL ,
[ExternalAdID] [bigint] NULL ,
[AdminUserID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_leads_result_queue] (
[lead_id] [int] NOT NULL ,
[parent_lead_id] [int] NULL ,
[partner_id] [int] NOT NULL ,
[DecisionChar] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DecisionStr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DecisionFlag] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AlternatePhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RuleNum] [int] NULL ,
[ProcessStatus] [int] NULL ,
[IsEmail] [bit] NULL ,
[ErrorCode] [int] NULL ,
[ErrorDescription] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DesiredMonthlyPayment] [float] NOT NULL ,
[realtor_id] [bigint] NULL ,
[lead_type_id] [int] NULL ,
[lead_status_id] [int] NULL ,
[buy_property_type_id] [int] NULL ,
[sell_property_type_id] [int] NULL ,
[time_frame_id] [int] NULL ,
[best_time_id] [int] NULL ,
[matched_on] [varchar] (767) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_area] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_suffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_ext] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_area2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_prefix2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_suffix2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_zip_1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_city_state_1] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_zip_2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_city_state_2] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_zip_3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_city_state_3] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_price_range_start] [money] NULL ,
[buy_price_range_end] [money] NULL ,
[buy_square_footage] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_bedroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_bathroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_detail_list] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_price_desired] [money] NULL ,
[sell_price_qualifying] [money] NULL ,
[sell_square_footage] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_bedroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_bathroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_detail_list] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[additional_info] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_in] [datetime] NULL ,
[date_sent] [datetime] NULL ,
[TrafficLogID] [bigint] NULL ,
[notify_offers] [bit] NULL ,
[credit_history] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[has_agent] [bit] NULL ,
[found_home] [bit] NULL ,
[cell_area] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cell_prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cell_suffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[why_selling] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[is_buysell] [bit] NULL ,
[Affiliate_ID] [bigint] NULL ,
[ExternalAdID] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_targus_test_description] (
[RuleNO] [int] IDENTITY (1, 1) NOT NULL ,
[Test_Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rationale] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Positions] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_admin_users] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_admin_users] PRIMARY KEY CLUSTERED
(
[admin_user_id]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[tbl_label_repository] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_projects] PRIMARY KEY CLUSTERED
(
[label_id]
 
ON [PRIMARY]GO
ALTER TABLE [rcexec].[tbl_leads_queue] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_leads_queue] PRIMARY KEY CLUSTERED
(
[lead_id]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[tbl_targus_test_description] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_targus_test_description] PRIMARY KEY CLUSTERED
(
[RuleNO]
 
ON [PRIMARY]GO
CREATE INDEX [IX_RCID] ON [dbo].[tbl_RCResultMatch]([RCID]) ON [PRIMARY]
GO
CREATE INDEX [IX_ResultID] ON [dbo].[tbl_RCResultMatch]([ResultID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_admin_users] ADD
CONSTRAINT [DF_tbl_admin_users_group_id] DEFAULT (5) FOR [group_id],
CONSTRAINT [DF_tbl_admin_users_date_created] DEFAULT (getdate()) FOR [date_created],
CONSTRAINT [DF_tbl_admin_users_date_updated] DEFAULT (getdate()) FOR [date_updated]
GO
ALTER TABLE [dbo].[tbl_label_repository] ADD
CONSTRAINT [DF_tbl_label_repository_delete_flag] DEFAULT (0) FOR [delete_flag]
GO
CREATE INDEX [ix_label_type] ON [dbo].[tbl_label_repository]([label_type_id]) ON [PRIMARY]
GO
ALTER TABLE [rcexec].[tbl_leads_queue] ADD
CONSTRAINT [DF_tbl_leads_queue_parent_lead_id] DEFAULT (0) FOR [parent_lead_id],
CONSTRAINT [DF_tbl_leads_queue_partner_id] DEFAULT (0) FOR [partner_id],
CONSTRAINT [DF_tbl_leads_queue_RealtorCompanyID] DEFAULT (0) FOR [RealtorCompanyID],
CONSTRAINT [DF_tbl_leads_queue_RealtorPrimaryContactUserName] DEFAULT ('') FOR [RealtorPrimaryContactUserName],
CONSTRAINT [DF_tbl_leads_queue_RealtorPrimaryContactFirstName] DEFAULT ('') FOR [RealtorPrimaryContactFirstName],
CONSTRAINT [DF_tbl_leads_queue_RealtorPrimaryContactLastName] DEFAULT ('') FOR [RealtorPrimaryContactLastName],
CONSTRAINT [DF_tbl_leads_queue_RealtorCompanyName] DEFAULT ('') FOR [RealtorCompanyName],
CONSTRAINT [DF_tbl_leads_queue_RealtorTotalOffices] DEFAULT (0) FOR [RealtorTotalOffices],
CONSTRAINT [DF_tbl_leads_queue_RealtorTotalAgents] DEFAULT (0) FOR [RealtorTotalAgents],
CONSTRAINT [DF_tbl_leads_queue_RealtorBrandLogoURL] DEFAULT ('') FOR [RealtorBrandLogoURL],
CONSTRAINT [DF_tbl_leads_queue_RealtorMoreInfoURL] DEFAULT ('') FOR [RealtorMoreInfoURL],
CONSTRAINT [DF_tbl_leads_queue_RealtorWeekendCoverage] DEFAULT (0) FOR [RealtorWeekendCoverage],
CONSTRAINT [DF_tbl_leads_queue_RealtorCustomerServiceRating] DEFAULT ('') FOR [RealtorCustomerServiceRating],
CONSTRAINT [DF_tbl_leads_queue_DesiredMonthlyPayment] DEFAULT (0) FOR [DesiredMonthlyPayment],
CONSTRAINT [DF_tbl_leads_queue_realtor_id] DEFAULT (0) FOR [realtor_id],
CONSTRAINT [DF_tbl_leads_queue_lead_type_id] DEFAULT (0) FOR [lead_type_id],
CONSTRAINT [DF_tbl_leads_queue_lead_status_id] DEFAULT (0) FOR [lead_status_id],
CONSTRAINT [DF_tbl_leads_queue_buy_property_type_id] DEFAULT (0) FOR [buy_property_type_id],
CONSTRAINT [DF_tbl_leads_queue_sell_property_type_id] DEFAULT (0) FOR [sell_property_type_id],
CONSTRAINT [DF_tbl_leads_queue_time_frame_id] DEFAULT (0) FOR [time_frame_id],
CONSTRAINT [DF_tbl_leads_queue_best_time_id] DEFAULT (0) FOR [best_time_id],
CONSTRAINT [DF_tbl_leads_queue_lead_fee] DEFAULT (0) FOR [lead_fee],
CONSTRAINT [DF_tbl_leads_queue_no_charge] DEFAULT (0) FOR [no_charge],
CONSTRAINT [DF_tbl_leads_queue_credited] DEFAULT (0) FOR [credited],
CONSTRAINT [DF_tbl_leads_queue_lead_problem] DEFAULT (0) FOR [lead_problem],
CONSTRAINT [DF_tbl_leads_queue_date_in] DEFAULT (getdate()) FOR [date_in],
CONSTRAINT [DF_tbl_leads_queue_TrafficLogID] DEFAULT (0) FOR [TrafficLogID],
CONSTRAINT [DF_tbl_leads_queue_notify_offers] DEFAULT (1) FOR [notify_offers],
CONSTRAINT [DF_tbl_leads_queue_has_agent] DEFAULT (0) FOR [has_agent],
CONSTRAINT [DF_tbl_leads_queue_found_home] DEFAULT (0) FOR [found_home],
CONSTRAINT [DF_tbl_leads_queue_cell_area] DEFAULT ('') FOR [cell_area],
CONSTRAINT [DF_tbl_leads_queue_is_buysell] DEFAULT (0) FOR [is_buysell],
CONSTRAINT [DF_tbl_leads_queue_Affiliate_ID] DEFAULT (0) FOR [Affiliate_ID],
CONSTRAINT [DF_tbl_leads_queue_for_RNDist] DEFAULT (1) FOR [for_RNDist]
GO
CREATE INDEX [IX_tbl_leads_queue] ON [rcexec].[tbl_leads_queue]([parent_lead_id]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbl_leads_queue_1] ON [rcexec].[tbl_leads_queue]([realtor_id]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbl_leads_queue_2] ON [rcexec].[tbl_leads_queue]([lead_status_id]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbl_leads_queue_3] ON [rcexec].[tbl_leads_queue]([credited]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbl_leads_queue_4] ON [rcexec].[tbl_leads_queue]([no_charge]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbl_leads_queue_5] ON [rcexec].[tbl_leads_queue]([date_sent]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbl_leads_queue_6] ON [rcexec].[tbl_leads_queue]([date_in]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbl_leads_queue_7] ON [rcexec].[tbl_leads_queue]([partner_id]) ON [PRIMARY]
GO
CREATE INDEX [ix_AffiliateID] ON [rcexec].[tbl_leads_queue]([Affiliate_ID]) ON [PRIMARY]
GO
CREATE INDEX [ix_zip] ON [rcexec].[tbl_leads_queue]([zip]) ON [PRIMARY]
GO
CREATE INDEX [tbl_leads_queue14] ON [rcexec].[tbl_leads_queue]([credited], [lead_id], [realtor_id], [lead_status_id], [date_in]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_leads_result_queue] ADD
CONSTRAINT [DF_tbl_leads_result_queue_parent_lead_id] DEFAULT (0) FOR [parent_lead_id],
CONSTRAINT [DF_tbl_leads_result_queue_partner_id] DEFAULT (0) FOR [partner_id],
CONSTRAINT [DF_tbl_leads_result_queue_AlternatePhone] DEFAULT ('') FOR [AlternatePhone],
CONSTRAINT [DF_tbl_leads_result_queue_RuleNum] DEFAULT (0) FOR [RuleNum],
CONSTRAINT [DF_tbl_leads_result_queue_IsProcessed_1] DEFAULT (0) FOR [ProcessStatus],
CONSTRAINT [DF_tbl_leads_result_queue_IsEmail] DEFAULT (0) FOR [IsEmail],
CONSTRAINT [DF_tbl_leads_result_queue_ErrorCode] DEFAULT (0) FOR [ErrorCode],
CONSTRAINT [DF_tbl_leads_result_queue_DesiredMonthlyPayment] DEFAULT (0) FOR [DesiredMonthlyPayment],
CONSTRAINT [DF_tbl_leads_result_queue_realtor_id] DEFAULT (0) FOR [realtor_id],
CONSTRAINT [DF_tbl_leads_result_queue_lead_type_id] DEFAULT (0) FOR [lead_type_id],
CONSTRAINT [DF_tbl_leads_result_queue_lead_status_id] DEFAULT (0) FOR [lead_status_id],
CONSTRAINT [DF_tbl_leads_result_queue_buy_property_type_id] DEFAULT (0) FOR [buy_property_type_id],
CONSTRAINT [DF_tbl_leads_result_queue_sell_property_type_id] DEFAULT (0) FOR [sell_property_type_id],
CONSTRAINT [DF_tbl_leads_result_queue_time_frame_id] DEFAULT (0) FOR [time_frame_id],
CONSTRAINT [DF_tbl_leads_result_queue_best_time_id] DEFAULT (0) FOR [best_time_id],
CONSTRAINT [DF_tbl_leads_result_queue_TrafficLogID] DEFAULT (0) FOR [TrafficLogID],
CONSTRAINT [DF_tbl_leads_result_queue_notify_offers] DEFAULT (1) FOR [notify_offers],
CONSTRAINT [DF_tbl_leads_result_queue_has_agent] DEFAULT (0) FOR [has_agent],
CONSTRAINT [DF_tbl_leads_result_queue_found_home] DEFAULT (0) FOR [found_home],
CONSTRAINT [DF_tbl_leads_result_queue_cell_area] DEFAULT ('') FOR [cell_area],
CONSTRAINT [DF_tbl_leads_result_queue_is_buysell] DEFAULT (0) FOR [is_buysell],
CONSTRAINT [DF_tbl_leads_result_queue_Affiliate_ID] DEFAULT (0) FOR [Affiliate_ID]
GO
CREATE INDEX [IX_LeadID] ON [dbo].[tbl_leads_result_queue]([lead_id]) ON [PRIMARY]
GO
CREATE INDEX [IX_ProcessStatus] ON [dbo].[tbl_leads_result_queue]([ProcessStatus]) ON [PRIMARY]
GO
CREATE INDEX [IX_DateIn] ON [dbo].[tbl_leads_result_queue]([date_in]) ON [PRIMARY]
GO
CREATE INDEX [IX_RuleNum] ON [dbo].[tbl_leads_result_queue]([RuleNum]) ON [PRIMARY]
GO
-
Is it used if you query only from its own table?
If so try adding joins one table at the time and see when it stops being used.
Also dates are usually good candidates for clustered indexes (no idea if it's the best solution for that problem and the rest of the system).
-
There are a number of reasons SQL make the decesion not to use an index. Sometimes the query optimizer determines that a bookmark lookup is more costly than a clustered index/table scan.
You can play with hints and see what the performance is like (look at I/O stats) but be aware that when you use an index hint the query optimizer will *always* use the index, even when there are much more efficent plans avaiable.
I have a feeling a lot of your indexes aren't being used.
SQL guy and Houston Magician
-
The reason it's not using the index is that you are looking for a range. Because it is not a clustered index (physically stored in order), the only way it can find every value is to fully scan the column and find every record that matches the range.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Clustered index is decidedly better on such column (but there can be only one clustered index and several candidate columns for it)... but I don't think a range should cause TABLE scan. Index scan, yes - but not table scan.
Maybe the index is in bad shape (fragmented) and so the optimizer decided it will be better not to use it? Do you perform regular index maintenance in your database? Does the table have a clustered index at all (on any column)? /*EDIT : just found the primary key in DDL, so this should be OK*/
Another thing is, how selective is the query? What percentage of the total number of rows do you estimate is in the date interval?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply