What's new in SQL Server 2012
What’s new in SqlServer 2012:
1. Cross-Cluster
Migration of AlwaysOn Availability Groups for OS Upgrade.
SQL Server 2012 SP1 introduces support for cross-cluster
migration of AlwaysOn Availability Groups deployments to a new Windows Server
Failover Clustering (WSFC) cluster. A cross-cluster migration moves one
AlwaysOn availability group or a batch of availability groups to the new,
destination WSFC cluster with minimal downtime. The cross-cluster migration
process enables you to maintain your service level agreements (SLAs) when
upgrading to a Windows Server 2012 cluster. SQL Server 2012 SP1
must be installed and enabled for AlwaysOn on the destination WSFC
cluster. The success of cross-cluster migration depends on thorough
planning and preparation of the destination WSFC cluster.
2.
Selective XML Index.
SQL Server 2012 SP1
introduces a new type of XML index known as a Selective XML Index. This new
index can improve querying performance over data stored as XML in SQL Server,
allow for much faster indexing of large XML data workloads, and improve
scalability by reducing storage costs of the index itself
3.DBCC
SHOW_STATISTICS works with SELECT permission
In
earlier releases of SQL Server, customers need administrative or ownership
permissions to run DBCC SHOW_STATISTICS. This restriction impacted the
Distributed Query functionality in SQL Server because, in many cases, customers
running distributed queries did not have administrative or ownership
permissions against remote tables to be able to gather statistics as part of
the compilation of the distributed query. While such scenarios still execute, it
often results in sub-optimal query plan choices that negatively impact
performance. SQL Server 2012 SP1 modifies the permission restrictions and
allows users with SELECT permission to use this command. Note that the
following requirements exist for SELECT permissions to be sufficient to run the
command:
·
Users must have permissions on all columns in the statistics
object
·
Users must have permission on all columns in a filter condition
(if one exists)
Customers
using Distributed Query should notice that statistics can now be used when
compiling queries from remote SQL Server data sources where they have only
SELECT permissions. Trace flag 9485 exists to revert the new permission check
to SQL Server 2012 RTM behavior in case of regression in customer scenarios.
What is DBCC SHOW_STATISTICS:
DBCC
SHOW_STATISTICS displays current query optimization statistics for a table or
indexed view. The query optimizer uses statistics to estimate the cardinality
or number of rows in the query result, which enables the query optimizer to
create a high quality query plan. For example, the query optimizer could use
cardinality estimates to choose the index seek operator instead of the index
scan operator in the query plan, improving query performance by avoiding a
resource-intensive index scan.
The query optimizer stores statistics for a table or indexed
view in a statistics object. For a table, the statistics object is created on
either an index or a list of table columns. The statistics object includes a
header with metadata about the statistics, a histogram with the distribution of
values in the first key column of the statistics object, and a density vector
to measure cross-column correlation. The Database Engine can compute
cardinality estimates with any of the data in the statistics object.
DBCC SHOW_STATISTICS displays the header, histogram, and density
vector based on data stored in the statistics object. The syntax lets you
specify a table or indexed view along with a target index name, statistics
name, or column name. This topic describes how to display the statistics and
how to understand the displayed results.
4.New
function returns statistics properties
The
dynamic management function sys.dm_db_stats_properties returns properties of statistics for
the specified database object (table or indexed view) in the current SQL Server
database. You can use this function to return information such as the last time
the statistics object was updated for the table or indexed view, or the number
of rows that were sampled for statistical calculations. For more information
5.SSMS
Complete in Express
Customers
who use the SQL Server Express With Tools, SQL Server Express COMP, SQL Server
Express with Advanced Services and SQL Server Management Studio Express now
have SQL Server Management Studio complete. These Express Editions now ship the
SSMS feature with functionalities that exist with the SSMS from full editions
of SQL Server.
6.SlipStream Full Installation
Provides
customers with a pre-built "Slipstream image" that consists of a
compressed, self-extracting .exe and a '.box' payload file that contains a SQL
Server 2012 RTM image (Setup.exe, MSI’s, etc.) along with the most recent
Service Pack. When the package is executed, the two images are merged in
real-time providing the user with a single Setup workflow experience, landing
the operation (Install, Upgrade, AddNode, etc) at the encompassed SP1 level,
executing any updates made to Setup components themselves in the Service Pack.
Customers
can perform new instance installations (or SQL Server 2008/2008 R2 upgrades) at
the Service Pack functional level using a single click install workflow
experience. This can be performed directly by utilizing the slipstream .exe
from the command line just as they would use Setup.exe by supplying all
appropriate setup command line parameters. This is in contrast to having to
install RTM media, find and download the desired Service Pack (for the correct
language and CPU), apply the Service Pack, then load Setup.exe to perform the
desired setup action.
Reference:
No comments:
Post a Comment