A simple Query causing high CPU in SQL Server 2008 We are noticing below SQL is causing very High CPU and its been executed 10,000 times. Is there a way we can tune this T-sql?

SELECT ISNULL(max(trans_seq),0) +1  
FROM inv_inventory_journal  
WHERE organization_id = @P0  
AND wkstn_id = '600' 


From Execution Plan, it is suggesting to create the following index.

/*
Missing Index Details from ExecutionPlan1.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 98.8039%.
*/

/*
USE [LKY_Xcenter]
GO
CREATE NONCLUSTERED INDEX [&lt;Name of Missing Index, sysname,&gt;]
ON [dbo].[inv_inventory_journal] ([organization_id],[wkstn_id])
INCLUDE ([trans_seq])
GO
*/




Table Structure
Based on your schema it looks like trans_seq can't be null so I would remove the isnull from the select as that would have a small amount of CPU overhead in just checking the value that is returned from the max function. 

SELECT max(trans_seq) +1  
FROM inv_inventory_journal  
WHERE organization_id = @P0  
AND wkstn_id = '600' 


Also if your query is always setting the wkstn_id to a constant 600. You could create a filtered index on that column. 

USE [LKY_Xcenter]
GO
CREATE NONCLUSTERED INDEX [&lt;Name of Missing Index, sysname,&gt;]
ON [dbo].[inv_inventory_journal] ([organization_id],[wkstn_id])
INCLUDE ([trans_seq])
WHERE [trans_seq] = 600;
GO


The nice thing about filtered indexes is that from an IO standpoint they only contain the rows that satisfies the where statement defined in the index so you could get better performance from them.

One thing of note with the filtered indexes is that in order for the query optimizer to use them the value in the where clause must be a constant in the query.  

Here is a couple of references to using filtered indexes:
MSDN

Brent Ozar