sqlserver20xxscalabilityenhancements-資料下載頁
2025-09-25 13:56本頁面
【正文】 em Mar2022 40 Microsoft Developer amp。 Platform Evangelism FG1 FG2 FG3 Partitioned Table Partition 1 Partition 2 Partition 3 Query 1 Query 2 update update Lock Escalation: The Solution SQL Server 2022 allows lock escalation to the partition level, allowing concurrent access to other partitions Escalation to partition level does not block queries on other partitions Mar2022 41 Microsoft Developer amp。 Platform Evangelism IX X FG1 FG2 FG3 Partitioned Table Partition 1 Partition 2 Partition 3 Query 1 Query 2 update update DEMO Lock Escalation Mar2022 42 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 Lock Escalation: Potential Problems Lock escalation to the partition level was originally going to be the default setting until some TAP applications hit deadlocks after upgrading This can happen (rarely) if the access pattern is such that a query escalates on one partition and then needs to lock a row on another partition ? Two such queries on different partitions could deadlock each other Customers need to be careful when adjusting the escalation setting Mar2022 45 Microsoft Developer amp。 Platform Evangelism PartitionLevel Lock Escalation Alternative (1) Architecturally, consider splitting readonly and readwrite data into separate tables and you can get even more benefits! How? ? Separate readonly into a partitioned table ? Keep readwrite as standalone table or separate partitioned table ? Consider using a partitioned view to simplify user access OR use application directed inserts/updates/deletes/selects Feb2022 46 Microsoft Developer amp。 Platform Evangelism PartitionLevel Lock Escalation Alternative (2) Why? ? RO partitioned table can have more indexes, RW partitioned table can have fewer indexes ? Partitioned tables are easier for the optimizer to optimize (because all partitions have the same indexes – however, filtered indexes potentially change this) ? The standalone RW table can have its indexes rebuilt online (SQL Server 2022/2022 do not support partitionlevel index rebuilds online) ? Statistics can be very different for new data ing into a partition (versus the larger, historical data in the readonly portion of the table) and in all versions of SQL Server (including SQL Server 2022), statistics are only maintained at tablelevel. By separating the RO and RW data, you might be able to get better statistical data and distribution (and therefore better optimization) Mar2022 47 Microsoft Developer amp。 Platform Evangelism Review Data Compression Service Broker Enhancements Query Optimizer Enhancements Partitioning Enhancements Mar2022 48 Microsoft Developer amp。 Platform Evangelism Resources Data Compression ? Storage Engine Blog ? Technical Webcast Query Optimizer Enhancements Star Joins: MERGE: Partitioning ? Blog ? Books Online topic ?Lock Escalation? Mar2022 49 Microsoft Developer amp。 Platform Evangelism Mar2022 50 Microsoft Developer amp。 Platform Evangelism
環(huán)評公示相關(guān)推薦
鄂ICP備17016276號-1