Slow performance filtering on ntext column on large table

I have a table that logs user changes with this definition:

audit_trail
(
   change_id int identity (1, 1) NOT NULL,
   change_date datetime NOT NULL,
   user_id int NOT NULL,
   record_id int NOT NULL,
   table_name nvarchar(50) NOT NULL,
   field_name nvarchar(50) NOT NULL,
   new_value ntext NULL
)

This query runs very slow (15+ minutes) on this table:

SELECT DISTINCT record_id
FROM audit_trail
WHERE table_name = 'jobs'
    AND field_name = 'status'
    A开发者_开发百科ND new_value LIKE '157'

My table has over 70 million records. This is not a usual query for this table. Normal queries on this table sort by date or search for changes in a date range so I have a clustered index on the change_date column. The execution plan for this query shows it doing a clustered index scan. I thought I could improve the performance by adding an nonclustered index on (table_name, field_name) but this index was not even used. Any recommendations on improving this query's performance?

---

try the following:

sp_tableoption N'audit_trail', 'text in row', '1024'

You might also consder full-text search.

---

If you can do that, changing nvarchar into varchar will improve the size, and probably the performance of your table. I also suspect that removing the unnecessary "DISTINCT" will allow the optimizer to use the other indexes.