SQL Query works but it takes too much time to complete

Question

I have this "simple" SQL query and it works but it is taking 11 minutes to complete. Can you tell me if there is a way to simplify the code or something in order to get a better performance and a faster response. Note: The query has been being running in the SQL Management Studio 18.6 in the Server (Windows Server 2019). Thank you in advance.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DISTINCT COUNT(*)
FROM [MyDataBase].[dbo].[ScanJobLog]
WHERE (ScanJobLog.StationID = '207' OR  ScanJobLog.StationID = '208' OR  ScanJobLog.StationID = '218' OR  ScanJobLog.StationID = '219') AND (ScanJobLog.Occurred BETWEEN '2023-03-28' AND '2023-03-29')
AND (ScanJobLog.ScanJobResult = '2')
AND (Scan LIKE 'L001620321%')

Answer 1

To keep this simple, you can create a nonclustered index, reference: (https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-nonclustered-indexes?view=sql-server-ver16).

I can determine the required nonclustered index without the execution plan since you provided your where-clause, and the where-clause in your example identifies what columns to index. You also said in comments you had no other indexes other than the clustered index (which is in all likelihood also your primary key since making the primary key the clustered index is the default SQL Server behavior).

CREATE NONCLUSTERED INDEX ncx_scanJobLog_StationID_Occurred_ScanJobResult_Scan
 ON [dbo].[ScanJobLog] (StationID, Occurred, ScanJobResult, Scan)

A caveat is if any primary key columns were in the where clause- then they can be excluded. There is obviously more to say on the subject of indexes but this is going out of scope of your original question.