The optimization process and recommendations:

1. Avoid Calling Functions With Indexed Columns (query line: 80): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `SignalDateTime` is indexed, the index won’t be used as it’s wrapped with the function `dbo.GetFilterStatusOfEquipment`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
2. Avoid Calling Functions With Indexed Columns (query line: 80): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `SpeedOfTheVehicle` is indexed, the index won’t be used as it’s wrapped with the function `dbo.GetFilterStatusOfEquipment`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
3. Avoid Calling Functions With Indexed Columns (query line: 80): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `IgnitionStatus` is indexed, the index won’t be used as it’s wrapped with the function `dbo.GetFilterStatusOfEquipment`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
4. Avoid Correlated Subqueries (query line: 80): A correlated subquery is a subquery that contains a reference (column: DriverID) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
5. Avoid Correlated Subqueries (query line: 96): A correlated subquery is a subquery that contains a reference (column: val) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
6. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `tbl_EquipmentTrack`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.

The optimized query:

SELECT
        et.X,
        et.Y,
        et.EquipID AS ID,
        e.EquipmentName AS Name,
        e.EquipmentNo,
        eg.EquipGroup AS 'Fleet_Name',
        et.SpeedOfTheVehicle AS Speed,
        et.IgnitionStatus,
        convert(varchar,
        et.SignalDateTime) AS SignalDateTime,
        et.DriverID,
        CASE d.DriverName 
            WHEN '' THEN 'Name' 
            ELSE d.DriverName END AS DriverName,
CASE isnull(d.EmployeeNo,
    '') 
    WHEN '' THEN '0' 
    ELSE d.EmployeeNo END AS DriverNo,
CASE isnull(d.LicenseNo,
    '') 
    WHEN '' THEN '0' 
    ELSE d.LicenseNo END AS LicenseNo,
dbo.GetFilterColorStatusOfEquipment(et.SignalDateTime,
et.SpeedOfTheVehicle,
(SELECT
    COUNT(J.JobId) 
FROM
    tbl_Notification J 
INNER JOIN
    tbl_NotificationAssign JN 
        ON JN.NotificationNo = J.NotificationNo 
INNER JOIN
    dbo.tbl_CustomStatus JS 
        ON JS.CustomStatusID = J.CustomStatusID 
INNER JOIN
    dbo.tbl_SystemStatus ss 
        ON ss.SystemStatusID = JS.SystemStatusID 
WHERE
    JN.DriverID = et.DriverID 
    AND ss.SystemStatusID != 9),
et.IgnitionStatus) AS FilterStatus,
e.SerialNo,
e.GPSIMIENO,
'Equipment' TYPE,
etr.Vector,
si.ImgPath AS 'Icon',
et.Address,
etr.EquipTypeID,
si.StatusTypeID,
ss.SystemStatusCode 
FROM
dbo.tbl_Equipment e 
INNER JOIN
dbo.tbl_EquipmentTrack et 
    ON et.EquipID = e.EquipID 
LEFT OUTER JOIN
dbo.tbl_DriverMaster d 
    ON et.DriverID = d.DriverID 
LEFT OUTER JOIN
dbo.tbl_EquipmentGroup eg 
    ON eg.EquipGroupID = e.EquipGroupID 
LEFT OUTER JOIN
dbo.tbl_EquipmentType etr 
    ON etr.EquipTypeID = eg.EquipTypeID FULL 
OUTER JOIN
dbo.tbl_StatusIMG si 
    ON etr.EquipTypeID = si.NotificationType 
    AND si.StatusTypeID = 2 FULL 
OUTER JOIN
dbo.tbl_SystemStatus ss 
    ON ss.SystemStatusID = si.SystemStatusID 
WHERE
et.X IS NOT NULL 
AND et.Y IS NOT NULL 
AND e.EquipmentName IS NOT NULL 
AND e.Available = 'Available' 
AND (
    (
        dbo.GetFilterStatusOfEquipment(et.SignalDateTime, et.SpeedOfTheVehicle, (SELECT
            COUNT(J.JobId) 
        FROM
            tbl_Notification J 
        INNER JOIN
            tbl_NotificationAssign JN 
                ON JN.NotificationNo = J.NotificationNo 
        INNER JOIN
            dbo.tbl_CustomStatus JS 
                ON JS.CustomStatusID = J.CustomStatusID 
        INNER JOIN
            dbo.tbl_SystemStatus ss 
                ON ss.SystemStatusID = JS.SystemStatusID 
        WHERE
            JN.DriverID = et.DriverID 
            AND ss.SystemStatusID != 9), et.IgnitionStatus) IN (
            SELECT
                val 
            FROM
                dbo.split(@filter,
                ',')
        )
    ) 
    OR (
        @filter = '*' 
        OR @filter = ''
    )
)