Ms SQL 2 k SP4 is avaialable!

ArticleDirectory

• 5.1.17 optimize queries with a large in list or many or clauses

Ms SQL 2 k SP4 is avaialable!

Http://www.microsoft.com/downloads/details.aspx? Familyid = 8e2dfc8d-c20e-4446-99a9-b7f0127f8bc5 & displaylang = ZH-CN

Http://download.microsoft.com/download/9/ B /f/9bff6646-2cdb-4069-ada0-548be9cb9338/ReadmeSql2k32sp4.htm#_additional_information_about_sp4

I don't know how it works ?! Is it obvious? :

5.1.17 optimize queries with a large in list or many or clauses

Introduced in SP4

SP4 contains changes to the SQL Server optimizer behavior, which affects queries that contain predicates with large in lists or many or clauses. More specifically, this change (in SQL Server 2000 hot fix)Program789 introduced) affects queries that contain the following content (or queries that can be rewritten using the corresponding expression containing the following content ):

• The in list contains over 10,000 elements.

• Two in lists. Each list contains more than 100 elements.
• The or clause contains more than 10,000 separation items.
• Or clause and in list combination, so that the corresponding expression contains more than 10,000 separation items

due to this change, SQL server uses less memory when compiling these types of statements, thus avoiding memory insufficiency errors. In rare cases, if the system that runs such a query has a large amount of memory but has a low degree of parallelism, The Optimizer may choose a query plan with poor performance. To override changes to the optimizer behavior, this service pack provides the trace flag 9060. By default, trace flag 9060 is disabled. If this trace flag is enabled, the SP3 behavior before hotfix 789 is enabled. If error 701 (insufficient system memory) is encountered when this trace flag is enabled, you should use the temporary table or table variable in the in list to overwrite the query. For the numeric range, use the between clause or the greater than (>) or less than (<) operator. For more information about how to use trace flags, see "trace flags" in SQL Server books online ".