SQL Server 2016：高性能与关键任务优化

SQL

Server

需积分: 12 53 浏览量更新于2024-07-16 收藏 1.61MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

"SQL Server 2016 Mission Critical Performance White Paper" 本文是关于Microsoft SQL Server 2016的一项技术白皮书，主要探讨在数据量和复杂性不断增长的背景下，如何实现关键任务性能的提升。白皮书详细介绍了SQL Server 2016在性能优化、安全性增强、高可用性和可扩展性等方面的新功能和操作策略。一、性能优化 SQL Server 2016引入了一系列创新，以提升企业级应用的性能。这些包括： 1. In-Memory OLTP（内存中在线事务处理）：显著提高事务处理速度，通过将数据存储在内存中，减少了磁盘I/O的等待时间。 2. Query Store：提供查询性能的历史记录和监控，帮助DBA快速识别和解决性能问题。 3. Columnstore Indexes：改进的列存储索引，加速数据分析和数据仓库查询。 4. Batch Mode on Rowstore：允许在行存储上进行批处理，进一步提升查询性能。二、安全增强 1. Always Encrypted：提供端到端的数据加密，确保敏感数据在传输和存储过程中的安全性。 2. Dynamic Data Masking：动态数据掩码，限制对特定数据字段的访问，防止未授权访问。 3. Row-Level Security：行级安全，实现细粒度的数据访问控制。 4. Enhanced Auditing：增强审计功能，提供更详细的数据库活动跟踪。三、高可用性 SQL Server 2016在高可用性方面进行了重大改进，如： 1. AlwaysOn Availability Groups：提供跨多个节点的高可用性解决方案，允许在不影响业务的情况下进行故障转移。 2. Stretch Database：自动将冷数据迁移到Azure云中，减少本地存储需求，同时保持数据在线并可查询。 3. Enhanced Backup and Recovery：优化备份和恢复过程，减少恢复时间目标(RTO)和恢复点目标(RPO)。四、可扩展性 1. PolyBase：支持混合查询，结合SQL和Hadoop数据，提升大数据分析能力。 2. Resource Governor：更好地管理服务器资源，确保关键工作负载的优先级和性能。 3. Scalable Shared Memory：通过扩展共享内存，适应更高的并发性和更大的数据集。总结，SQL Server 2016为企业提供了一个新的“关键任务”标准，它不仅提升了数据库的性能，还增强了安全性，确保了高可用性，并提供了强大的扩展能力，以应对全球化市场中日益变化的挑战。这些特性使组织能够在动态环境中保持竞争力。虽然文档内容仅供参考，但微软保证了其在发布时的准确性，旨在为读者提供有价值的信息。

资源详情

资源推荐

Page 7

Mission-critical performance with SQL

Server

SQL Server’s evolution mirrors the growing critical nature of data for enterprises. Data is the new currency,

and it has become a major competitive differentiator. Companies with a data-centric culture are

harnessing increasingly diverse data. This data is not just relational or internal but includes both relational

and non-relational, as well as internal and external data sources. These companies are using new

analytical models to review historical data and apply it to help predict the future. Insights from the data

are many, which are shared more broadly in the organization. And, in many cases, much of this analysis is

dispensed at near real-time speeds. Companies with a data-centric culture are more productive, more

efficient with their operations, and they innovate faster—all leading to improvements in their bottom line.

More than ever, organizations need mission-critical operations that are easy to deploy and that are

balanced with faster time-to-solution. This white paper reviews four main areas in which SQL Server

continues to deliver in this manner: performance, security, availability, and scalability.

Performance

SQL Server consistently leads in performance benchmarks, such as TPC-E and TPC-H, and in real-world

application performance. With SQL Server 2016, performance is enhanced with a number of new

technologies, including in-memory enhancements, Query Store, and temporal support, to name a few.

SQL Server’s integrated in-memory toolset goes far beyond isolated features to support dramatic

performance improvements in a wide range of scenarios. These technologies include In-Memory Online

Transaction Processing (In-Memory OLTP), primarily for transactional workloads, In-Memory Columnstore

for decision-support workloads (discussed in the Microsoft SQL Server: Deeper Insights Across Data white

paper), and the new Query Store feature, which allows you to monitor and optimize query plans for

particular application scenarios over time―providing additional performance-tuning opportunities.

In-Memory Online Transaction Processing

In-memory technology for SQL Server dramatically improves the throughput and latency of SQL Server

OLTP capabilities. It is designed to meet the requirements of the most demanding transaction processing

applications, and Microsoft has worked closely with a number of companies to prove these gains. The

feature set of In-Memory OLTP includes the following:

 Memory-optimized tables: There are two types of memory-optimized tables. Durable tables are

fully logged and persist over server restarts. Non-durable tables do not persist over server restarts

and are most commonly used in lieu of global temp tables in the user database or in scenarios

where persistence is not needed, such as staging tables in an Extract Transform Load (ETL)

process.

 Memory-optimized table variables: These variables are created using memory-optimized table

types. Variables are stored in-memory, leading to more efficient data access because they use the

Page 8

same memory-optimized algorithms and data structures as memory-optimized tables—

particularly when using natively compiled stored procedures.

 Natively compiled stored procedures: SQL Server can natively compile stored procedures that

access memory-optimized tables. Native compilation enables faster data access and more

efficient query execution than interpreted (traditional) Transact-SQL. Natively compiled stored

procedures are parsed and compiled when they are loaded to native DLLs (dynamic-link libraries).

This is in contrast to other stored procedures that are compiled on first run. They have an

execution plan created and reused, and they use an interpreter for execution.

 Natively compiled scalar user-defined functions (UDFs): These replace traditional scalar UDFs

that do not perform data access, and this replacement reduces UDF runtime. Natively compiled

scalar UDFs cannot access disk-based tables. If data access is required, consider migrating the

table to memory-optimized (if no data access occurs, migration is not required).

In-Memory OLTP is designed on the following architectural principles:

 Optimize for main-memory data access. Storage-optimized engines (such as the current OLTP

engine in SQL Server) will retain hot data in a main-memory buffer pool based on frequency of

access. The data access and modification capabilities, however, are designed so that data may be

paged in or out to disk at any point. With In-Memory OLTP, you place tables used in the extreme

transaction-processing portion of an application into memory-optimized main-memory

structures. The remaining application tables, such as reference data details or historical data, are

left in traditional storage-optimized structures. This approach enables you to optimize hotspots

for memory use, without having to manage multiple data engines. Main-memory structures for

In-Memory OLTP eliminate the overhead and indirection of the storage-optimized view while still

providing the full atomicity, consistency, isolation, and durability (ACID) properties you expect

from a database system.

 Include tools for migration. To identify the appropriate tables and memory structures for

utilizing In-Memory OLTP, SQL Server 2016 Management Studio includes tools designed to assist

users in transitioning to In-Memory OLTP. These include transaction performance analysis to

identify objects that would benefit from migration, migration advisors to assist in migrating disk-

based tables to memory-optimized tables, and migration of traditional stored procedures and

functions to natively compiled objects.

 Accelerate business-logic processing. In-Memory OLTP, queries, and procedural logic in

procedures that are stored in Transact-SQL (T-SQL) are compiled directly into machine code

through aggressive optimizations that are applied at compilation time. Consequently, the stored

procedure can be executed at the speed of native code.

 Provide frictionless scale-up. In-Memory OLTP implements a highly scalable concurrency

control mechanism and uses a series of lock-free data structures to eliminate traditional locks and

latches while guaranteeing the correct transactional semantics that ensure data consistency.

 Integrate into SQL Server. One of the most impressive things about In-Memory OLTP is that it

achieves breakthrough improvements in transactional processing capabilities without requiring a

separate data management product or new programming model. This enables an integrated

developer and database administrator (DBA) experience with the same T-SQL, client stack,

tooling, backup and restore, and AlwaysOn capabilities. By offering in-memory functionality

剩余36页未读，继续阅读

mywlbest

粉丝: 0
资源: 1

SQL Server 2016：高性能与关键任务优化

SQL_Server_2016_Mission_Critical_Performance_White_Paper

cn_sql_server_2012_enterprise_edition_with_sp1_x64_dvd_1234495.iso sha1 aeb1

sql_query = "SELECT i_ui_identifier,nvc_user_name FROM t_user_info" sql_server_df = pd.read_sql(sql_query, sqlserver_conn)这是什么意思

cn_sql_server_2008_r2_express_x64.exe

cn_sql_server_2014_enterprise_edition_x64_dvd_3932882.iso

SQLNET.ENCRYPTION_SER VER =REQUESTED, SQLNET.ENCRYPTION_TYP ES_SERVER = RC4_256, SQLNET.CRYPTO_CHECKS UM_SERVER = REQUESTED

cn_sql_server_2014_enterprise_edition_with_service_pack_3

sqlserver学习资料pdf文档

cn_sql_server_2012_express_ s_x86

实时计算：Apache Flink：Flink与Kafka集成实现事件驱动架构.docx

最新资源