How to optimize this Select MAX() SQL query? In the following select query, it take almost 1 min to return the result, seems the table has over 2 millions record and growing. There are no index i can use from the table. How do I optimize this query?

SELECT MAX(StartDate) AS Expr1 
FROM [MYSERVER].MYDB.dbo.ProductActivity AS ProductActivity_1


I already tried with the following, which also takes 1 min to get the result

SELECT TOP(1) *
FROM [MYSERVER].MYDB.dbo.ProductActivity AS ProductActivity_1
ORDER BY StartDate DESC


Is there another way to optimize the query?
As mentioned in the comments, an index on StartDate will let your query seek right to the row it needs, rather than scanning the whole table or another index (which is what it's likely doing right now):

CREATE NONCLUSTERED INDEX IX_StartDate
ON dbo.ProductActivity (StartDate DESC);


Like any other index, this one will need to be maintained anytime a row is inserted into, or deleted from, the ProductActivity table.  And anytime the StartDate column is updated (although that seems unlikely based on the name alone).

This doesn't seem like it would add a lot of overhead, but you should test with your workload to be sure.  If the MAX(StartDate) is needed very often, it's probably worthwhile to keep this index around.  

If there are lots and lots of writes to this table (hundreds or thousands per second), and the MAX(StartDate) isn't needed often, then you might be better off just periodically waiting the 1 minute or so for that query to finish rather than paying the overhead on each write.