sqlserver20xxscalabilityenhancements-預(yù)覽頁
【正文】 LINE if required Mar2022 14 Microsoft Developer amp。 Platform Evangelism Module Overview Data Compression Service Broker Enhancements Query Optimizer Enhancements Partitioning Enhancements Mar2022 22 Microsoft Developer amp。 Platform Evangelism MERGE MERGE lets you INSERT, UPDATE, AND DELETE in a single operation (. single pass of the data set) You provide a source and target table Conceptually, this is a full outer join between the two tables and lets you provide mands for rows that match, rows only in the source, and rows only in the target You can specify mands for at least 1 and at most all of these cases and define what should happen (INSERT, UPDATE, DELETE) Source table can be any table source, target table can be an updateable view but not be a remote table or distributed partitioned view MERGE: Example MERGE: Details Great for large tables (ex: data warehouse population) where onepass transforms can give a performance boost. Also can simplify TSQL logic and push it into the database engine Special $ACTION column allows you to record whether MERGE did an INSERT, UPDATE, DELETE Plan Freezing sp_create_plan_guide was added late in SQL 2022 ? Had to get XML showplan ? Then had to quote the quotes and pass it back in to this stored procedure ? Lots of restrictions/problems in initial release (Only worked on SELECT statements, XML nesting limits in plan representation, size limits getting the plan out through the various SQL query tools, …) ? SQL 2022 adds another procedure: sp_create_plan_guide_from_cache ?Gets the plan directly from the server?s plan cache ?Both sp?s now work on INSERT, UPDATE, DELETE, (MERGE) ? Adds an offset to let you specify which statement in a batch to hint Plan guides are persisted in Star Join Special Optimizations for Data Warehouse Queries Uses bitmaps from dimension tables to filter rows from a fact table in a series of joins. Query Execution applied this in SQL 2022, but it often lacked cardinality/costing information to make a good decision You can see this as a “Bitmap” operator in the query tree, right before the parallelism operators for hash/merge joins Star Join: Example Image From: SQL Server Books Online Star Join: Restrictions Fact Table Must Have 100+ Pages Singlecolumn inner joins from fact to each dimension table (necessary for the bitmap) Bitmaps are an optimization, so they can be dropped from the plan if the server is under memory pressure. Bitmaps are only used in parallel plans, so if there are not enough available threads bitmaps will also be dropped. You can see a “Bitmap Warning” trace event to track down nonuse of Bitmaps where the QP wanted to use them. Module Overview Data Compression Service Broker Enhancements Query Optimizer Enhancements Partitioning Enhancements Mar2022 34 Microsoft Developer amp。 Platform Evangelism Lock Escalation: Options and Syntax Escalation setting is pertable, set with ALTER TABLE: ? ALTER TABLE mytable SET (LOCK_ESCALATION = {AUTO | TABLE | DISABLE}) AUTO: Partitionlevel escalation if the table is partitioned TABLE: Always tablelevel escalation DISABLE: Don’t escalate unless absolutely necessary How to tell what setting a table already has? ? SELECT lock_escalation_desc FROM WHERE name = ?mytablename? There is no tool (SSMS) support for ALTER TABLE Mar2022 43 Microsoft Developer amp。 Platform Evangelism Review Data Compression Service Broker Enhancements Query Optimizer Enhancements Partitioning Enhancements Mar2022 48 Microsoft Developer
環(huán)評公示相關(guān)推薦
鄂ICP備17016276號-1