Database Reference
In-Depth Information
Cardinality estimator and query plans
The cardinality estimator has been redesigned in SQL Server 2014. It has been
redesigned in order to improve the quality of query plans and thus improve the
query performance. The new cardinality estimator includes assumptions and
algorithms that work well on modern transactional systems (OLTP) and data
warehousing databases. Microsoft has made these changes based on customer
feedback. The following is the feedback from a Microsoft customer:
while most queries will benefit from the change or remain unchanged, a small
number might show regressions compared to the previous cardinality estimator.
The changes to the cardinality estimator means that you should allow additional
testing of your database as you migrate and move them to SQL Server 2014 to ensure
that this change does not affect your database performance in a negative way.
Statistics
One of the problems while updating statistics on large tables in SQL Server is that the
entire table has to be scanned, for example, while using the WITH FULLSCAN option
to scan the entire table, even if only recent data has changed. This is also true when
using partitioning. Even if only the newest partition has changed since the last time,
the statistics are updated. Updating the statistics again requires a scan of the entire
table, not just the current partition. This scan option includes all the partitions that
didn't change. You can now update statistics incrementally with the introduction of
SQL Server 2014, which can provide help with this problem.
The CREATE STATISTICS option and related statistic statements now allow
for individual partition statistics to be created and updated using the INCREMENTAL
option. Other related statements that allow or report incremental statistics include:
UPDATE STATISTICS , sp_createstats , CREATE INDEX , ALTER INDEX , ALTER
DATABASE SET , DATABASEPROPERTYEX , sys.databases , and sys.stats options.
Resource Governor
In the previous editions of SQL Server, the Resource Governor allowed you to
specify the limits on the amount of CPU or memory that a process can use as part
of the resource pool. In SQL Server 2014, this has now been improved with new
Resource Governor settings to include the I/O activity too. In SQL Server 2014, you
can use the new MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings to
control the physical I/Os issued for user threads in a given resource pool.
 
Search WWH ::




Custom Search