Thank you for a very timely (for me) and most excellent post. I had all the right indexes and FK constraints set-up on my web application database. When I checked, I found 7 (out of 23) FK contraints were not trusted.

Thank you also to Peter for pointing out why I wasn't able to get them trusted (Not for Replication).

Note, if you use the table designer FK relationships dialog box, the property is called "Enforce for Replication" and I had to mark it as "Yes" to get SS to check and start trusting the FK constraints. All the trusted constraints already had this marked as "yes" in the the Table Designer FK relationships dialog box.

Also, I didn't have to drop the FK constraint and re-create it, simply changing the "enforce for replication" to "yes" and telling SS to "check existing data on creation or re-enabling" to yes and save the table was enough for SS to start trusting these constraints.

After doing this for all previously untrusted 7 constraints, the page load times in my web application have gone from an agonising 10 seconds down to 3 seconds!

A tremendous improvement!!

However, there's still room for more improvement in my app, it would be great to see sub 1 second page load times on a fast client network connection. I'm sure the hardware's capable, I just need to find where the bottlenecks are and remove them! Articles like this definitely help!!