SQL Server 2008 R2 - optimization issue

I'm running into a performance issue with the current schema. So I built an equivalent schema to solve the issue.

I ran some tests on both schemas and the results are hard to understand. For the record, the data is the same.

I get the following from the Profiler when executing equivalent requests on the two schemas.

Old schema:

• 1,300,000 reads
• 5,000 CPU
• 4 seconds execution time

New schema:

• 30,000 read开发者_StackOverflow中文版s
• 3,000 CPU
• 6 seconds execution time

The difference seems to be in the query plan used. The old schema has parallelism in the query plan. The new schema isn't using parallelism.

1. Has anyone faced similar situations (less IO/CPU but more execution time). How did you solve it?

2. Is there a way to force parallelism? I've played with query hints(http://msdn.microsoft.com/en-us/library/ms18171). I'm able to stop parallelism on the old schema but can't seem the query on the new schema to use parallelism.

Thanks in advance.

---

Louis,

Currently there is no way to force parallelism in SQL Server straight out of the box but Adam Machanic did some work to do that though.

http://whoisactive.com

Coming to your first question, yes we have seen cases like that too. Note that Parallelism is cpu bound and that's why you are seeing more cpu time but overall less execution time as you have multiple threads doing the work for you.

http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/

Make sure you have proper indexes in place and also stats are updated with full scan. In the long run it is best if Query Optimizer makes the decisions by itself but if you want to overwrite the QO plans then you may have to add lot more details. Schema, data and repro.

HTH