Before I start I realise I am not going to get an absolute answer here but just looking for some possible areas to look at to help find a resolution to my problem.

A journal table in our system is frequently updated by most objects that exist within the system, and we access this table with a UDF.

Periodically calls to this UDF will timeout due to a bad query plan, and requires us to recompile the UDF. The indexes on the table are refreshed daily.

We are also running SQL 2014, and have experienced this issue from 2005 onwards.

Any suggestions on how I could work out whether this is a poorly created index, or something else?

Sample of the query in the UDF below. There is a multi-column index on Y/Z and column W is a clustered index.

SELECT       TOP 1     @x = ***columnX*** FROM     [dbo].***tablename*** WITH (NOLOCK) WHERE     [J1].***ColumnY*** = @Y AND     [J1].***ColumnZ*** = @Z ORDER BY     [J1].***ColumnW*** DESC