There is SQL query that joins multiples sql views and totalling around 15 Inner Join/Outer Joins. The Query actually does a count(*) on the result. The query itself takes 3 minutes to run which is fine. However, it forces the database server to 99%. The configuration is : SQL server 2008 enterprise ed on Windows server 2008 enterprise ed with 4 quad core processors and 32 GB RAM. The tables are partitioned and have around 60 million records. The indexes are set and the Database tuning wizard/Execution plan all seems to be fine.
Since it forces to 99%, all the server access is blocked and the rest of the regular business applications timeout - that have queries running on this server and throw errors.