Considering Parallel plans and forcing plans for better performance

Parameterizing T-SQL queries are a well-known task such as database programming and best practice in some tasks. It allows query plan reuse and eliminates the need of recompilation for multiple invocations of the same query that simply has different parameter values. However, there are times when parameterized queries might perform poorly, because they use cached query plans that are optimized for some non-representative set of parameter values.  

Forcing query execution plans, to a limited extent, was possible in earlier versions of SQL Server by using various query hints, join hints, and index hints. However, the process was often based on trial and error and was also tedious. SQL Server 2005 introduces a new query hint, named USE PLAN, that guides the optimizer in creating a query plan based upon a specified XML query plan.

SQL Server uses a complex, cost-based query optimization mechanism that considers numerous factors before formulating a query execution plan. After being compiled, query plans are cached by the SQL Server engine to avoid having to repeat the same task when the identical query is re-executed. Query plans are optimized for the specific data present in the underlying tables. Because of this, the SQL Server engine constantly monitors changes to the underlying tables and triggers a recompile of the query plan when it estimates that the data has changed significantly enough to justify a re-optimization.

Following are extracted from Books Online and few references from technet articles too, that I felt very useful to refer: 

SQL Server generates optimal query plans for most queries. However, there are times, especially with more complex applications, when certain queries benefit from user intervention and some level of hand tuning. Following are typical examples of this:

 So what you need to consider for the consistency and force a particular query to execute in a non-parallel query plan, think about multi-processor servers with 4 or more. Obviously SQL Server determines at run time whether a query should be executed through a parallel or non-parallel query plan, based on pre-determined criteria. Most of the times (in my experience) this works well in speeding up the execution using parallelization, but not always good to go.

I have been going through a technet article and found this interesting feature in SQL Server. There is an un-documented start-up switch: “-P”. What this switch does is that it initializes a particular number of User Mode Schedulers (UMS). There are a couple of ways that you can use this switch - via the registry, via the Start-up parameters screen for the instance properties.

Eventually I have gone through the Books ONline for startup options of starting a SQL Server instance. During the startup of a SQL Server instance, SQL Server allocates one UMS per logical processor - logical because if it is a single processor but is hyper-threaded, that amounts to two logical processors. The number of UMSs that are running for a given instance is what then sets the maximum degree of parallelism for a given instance of SQL Server. For instance to simulate similar scenario using a quad processor box on your development server, try using -P4 as a startup option on that SQL instance. By taking the settings into affect the SQL engine will allocate 4 UMSs (threads) to the instance and will consider those for the parallel query execution plans. Even I found that this is only affective in using with Enterprise or Developer editions of SQL Server on both 2000 & 2005 versions. Further references on the web for Parallel Execution plans - http://www.sql-server-performance.com/articles/per/parallel_execution_plans_p1.aspx and http://www.sql-server-performance.com/articles/per/parallel_execution_plans_p2.aspx articles.

Also I would like to highlight the KBA reference that is posted on this site - http://sqlserver-qa.net/blogs/kba/archive/2007/06/11/705.aspx that refers:

Rewriting a query by using JOIN hints, query (OPTION clause) hints, and index hints is one way to get an improved plan for it to be produced automatically by SQL Server. You can then successfully force this plan on the original query in many cases. For more information about how to apply this technique, see the SQL Server 2005 Books Online topic, “Plan Forcing Scenario: Create a Plan Guide to Force a Plan Obtained from a Rewritten Query.”