FineTune TSQL SQL Server

The below query takes a lot of time, like 80 minutes to return 1400 results, any recommendation how to tune this

exec sp_executesql N'set ARITHIGNORE ON;
SELECT TOP (@solutionid) 
        b.[solutionid],MAX(b.title) title,
        MIN(a.[Status]) [Status],
        MIN(a.[Audience]) Audience,
        MAX(b.productname) ProductName, 
        MIN(CAST( DateCreated as datetime)) DateCreated ,
        MIN(CAST( DateLastModified as datetime)) DateModified,
        [pc_pres_text]=REPLACE(REPLACE(REPLACE((SELECT [pc_pres_text] as [data()]
FROM    [dbo.view] a
WHERE   a.[solutionid]=b.[solutionid]
FOR XML PATH('''')), ''&lt;'',''<''),''&gt;'',''>'') ,''&amp;'',''&''), 
        ''http://support/supp=''+b.[solutionid] PrimusUrl
FROM    [view] b
INNER JOIN [table] a ON b.solutionid=a.solutionid
WHERE   ((@Audien开发者_运维知识库ce IS NULL AND [Audience] IS NULL) OR [Audience] LIKE @Audience) AND [DateCreated] > @DateCreated AND 
        ((@Status IS NULL AND [Status] IS NULL) OR [Status] = @Status)
GROUP BY b.[solutionid]
ORDER BY b.[solutionid]',N'@Audience nvarchar(10),@DateCreated datetime,@Status nvarchar(9),@solutionid bigint',
@Audience=N'test%',@DateCreated='2000-01-01 00:00:00',@Status=N'test',@solutionid=500000

If you are passing parameter for @audience and @status then no need to test for a null condition but that is not going to do much to performance. If you do not output to XML do you still have performance problem? Include hint with (nolock) or possibly (holdlock). Not sure you can (holdlock) on a view - you might need to break it down to the tables for (holdlock). Why the cast on DateCreated and DateLastModified - are they text fields in SQL? If they are text and will cast to datetime then why not store them as datetime?

First, BalamBalam recommends using nolock. This can indeed help performance enormously when used properly, but it can cause dirty reads and lead to inconsistent results. In short, nolock can be useful but it should be used with great caution and full understanding of the risks it involves.

Next, I would definitely take a look at your indexes. The missing index feature can give you a good idea of what specifically to look at, but offhand I would certainly make sure all solutionId columns are indexed.

Finally, this has some general performance thoughts that may be useful as you optimize this: http://www.sqlservercentral.com/articles/Performance/71001/