I had at one time asked someone to fix a report that ran in 2 hours. They refused, until later that year when it was taking longer than 26 hours and wouldn't complete in time to refresh the reporting database. But at least they finally listened!

I once fixed a performance problem where the duration of "the problem" was completing in 200ms.

As it turns out, the 26-hour report used a UDF and that UDF was the problem. Not because UDFs are bad, the problem was they were using an inline UDF as a scalar UDF (very bad!). It got fixed and it ran in under an hour.

My speed suggestions:

1. Use SQL Profiler

Build a performance template... include Textdata, reads, cpu, writes, duration, dbid, spid, loginame for the events SQL: Completed, SP:Stmt Completed, RPC:Completed and filter by Reads > 50000. Store the trace information in a table on a dev box. Hint: When you build a query to get the trace info from the table use this for the textdata column as it converts tabs, carriage returns, and whatever 13 (linefeed?) to a space:

replace (replace (replace(convert (varchar (4000), textdata), char(13), ' '), char(10), ' '), char(9), ' ') as Textdata

Fix the performance problems in your trace. Sensory overload? Sort by Reads DESC. Find repeated queries, target those first. They probably show up together. 😉

When those are fixed, reduce the amount of reads. Repeat.

2. Every table gets a clustered index.

The reason SQL Server defaults the clustered index on the PK is because you primarily will join to a table on it's PK, or at least look up info by it. When you use the clustered index, you get lookups (any of the columns) for free. Hence, putting the clustered index on your PK will likely be your best option.

3. Avoid data conversions on a COLUMN

WHERE convert(varchar(12), ModifiedDate) = @TheDateIWant <-- VERY bad = table scan

WHERE ModifiedDate = convert(datetime, @TheDateIWant) <-- Fast = uses index on ModifiedDate

I once supported a Workflow system that put UPPER() around everything. It was a case-insensitive database. Long story, you get the point.