Validating database query results

You can click the This page button in the Feedback section at the bottom of this page.

validating database query results-88

Applies to: Foreign platform (such as Oracle, DB2, My SQL and Sybase) to SQL Server migration.

SQL Server compiles query plans on stored procedures by using sniffing the input parameters at the first compile, generating a parameterized and reusable plan, optimized for that input data distribution.

Even if indexes are added to the base table(s), this is not going to help.

For MSTVFs, SQL Server uses a fixed estimation of 1 for the number of rows expected to be returned by an MSTVF (starting with SQL Server 2014 (12.x) that fixed estimation is 100 rows).

Even if not stored procedures, most statements generating trivial plans will be parameterized.

After a plan is first cached, any future execution maps to a previously cached plan.

For more information on this topic, see Parameter Sniffing.

Applies to: Foreign platform (such as Oracle, DB2, My SQL and Sybase) and SQL Server to SQL Server migration.

Below are some of the common performance scenarios encountered after migrating to SQL Server Platform and how to resolve them.

Tags: , ,