Just got through a week of load testing. Database servers pegging at 100% CPU utilization. Managed to reduce it to 30%. Did not manage to retain sanity.
Imagine a view so horrible that, from a performance standpoint, the following is necessary to select from it efficiently:
SELECT ProductView.* FROM (
SELECT TOP (@PageSize) ProductID, Rank FROM (
SELECT ProductID, ROW_NUMBER() OVER ( ORDER BY SortNumber, ProductID ) AS Rank FROM Product
WHERE Enabled = 1 AND Quantity > 0
) AS Results
WHERE Rank > ( @PageIndex * @PageSize )
ORDER BY Rank
) AS Results
INNER JOIN ProductView ON ProductView.ProductId = Results.ProductId
ORDER BY Rank
The view had 13 joins. One join in particular was against a view aggregating the results of a sub query depending on values from another view which in turn got its results from aggregating additional data.
Or, quite aptly,
One View to rule them all, One View to find them, One View to bring them all, and in the darkness bind them.