Well I have seen some horror stored procs in my time and this one is particularly nasty. Split the proc in multiples procedures to return 1 dataset each, multiple dataset can destroy the performance. You have business logic in your stored proc (c.title and followupstatus should be reference/dimension tables) You are formatting dates in the database, leave that to the client. This looks like a complete horror - looks like you are storing date & time when you really only need the date and this kludge it trying to deal with it.
WHERE ((CONVERT(NVARCHAR(10), a.follow\_up\_date\_, 121) + ' ' + CONVERT(CHAR(5), CAST(a.follow\_up\_time AS DATETIME), 108)) < (CONVERT(VARCHAR(16), GETDATE(), 121)))
AND (CONVERT(NVARCHAR(MAX), a.follow\_up\_date\_, 101) + ' ' + CONVERT(NVARCHAR(MAX), SUBSTRING(a.follow\_up\_time, 1, 5), 108)) <= (CONVERT(VARCHAR(16), GETDATE(), 120))
Try using datetime data type, BETWEEN may be useful to you. This seems to indicate you are storing your dates as varchar -
ORDER BY CONVERT(DATETIME, follow_up_date) DESC
one of the most basic and expensive errors a database designer can make. I don't think you can blame the database server if this is what you are asking it to deal with. When you have cleaned up the errors you should then use profiler to determine if indexes can improve the performance.
Never underestimate the power of human stupidity RAH
