I was working on a SQL Server 2005 system yesterday and I had really awful performance on one of my queries. I just couldn’t figure out why it was running slow. I decided to use the new SQL Server 2008 T-SQL Debugger on the code, and sure enough – I found the problem. I had a User-Defined Function (UDF) being referenced in the code, and when I stepped through it, I noticed that it didn’t have the “WITH SCHEMABINDING” option in it. The effect of that is that SQL Server 2005 treats a UDF without that option as non-deterministic, which means it won’t evaluate it properly until later in the cycle.
All that to say two things: use WITH SCHEMABINDING in your UDF’s (more here) and the Debugger is cool!