SQL Performance, Execution plan showing mostly Index Scan instead of Index Seek

Question

I am running below query, which is returning mostly 25 records. But it is taking almost 20 seconds. Also the total number of records in the table is less than 400,000.

SELECT * FROM Tickets 
LEFT OUTER JOIN HouseAccounts ON (Tickets.lHouseAccount_ID = HouseAccounts.lAccountID) 
LEFT OUTER JOIN Customers ON (Tickets.lCustomerID = Customers.lCustomerID) 
LEFT OUTER JOIN Vehicles ON (Tickets.lVehicleID = Vehicles.lVehicleID) 
WHERE (Tickets.sTicket_Number) NOT LIKE 'ADJ%' AND dbo.DateOnly(Tickets.dtCreated) between DATEADD(day, -60, dbo.DateOnly(GETDATE())) 
and dbo.DateOnly(GETDATE()) AND (Tickets.bDeleted = 0 or Tickets.bDeleted IS NULL)

Below is the Tickets table structure

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Tickets](
 [Ticket_ID] [int] IDENTITY(1,1) NOT NULL,
 [lLocationID] [int] NULL,
 [dtCreated] [datetime] NULL,
 [dtUpdated] [datetime] NULL,
 [dtIn] [datetime] NULL,
 [dtOut] [datetime] NULL,
 [sTicket_Number] [nvarchar](10) NULL,
 [dblTotal] [float] NULL,
 [dblTaxes] [float] NULL,
 [dblTendered] [float] NULL,
 [dblChangeDue] [float] NULL,
 [bPaid] [smallint] NULL,
 [bCash] [smallint] NULL,
 [bCreditCard] [smallint] NULL,
 [bGiftCard] [smallint] NULL,
 [bHouseAccount] [smallint] NULL,
 [lHouseAccount_ID] [int] NULL,
 [sUserName] [nvarchar](25) NULL,
 [lUserID] [int] NULL,
 [lShiftNumber] [int] NULL,
 [imgSignature] [image] NULL,
 [sSignatureFileName] [nvarchar](25) NULL,
 [sPlate] [nvarchar](10) NULL,
 [sMake] [nvarchar](20) NULL,
 [sCarNumber] [nvarchar](25) NULL,
 [sDriverName] [nvarchar](64) NULL,
 [sZipcode] [nvarchar](5) NULL,
 [sAge] [nvarchar](10) NULL,
 [sGender] [nvarchar](10) NULL,
 [sFleetCard] [nvarchar](25) NULL,
 [sFleetCardExp] [nvarchar](8) NULL,
 [bCheck] [smallint] NULL,
 [lVIPAccountID] [int] NULL,
 [lPointsThisVisit] [float] NULL,
 [lGreeterID] [int] NULL,
 [lCustomerID] [int] NULL,
 [lVehicleID] [int] NULL,
 [lWorkOrderID] [int] NULL,
 [sWorkOrderNumber] [nvarchar](8) NULL,
 [sVehicleMake] [nvarchar](20) NULL,
 [sVehicleColor] [nvarchar](20) NULL,
 [sVehicleState] [nvarchar](2) NULL,
 [sVehiclePlate] [nvarchar](9) NULL,
 [sVehicleDamage] [nvarchar](100) NULL,
 [sCustomerName] [nvarchar](25) NULL,
 [dtReturnDate] [datetime] NULL,
 [lOdometer] [int] NULL,
 [sRoomNumber] [nvarchar](6) NULL,
 [sSpaceNumber] [nvarchar](50) NULL,
 [bExpressTicket] [smallint] NULL,
 [lRateStructureId] [int] NULL,
 [sRateStructure] [nvarchar](25) NULL,
 [mRate] [money] NULL,
 [mSurcharge] [money] NULL,
 [mValidation] [money] NULL,
 [mPrepaid] [money] NULL,
 [mRefund] [money] NULL,
 [mMisc] [money] NULL,
 [bVoided] [smallint] NULL,
 [bCheckedOut] [smallint] NULL,
 [bClosedOut] [smallint] NULL,
 [bRefunded] [smallint] NULL,
 [lParkerId] [int] NULL,
 [bUpdated] [smallint] NULL,
 [bIndoor] [smallint] NULL,
 [iTimesPrinted] [smallint] NULL,
 [bAudit] [bit] NULL,
 [bArchived] [bit] NULL,
 [lCounterId] [int] NULL,
 [bPaymentOther] [bit] NULL,
 [sPaymentDescription] [nvarchar](50) NULL,
 [bScanned] [bit] NULL,
 [bPrinted] [bit] NULL,
 [bReversed] [bit] NULL,
 [sCashierTerminal] [nvarchar](50) NULL,
 [sGreeterTerminal] [nvarchar](50) NULL,
 [bLocked] [bit] NULL,
 [bWash] [bit] NULL,
 [bDeleted] [bit] NULL,
 [sDeletedBy] [nvarchar](125) NULL,
 [dtClosed] [datetime] NULL,
 [lCloserId] [int] NULL,
 [lCloserId2] [int] NULL,
 [bBarcodeScanned] [bit] NULL,
 CONSTRAINT [aaaaaTickets_PK] PRIMARY KEY NONCLUSTERED 
(
 [Ticket_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 65) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Tickets] ADD  CONSTRAINT [DF__Tickets__dblTota__014935CB]  DEFAULT ((0)) FOR [dblTotal]
GO

ALTER TABLE [dbo].[Tickets] ADD  CONSTRAINT [DF__Tickets__bPaid__023D5A04]  DEFAULT ((0)) FOR [bPaid]
GO

ALTER TABLE [dbo].[Tickets] ADD  CONSTRAINT [DF__Tickets__bCash__03317E3D]  DEFAULT ((0)) FOR [bCash]
GO

ALTER TABLE [dbo].[Tickets] ADD  CONSTRAINT [DF__Tickets__bCredit__0425A276]  DEFAULT ((0)) FOR [bCreditCard]
GO

ALTER TABLE [dbo].[Tickets] ADD  CONSTRAINT [DF__Tickets__bGiftCa__0519C6AF]  DEFAULT ((0)) FOR [bGiftCard]
GO

And, here is the index and execution plan-

I have already tried with Update Statistics and updating indexes. However, nothing helped. Please suggest, how can I improve the performance of the query.

Execution Plan.sqlplan

Execution Plan.xml

Indexes.xlsx

Answer 1

You should avoid scalar and multistatement UDFs if possible, because they are slow. And You should definitely avoid them in conditions, because they are not sargable. Replacing dbo.DateOnly( with Convert(date, should help.

The thing I am curious about - are there tickets from the future? And if there are, You really want to skip them? Chances are, You can replace that between with simple Tickets.dtCreated >= convert(date, DATEADD(day, -60, GETDATE())).