ASP.NET/SQL 2008 Performance issue

Database bottlenecks can frequently be improved by improving your SQL queries. Without knowing what those look like, consider creating an operational data store or a data warehouse that you populate on a scheduled basis.

Sometimes flattening out your complex relational databases is the way to go. It can make queries run significantly faster, and make it a lot easier to optimize your queries, since the model is very flat. That may also make it easier to determine if you need to scale your database server up or out. A capacity and growth analysis may help to make that call.

Transactional/highly normalized databases are not usually as scalable as an ODS or data warehouse.

Edit: Your ORM may have optimizations as well that it may support, that may be worth looking into, rather than just looking into how to optimize the queries that it's sending to your database. Perhaps bypassing your ORM altogether for the reports could be one way to have full control over your queries in order to gain better performance.

1. determine which ad-hoc queries will most likely be run or limit the search criteria with stored procedures.. can you summarize data?.. treat this
   app like a data warehouse.
2. create indexes on each column involved in the search to avoid table scans.
3. create fragments on expressions.
4. periodically reorg the data and update statistics as more leads are loaded.
5. put the temporary files created by queries (result sets) in ramdisk.
6. consider migrating to a high-performance RDBMS engine like Informix OnLine.
7. Initiate another thread to start displaying N rows from the result set while the query
   continues to execute.

Consider how your ORM is creating the queries. If you're having poor search performance perhaps you could try using stored procedures to return your results and, if necessary, multiple stored procedures specifically tailored to which search criteria are in use.