Notes on Yavor Angelov, His PDC 2009 Presentation, “Making Microsoft SQL Server 2008 Fly”

Microsoft’s Yavor Angelov PDC 2009 presentation, “Making Microsoft SQL Server 2008 Fly,” brought me to these points:

• The Database Engine Tuning Advisor—it seems to come with SQL Server 2008.
• “The missing indexes feature uses dynamic management objects and Showplan to provide information about missing indexes that could enhance SQL Server query performance.” Missing indexes is on by default.
• “What is ‘parameter sniffing’? When a stored procedure is first executed SQL Server looks at the input parameters and uses this as guidance to build the query plan. This is known as ‘parameter sniffing.’”
• “Parameter use, especially in more complex scenarios, can also cause performance issues. If the queries are complex and/or the data distribution on the columns against which the parameter is compared vary, the cost of different plan choices can change. A plan that is optimal for one parameter value may perform poorly for another value. The query optimizer still needs to estimate the selectivity and cardinality of predicates using a parameter value. This section describes how this process works in more detail.”
• “Static” parameters for stored procedures yield better execution plans than do local variables or mutable parameters.
• “SQL Server offers many hints that can be used to force how an execution plan is put together. The option that we are interested in is the OPTIMIZE FOR option. This will allow us to specify what parameter value we want SQL Server to use when creating the execution plan. This is a SQL Server 2005 hint.”—Greg Robidoux
• “Queries that can benefit from plan guides are generally parameter-based, and may be performing poorly because they use cached query plans whose parameter values do not represent a worst-case or most representative scenario. The OPTIMIZE FOR and RECOMPILE query hints can be used to address this problem. OPTIMIZE FOR instructs SQL Server to use a particular value for a parameter when the query is optimized. RECOMPILE instructs the server to discard a query plan after execution, forcing the query optimizer to recompile a new query plan the next time that the same query is executed.”
• “In the previous example, the value for the @stmt parameter is the parameterized form of the query. The only reliable way to obtain this value for use in sp_create_plan_guide is to use the sp_get_query_template system stored procedure. The following script can be used both to obtain the parameterized query and then create a plan guide on it.”
• “SQL Server 2008 supports both row and page compression for both tables and indexes. … Compression is available only in the SQL Server 2008 Enterprise and Developer editions.”

rasx()