SQL ????? 2005 ?? SQL Server 2008 ??? ???? ????? ??????? ?? ????? ?? ??? ?????? ??????? ???? ???? ?? ??? ????????? ????? ???? ?? ??? ???? ????

???? ?????? ???? ??????
???? ID: 914288 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

?????

?????? ??????????? Microsoft SQL Server 2005 ?? Microsoft SQL Server 2008 ??? ??? ?????? ?? ????????? ????? ?? ??? ????? ?? ??? ????? ?????? ?? ??????? ?? ????? ???? ??:
  • ??????? ????????
  • ????????? ???????
  • ??????? ???? ??????
??????????, ???? ?????? ???????? ??? ?? ???? ?????? ?? ??????? ??? ?? ??? ??????? ?????? ?? ?????? ??????????? ??????? ?? ???: ??????? ???? ?? ??? ????? ????

Microsoft ?????? ?????? ???? ??????? ???????? ?? ??? ????????? ????? ???? ?? ??? ????? ?? ???? ??? ??? ??????????? ?????? ?? ???? ???? ?? ??? Microsoft ?????? ?????? ???? ??????? ???????? ?? ?? ????????? ?? ????? ???? ??? ?? ???? ????? ????????? ????? ???? ?? ??? ????? ?? ????? ???? ??? ???? ???? ?????? ??????????? ??????? ????? ?? ????? ???

???? ???????

??? ?? ?? ????? SQL Server 2005 ????????? ????? ???? ?? ??? ????? ?? ???? ???? ?? ????, ????????? ???? ?? SQL ????? ??????? Studio ??????? SQL Server 2005 ?????? ??? 2 ?? ??? ?? ??????? ??? ??? ?? SQL Server 2005 ??? SQL Server ??????? Studio ?? ?????? ????????? ?? ?????, ????????? ??????? ??? ???? ??? ?????? ?????? ?? ???? ??? ??? ????? ?? ??? ??? ?? ????? ???? ?? ????

????????? ???? ???????

?? ?? ???? ???? ????? clone ??????? ?????, ?? ?? ???? ?? ???? ?? ???? ???? ??????? ???????? ?? ???-??? ???????????? ?? ????? ?? ????????? ?? ??? ?????????? ?? ?? ???? ??????? ????????? ??, ?? ???? ????? ??? ????? ??:
  • ?? ?????? ?? ???: ??????? ???? ?? ??? ?????? ?????? ?????????? ???????? ?? ???????? ?? ?????
  • ???? ?????? ???????? ?? ??? ???? ?? ??? ????? ?? ?? ???????????? ?? ???????? ???
???:??? ?? ???? ??????? ?? ??? ??? ????????? ????? ????, ?? ??????? ?? ??? ???????? ????? ?????????? ?? ????, ???????????? ????????? ?????????? CPU ???????? ?? consumes ??? ?? ??????? ???? ??? ?? off-peak ????? ?? ????? ????????? ????? ????? ??, ?? ???-??? ?????????? ?? ??? ????????? ????? ???? ?? ??? ????? ?????? ?? ????? ?? ???? ????

???? ?????? ?????? ???????? ?? ?? ???????? ??????? ?????????, ?? ????? ?? ???? ????:
  1. SQL ????? Studio ??????? ?? ??????
  2. ????????????? Explorer, ??????? ???????????, ?? ???? ??? ?? ????????? ?? ??????? ?? ?????? ??????
  3. ??????? ?? ????-????? ????, ?? ????? ??????????? ????-????? ????, ?? ???? ???????????? ????? ????.
  4. ????????? ??????? ??? ???????? ???? ?? ??? ??????? ????? ??? ?? ??? ???? ?? ??? ????? ????????? ??????? ??? ???????? ?? ??? ????????????????? ?? ????? ?? ??? ????? ????, ?? ???? ???next.
  5. ?????????????? ?????? ?????????? ????? ???, ??? ?? ?? ????? ?????? ??? ???????? ???? ?? ??? ??????? ??? ?? ????? ???????? ??????
    ?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
    ???????????? ????????? ?? ??? ???? ?? ???
    Ansi ??????True
    ?????? ?? ???????????? ???? ????True
    ?????? ???????? ?? ??? ????????? ????? ????True
    ?????? ???? ??? ????? ????True
    ????????? CollationTrue
    ????????? ??????? ?????True
    ????????? ?????True
    ????????? ???????? ???? ?????????True
    ????????? ??????????????? ?? histograms ?????????
    ????????? ???????True
    ????????? TriggersTrue
    ???:TheScript Logins?????? ??Script Object Level Permissionsoption may not be required unless the schema contains objects that are owned by logins other thandbo.
  6. ????? ????,next.
  7. ????? ????Script to Fileoption, and then enter a file name.
  8. ????? ????,??????.

Script individual objects

You may only script individual objects that are referenced by a particular query instead of scripting the complete database. However, unless all database objects were created by using the WITH SCHEMABINDING clause, the dependency information in thesys.dependssystem table may not always be accurate. This inaccuracy may cause one of the following issues:
  • The scripting process does not script a dependent object.
  • The scripting process may script objects in the incorrect order. To run the script successfully, you must manually edit the generated script.
Therefore, we do not recommend that you script individual objects, unless the database has lots of objects and scripting would otherwise take too long. If you must use script individual objects, follow these steps:
  1. In SQL Server Management Studio, expand???????, and then locate the database that you want to script.
  2. Right-click the database, point toScript Database As?? ????? ????,CREATE to?? ????-????? ????, ?? ???? ???????.
  3. Enter a file name, and then click??????.

    The core database container will be scripted. This container includes files, file groups, the database, and properties.
  4. Right-click the database, point to??????? ????-????? ????, ?? ???? ???Generate Scripts.
  5. Make sure that the correct database is selected, and then clicknext.
  6. ?????Choose Script Optionsdialog box, change the following settings from the default value to the value that is listed in the following table.
    ?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
    Scripting optionValue to select
    Ansi PaddingTrue
    Continue Scripting on ErrorTrue
    Include System Constraint NamesTrue
    Generate Script for Dependent ObjectsTrue
    Script CollationTrue
    Script LoginsTrue
    Script Object Level PermissionsTrue
    Script StatisticsScript Statistics and histograms
    Script USE DATABASETrue
    Script IndexesTrue
    Script TriggersTrue
    ???:TheScript Logins?????? ??Script Object Level Permissionsoption may not be required unless the schema contains objects that are owned by logins other thandbo.
  7. ?????Choose Object Typesdialog box, select all database object types that the problematic query references.

    For example, if the query only references tables, select????????. If the query references a view, selectViews and Tables. If the problematic query uses a user-defined function, selectFunctions.
  8. When you have selected all the object types that are referenced by the query, clicknext.
  9. A dialog box appears for each database object type that you selected in step 7. In each dialog box, select the specific tables, views, functions, or other database objects, and then clicknext.
  10. ????? ????Script to Fileoption, and then specify the same file name that you entered in step 3.
  11. ????? ????,??????to start the scripting.
When the scripting has finished, send the script file to the Microsoft support engineer. The Microsoft Support Engineer may also request the following information:
  • The hardware configuration, including the number of processors and how much physical memory exists
  • The SET options that were active when you ran the query
???:You may have already provided this information by sending a SQLDiag report or a SQL Profiler trace. You may have also used another method to provide this information.

How the information is used

The following tables help explain how the query optimizer uses this information to select a query plan.

Metadata

?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
ConstraintsThe query optimizer frequently uses constraints to detect contradictions between the query and the underlying schema. For example, if the query has a "WHERE col = 5" clause and a "CHECK (col< 5)"="" check="" constraint="" exists,="" the="" query="" optimizer="" knows="" that="" no="" rows="" will="">

The query optimizer makes similar types of deductions about nullability. For example, the "WHERE col IS NULL" clause is known to be true or false depending on the nullability of the column and whether the column is from the outer table of an outer join. The presence of FOREIGN KEY constraints is useful to determine cardinality and the appropriate join order. The query optimizer can use constraint information to eliminate joins or simplify predicates. These changes may remove the requirement to access the base tables.
??????????? ?? ????? ?? ??????????? ?? ????? ????? ?? ?????? ????? ?? ?????? ?? ?? ???? ??????????? ????? ??????? ??? ???? Predicate ?? ??????? ?? ???? ?? ?????? ??????????? ?? ???? ?? ????? ?????, ???????????, ?? ????? ?? ?? predicate cardinality ?????? ????? ?? ???? ?????? ????? ???? cardinality ?? ???? ???? ?? ??? ?????? ???? Cardinality ?? ???? ???? ??? ?????? ?? ???? ?? ?????? ?? ??? ?? ??? ??? ????? ?? ??? ??? ????? ???? ???? ??? ?????, ?? ?????? ?? ????????? ??????? ?? ??????? ???? ?? ??? ?? ???? ???? ????? cardinality ???? ??????
?????? ???? (????????? ?? ??????? ?? ??????)?????? ??????????? histograms ?? ????? ????????? ?? ?? ???? ??? ?? predicate ?? ??? ?? ??? ???? ???? ?? ??? ????? ???? ??? ????????? ?????? ?????? ?????? ??? ?? ??? ?? ????????? ?? ?????? ?? ???? ?? multiplying ?????? ????? cardinality ?????? ???? ?? ??? The number of pages in the table or the index is a factor in estimating the IO cost. The table size is used to calculate the cost of a scan, and it is useful when you estimate the number of pages that will be accessed during an index seek.
Database optionsSeveral database options can affect optimization. TheAUTO_CREATE_STATISTICS, ??AUTO_UPDATE_STATISTICSoptions affect whether the query optimizer will create new statistics or update statistics that are out of date. ThePARAMETERIZATIONlevel affects how the input query is parameterized before the input query is handed to the query optimizer. Parameterization can affect cardinality estimation and can also prevent matching against indexed views and other types of optimizations. TheDATE_CORRELATION_OPTIMIZATIONsetting causes the optimizer to search for correlations between columns. This setting affects cardinality and cost estimation.

???????

?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
Session SET optionsTheANSI_NULLSsetting affects whether the "NULL = NULL" expression evaluates as true. Cardinality estimation for outer joins may change depending on the current setting. Additionally, ambiguous expressions may also change. For example, the "col = NULL" expression evaluates differently based on the setting. However, the "col IS NULL" expression always evaluates the same way.
Hardware resources????? ???? ?? ??? ???????? ?? ??? ???? ??????? SQL ????? ?? ??? ?????? ?????? ?? ?????? ?? ?????? ???? ??? ?????? ?? ???, ???? ?? ???? ??? ?? ???? ??, ??? ?????? ??????????? knows ?? ???? spooled ????? ???? ?????? ????? ?? ???? ???????, ??? ???? ?? ???? ???? ??? ?? ??, ???????? ?? ?????? ??? ???? ???? ?? ???? SQL ????? ?? considers ????? ???????????? ??? ????? ?? ?? ?? ???? ???????? ?? ??? ??? "MAXDOP" ????? ?? ????? ???? parallelism ????? ???? ???? ??? ?? ??parallelism ?? ?????? ?????????????? ???????
SQL ????? ????? ???? (SKU) ??????? ???????? ???? SQL Server 2005 ?? SQL Server 2008 ?? ??????? editions ?? ????? ???? ?????? ?? ???, ?????????? ??????? ?? ??????? ??? ?????? ?? ??? ???? ???? ??? ??? ?? SQL Server 2005 ??????????? ???????? ??? ???, ????? ?????????????? ?? ??? ???? ?? SQL Server 2000 Standard Edition ?? SQL Server 2005 ??????????? ??????? ?? ??? ????? ???

???

???? ID: 914288 - ????? ???????: 06 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
??????: 
kbexpertiseinter kbexpertiseadvanced kbsql2005engine kbhowto kbinfo kbmt KB914288 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:914288

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com