Skip links

SQL Server 2014 Database Engine: New Features and Enhancements

You may want to read Part 1 , Part 2 , and Part 3 before continuing. Facebook, MySpace, and Twitter are all good examples of using technology to let… You can know more about the CTP-1 version from my previous blog post. This post provides the Download Link, Install Prerequisites, Limitations, etc for the beta product. With this new release of SQL Server 2014 there are a number of new features that Microsoft has included. Below I have outlined a number of those items I think are noteworthy.

  • Do you need a quick estimated distinct count on that enormous billion-row table?
  • Performance improvements due to added support for multithreading inside the enclave
    and key caching.
  • This statement has been improved and now it can be run in parallel, but the database compatibility level must be at least 110.
  • This durability feature can be controlled at multiple levels, database, COMMIT, or ATOMIC blocks.

The reduced latency gained from designating all or some of the transactions as delayed durable. The new estimator has incorporated algorithms and assumptions which work with modern OLTP and data warehouse workloads. This engine has been designed to deliver increased concurrency and it has a brand new mechanism to control and eliminate delays due to locking with an optimistic concurrency control mechanism. SQL Server 2022 extends segment elimination capabilities to string, binary, guid,
and datetimeoffset for scale greater than two. But, to take advantage of this feature, you will need to enable Query Store in
read-write mode.

The Options of Managed PostgreSQL in the Cloud

By no means is this an exhaustive list of the new features that Microsoft has included in this release. I know you have been waiting for the next release of SQL Server. On June 25th Microsoft released the first community technical preview (CTP1) for the next version of SQL Server. IF anybody knows of a site that lists the improvements to T-SQL that would be great.

sql server 2014 new features for dba

The following table
shows some examples of this and the links in the additional information section
contain a complete listing. Dbi services is a company specialized in IT consulting and services. We are experts in innovative and efficient data infrastructures and platforms. Tailor-made solutions is what we offer to our customers thanks to our sql server 2014 new features for dba consultants, whose skills and knowledge are constantly evolving thanks to continuous training. There is a very strong will on the part of Microsoft to provide BI tools, in order to easily access, analyze and share data from any source. Moreover, Microsoft has a real desire to strenghten Cloud utilization through Windows Azure and AlwaysOn.

SQL Server Performance Enhancements

In the pre-release version of SQL Server 2014, SQL Server Setup doesn’t install SSDT-BI. Instead, you must download SSDT-BI separately from the Microsoft Download Center. Power BI for Office 365 is a cloud-based business intelligence (BI) solution that provides data navigation and visualization capabilities. Power BI for Office 365 includes Power Query (formerly code-named Data Explorer), Power Map (formerly code-named GeoFlow), Power Pivot, and Power View. You can learn more about Power BI at Microsoft’s Power BI for Office 365 site.

  • Changes have been incorporated into the database engine, so In Memory Built-In objects are processed to take advantage of memory processing, instead of using the b-tree indexes and buffer pools.
  • Instead, you must download SSDT-BI separately from the Microsoft Download Center.
  • The new estimation logic which was designed for the cardinality estimator has improved the quality of the query execution plans and thus has a direct impact on the performance of queries.
  • You can find more detailed information about this feature, here.
  • Always Encrypted with Secure Enclaves may be just the solution you’re looking for.

suppose that the first thing that comes to mind when someone talks about blockchain
is Bitcoin and cryptocurrencies. Still, other uses for this technology make it useful
to guarantee the integrity of data. This feature allows you to run near real-time analytics with minimum impact on
the operational workload and reduces complexity because it doesn’t require using
ETL processes to deal with data movement.

Query Store Related

Power Pivot is an Excel feature for faster analytics and flexible modeling of large data sets, with in-memory technology. SQL Server 2014, this time mainly focuses on high performance in-Memory Operations for OLTP Databases by providing Memory Optimized Tables & Natively Compiled Stored Procedures. There is not much enhancement in T-SQL section, which was with SQL Server 2012. But with In-Memory features SQL Server 2014 has made a lot of impression and is definitely going to make a lot of impact. Do you know of any links to any T-SQL improvements or new features in SQL Server 2014.

sql server 2014 new features for dba

Furthermore, a new wizard has been delivered to deploy database to SQL Server in Windows Azure Virtual Machine for easier use. Power View allows to see the data in new ways with an interactive virtualization, such as a 3 D geospatial analysis with Power Map. My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. Indexing Clustered Columnstore Indexes – yo dawg, Brent heard you like indexes, so he shows you how to do something Books Online says can’t be done. Learn almost everything you need to know about SQL Server 2014 in Brent’s rundown of new features in SQL Server 2014.

SQL Server 2014

There are also improvements in the thread usage by availability groups, specifically
in the parallel redo. If the answer to all of those questions is yes, an SSD buffer pool extension may be for you. Honestly, Microsoft could stop there and I’d probably still recommend the new version for most of my clients, because that’s a killer performance benefit.

sql server 2014 new features for dba

Leave a comment