In regards nchar and nvarchar, its purely down to the 3rd party design for this.

There's a lot of this throughout their table designs, however they have very little willingness to want to make their application better. Frustrating to say the least.

I've even raised issues regarding bringing back TOP 99999 back to user front end. Makes zero sense to bring back so much data.

On a further note of the problem query, I have today noticed that the query causing the problem seems to hang for what I believed to be a specific parameter 'A4'. What I've since found following further SQL traces is that at some point during the query being repeatedly processed, all parameter values passed into the stored procedure start to show larger reads against the table at some point. It just happens 'A4' value reads 67K rows. Most other parameter values return under 1K rows. Usually no 'heap' appears in the execution plan, however, it appears a 'heap' does start to appear around the build up of the problem.

If the 'A4' value runs as expected it shows as follows in a SQL trace:

query executed with A4 value, 780 CPU, 3780 Reads,1 Writes

However, this morning I started to see the same query produce following output in a SQL trace file.....

10:42

A4 executed with, 84366 CPU, 4906036 Reads, 110014 Writes

10:45

A4 executed with, 80091 CPU, 5284791 Reads, 110007 Writes

10:46

A4 executed with, 97812 CPU, 6179117 Reads, 110012 Writes

I'm running further analysis to see what happens in the database leading up to the problem.

Every time statistics update on the 'agldimvalue' table is executed, it resolves the problem.

regards

Paul