SQL 性能调优 - FORCE ORDER Query Hint

封云亭

　　From: http://www.sqlserver.info/syntax/force-order-query-hint/
　　When querying out data, SQL Server has a ton of different query hints.  One less commonly known query hint is FORCE ORDER.  So here we will talk about what this query hint does and how we can use it efficiently.
　　
　　FORCE ORDER
　　When you put this query hint on to your query, it tells SQL Server that when it executes the statement to not change the order of the joins in the query.  It will join the tables in the exact order that is specified in the query.  Normally the SQL Server optimizer will rearrange your joins to be in the order that it thinks will be optimal for your query to execute.
　　To use the FORCE ORDER query hint, you will want to add this at the end of your query.
　　Example Without FORCE ORDER
　　1
　　2
　　3
　　4
　　SELECT*
　　FROMHabitat H
　　INNERJOIN AnimalHabitat AH ON H.HabitatID = AH.HabitatID
　　INNERJOIN Animal A ON AH.AnimalID = A.AnimalID
　　This is the actual execution plan without the FORCE ORDER hint.  You can see that SQL Server starts at the table Animal and AnimalHabitat tables, then goes to the Habitat table.  If you look at our query above, we clearly list the tables in the order of Habitat, AnimalHabitat, then Animal.

　　Example With FORCE ORDER
　　1
　　2
　　3
　　4
　　5
　　SELECT*
　　FROMHabitat H
　　INNERJOIN AnimalHabitat AH ON H.HabitatID = AH.HabitatID
　　INNERJOIN Animal A ON AH.AnimalID = A.AnimalID
　　OPTION(FORCE ORDER)
　　In the screenshot below, it shows the actual execution plan with the FORCE ORDER hint.  In the new query, you can see that SQL Server starts with the Habitat and AnimalHabitat tables and then moves to the Animal table.  It does this because when we turn on the FORCE ORDER option, it uses the order of the tables specified in the query syntax.

　　What Is This Used For
　　The FORCE ORDER query hint is only used when you want to override the way that SQL Server wants to execute this query.  Normally you will just let SQL Server figure out how to get the data from the database.  It does such a good job at it, that you do not usually need to override this functionality.
　　Occasionally SQL Server will not figure out the most optimal way to pull this data out.  In this case you may want to try this query hint.