The SQL Server product group has made sizable investments in improving scalability and
performance associated with the SQL Server Database Engine. Some of the main enhancements that
- allow organizations to improve their SQL Server workloads include the following:Columnstore Indexes More and more organizations have a requirement to deliver breakthrough and predictable performance on large data sets to stay competitive. SQL Server 2012 introduces a new in-memory, columnstore index built directly in the relational engine. Together with advanced query-processing enhancements, these technologies provide blazing-fast performance and improve queries associated with data warehouse workloads by 10 to 100 times. In some cases, customers have experienced a 400 percent improvement in performance. For more information on this new capability for data warehouse workloads, review Chapter 3, “Blazing-Fast Query Performance with Columnstore Indexes.”
- Partition Support Increased To dramatically boost scalability and performance associated with large tables and data warehouses, SQL Server 2012 now supports up to 15,000 partitions per table by default. This is a significant increase from the previous version of SQL Server, which was limited to 1000 partitions by default. This new expanded support also helps enable large sliding-window scenarios for data warehouse maintenance.
- Online Index Create, Rebuild, and Drop Many organizations running mission-critical workloads use online indexing to ensure their business environment does not experience downtime during routine index maintenance. With SQL Server 2012, indexes containing varchar(max), nvarchar(max), and varbinary(max) columns can now be created, rebuilt, and dropped as an online operation. This is vital for organizations that require maximum uptime and concurrent user activity during index operations.
- Achieve Maximum Scalability with Windows Server 2008 R2 Windows Server 2008 R2 is built to achieve unprecedented workload size, dynamic scalability, and across-the-board availability and reliability. As a result, SQL Server 2012 can achieve maximum scalability when running on Windows Server 2008 R2 because it supports up to 256 logical processors and 2 terabytes of memory in a single operating system instance.
SQL Server deployments are growing more numerous and more common in organizations. This fact
demands that all database administrators be prepared by having the appropriate tools to successfully
manage their SQL Server infrastructure. Recall that the previous releases of SQL Server included
many new features tailored toward manageability. For example, database administrators could easily
leverage Policy Based Management, Resource Governor, Data Collector, Data-tier applications, and
Utility Control Point. Note that the product group responsible for manageability never stopped
investing in manageability. With SQL Server 2012, they unveiled additional investments in SQL Server tools and monitoring features. The following list articulates the manageability enhancements in SQL Server 2012:SQL Server Management Studio With SQL Server 2012, IntelliSense and Transact-SQL debugging have been enhanced to bolster the development experience in SQL Server Management Studio.IntelliSense Enhancements A completion list will now suggest string matches based on
partial words, whereas in the past it typically made recommendations based on the first
character.
- A new Insert Snippet menu This new feature is illustrated in Figure 1-4. It offers developers a categorized list of snippets to choose from to streamline code. The snippet picket tooltip can be launched by pressing CTRL+K, pressing CTRL+X, or selecting it from the Edit menu.
- Transact-SQL Debugger This feature introduces the potential to debug Transact-SQL scripts on instances of SQL Server 2005 Service Pack 2 (SP2) or later and enhances breakpoint functionality.
- Resource Governor Enhancements Many organizations currently leverage Resource Governor to gain predictable performance and improve their management of SQL Server workloads and resources by implementing limits on resource consumption based on incoming requests. In the past few years, customers have also been requesting additional improvements to the Resource Governor feature. Customers wanted to increase the maximum number of resource pools and support large-scale, multitenant database solutions with a higher level of isolation between workloads. They also wanted predictable chargeback and vertical isolation of machine resources. The SQL Server product group responsible for the Resource Governor feature introduced new capabilities to address the requests of its customers and the SQL Server community. To begin, support for larger scale multitenancy can now be achieved on a single instance of SQL Server because the number of resource pools Resource Governor supports increased from 20 to 64. In addition, a maximum cap for CPU usage has been introduced to enable predictable chargeback and isolation on the CPU. Finally, resource pools can be affinitized to an individual schedule or a group of schedules for vertical isolation of machine resources.
the maximum average CPU bandwidth that will be allocated is 40 percent.
ALTER RESOURCE POOL Pool25
WITH(
MIN_CPU_PERCENT = 5,
MAX_CPU_PERCENT = 40,
CAP_CPU_PERCENT = 80,
AFFINITY SCHEDULER = (8, 12 TO 16),
MIN_MEMORY_PERCENT = 5,
MAX_MEMORY_PERCENT = 15,
);
- Contained Databases Authentication associated with database portability was a challenge in the previous versions of SQL Server. This was the result of users in a database being associated with logins on the source instance of SQL Server. If the database ever moved to another instance of SQL Server, the risk was that the login might not exist. With the introduction of contained databases in SQL Server 2012, users are authenticated directly into a user database without the dependency of logins in the Database Engine. This feature facilitates better portability of user databases among servers because contained databases have no external dependencies.
- Tight Integration with SQL Azure A new Deploy Database To SQL Azure wizard, pictured in Figure 1-5, is integrated in the SQL Server Database Engine to help organizations deploy an on-premise database to SQL Azure. Furthermore, new scenarios can be enabled with SQL Azure Data Sync, which is a cloud service that provides bidirectional data synchronization between databases across the datacenter and cloud.
- Startup Options Relocated Within SQL Server Configuration Manager, a new Startup Parameters tab was introduced for better manageability of the parameters required for startup. A DBA can now easily specify startup parameters compared to previous versions of SQL Server, which at times was a tedious task. The Startup Parameters tab can be invoked by right-clicking a SQL Server instance name in SQL Server Configuration Manager and then selecting Properties.
- Data-Tier Application (DAC) Enhancements SQL Server 2008 R2 introduced the concept of data-tier applications. A data-tier application is a single unit of deployment containing all of the database’s schema, dependent objects, and deployment requirements used by an application. SQL Server 2012 introduces a few enhancements to DAC. With the new SQL Server, DAC upgrades are performed in an in-place fashion compared to the previous side-byside upgrade process we’ve all grown accustomed to over the years. Moreover, DACs can be deployed, imported and exported more easily across premises and public cloud environments, such as SQL Azure. Finally, data-tier applications now support many more objects compared to the previous SQL Server release.
0 komentar:
Post a Comment