Optimize simple query in SQL Server
It has no WHERE clause so it must process and aggregate all 40 million rows. SQL Server will not take advantage of the index order and skip scan ahead to the next IdTag once it has found the MAX for the current group but will continue processing the other rows in that group. Each group has an average of about 30,000 rows.

As you have another table that lists the 1,386 distinct IdTag types then you could try the following instead.

SELECT D.IdTag,
       V.PCTimeStamp,
       V.Now,
       datediff(SECOND, V.PCTimeStamp, V.Now) AS DELAY
FROM   DescriptionTagsOPC D
       CROSS APPLY (SELECT TOP 1 *,
                                 getdate() AS Now
                    FROM   ValuesTagsOPC V
                    WHERE  D.IdTag = V.IdTag
                    ORDER  BY PCTimeStamp DESC) V 


To replace the scan of 40 million rows with  1,386 seeks.

If that table was not available then a recursive CTE could be used to achieve similar results.

WITH    RecursiveCTE
AS      (
        SELECT TOP 1 IdTag, PCTimeStamp
        FROM ValuesTagsOPC
        ORDER BY IdTag DESC, PCTimeStamp DESC
        UNION   ALL
        SELECT  R.IdTag, R.PCTimeStamp
        FROM    (
                SELECT  V.*,
                        rn = ROW_NUMBER() OVER (ORDER BY V.IdTag DESC, V.PCTimeStamp DESC)
                FROM    ValuesTagsOPC V
                JOIN    RecursiveCTE R
                        ON  V.IdTag &lt; R.IdTag
                ) R
        WHERE   R.rn = 1
        )
SELECT  IdTag,
        PCTimeStamp,
        getdate()                                 AS NOW,
        datediff(SECOND, PCTimeStamp, getdate()) AS DELAY
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);